Pull Info From URL Query, Sort and Paste Results in Form/eMail

[iMadejoolaff]

Using Outlook 2010. I am a dabbler in code; much better at reading/deciphering than writing, so let me apologize for my wicked-bad VBA grammar.

I have been given a URL that pulls information from an online Database. The information I have for accessing this data is as follows:
___________________________________________________________________________ ______________________________
http(squiggle)somesitedotcom/ac/someapi.asp?user=UUUUUUUU&pw=PPPPPPPP&cmd=somesql&sql=select[/URL] top 1 UDB_SalesRep1, UDB_CustAccount1, UDB_CustContact1, UDB_CustPhone1, UDB_CustEmail1 from amdevtdata_D where originalfilename = 'Bidder List' and evrefnum = ‘XXXXXX'
User = User Name
pw= Password
Evrefnum = Job Number
Here is what the data will look like when returned as shown below, based on the Job Number.
UDB_SalesRep1 UDB_CustAccount1 UDB_CustContact1 UDB_CustPhone1 UDB_CustEmail1 SalesRepName CustAccountName CustContactName CustPhone# CustemailAddress

Here are the technical details of the format of the data that is returned.

First, the return data will have a row that names the columns. Each column will be separated by <esc><vbtab> (ascii 027+009)
At the end of this row will be a row terminator of <esc><vbcrlf> (ascii 027+013+010)
Next come the data rows. Again, each data column is separated by <esc><vbtab> and each row is terminated with <esc><vbcrlf>
___________________________________________________________________________ ______________________________

I need to take that data and split it into separate strings that can be placed either in a userform or in the address/subject bar of an email. Problem is, I can get that data to show on a webpage, but I have no idea where it would go or how to access it in Outlook. I've tried a couple things like extrapolating strings and setting that to a variable, but you geniuses will know where I went wrong there.

Does anyone have any experience with this? Any assistance you can offer will be greatly appreciated! Also, I'm not sure if any of my current code would be pertinent to this issue, but I will post it if I need to.

[gmayor]

Just thinking out loud and without access to the web page, if the extracted data is as you describe you can replace the field separators with e.g. commas and the record separators with Chr(13) and put the results in an array which you can then interrogate to get the values you want.

[iMadejoolaff]

gmayor, if I understood Arrays I wouldn't be a dabbler! Sounds like a promising way to start, though. I'll read up on it and see if I can figure something out. Thanks for the direction

[gmayor]

OK, let's assume that you have a large multi-line string of text that encompasses the data that you have grabbed from the web site as described. That string is strData in the function below. You can then write the fields from each line to the array and you can interrogate that array by reading the row and column as shown in the test macro which follows.

Option Explicit
Function MyData(strData As String) As Variant
Dim vWords() As String
Dim vLines() As String
Dim i As Long, j As Long
    'replace the field separators with commas
    strData = Replace(strData, Chr(27) & Chr(9), Chr(44))
    'replace the record separators with vbCr (Chr(13)
    strData = Replace(strData, Chr(27) & Chr(13) & Chr(10), vbCr)
    'Split the data at the line breaks
    vLines = Split(strData, vbCr)
    'Split the lines at the commas
    vWords = Split(vLines(0), Chr(44))
    'setup the array to receive the data
    ReDim DataArray(UBound(vLines), UBound(vWords))
    'Process each line
    For i = 0 To UBound(vLines)
        'Process each field
        vWords = Split(vLines(i), Chr(44))
        'add them to the array
        For j = 0 To UBound(vWords)
            DataArray(i, j) = vWords(j)
        Next j
    Next i
    MyData = DataArray
End Function

'Read a field
Sub Test
'Call the array and supply it with the source of the string - here a document
MsgBox MyData(ActiveDocument.Range.Text)(1, 1)        'Row,Col
End sub

[iMadejoolaff]

This looks great, gmayor! I'm afraid I might be messing something up, though. I've been dorking around all morning with this:

 'Read a field
Sub Test()
     'Call the array and supply it with the source of the string - here a document
    MsgBox MyData("Full URL Here")(50, 5) 'Row,Col
End Sub

The bold URL in my first post is the type of URL I'm placing in place of "Full URL Here", but I keep getting Type Mismatch or Subscript out of Range errors with everything I try. It seems the URL is not a suitable data source for the Array; could it be because it's too long? Or, did I not change something appropriately in the Function and that's what's hanging it up? Also, does the array in MyData store that i,j table in something like the Clipboard? Sorry for all the questions, just trying to understand what's going on.

[westconn1]

as far as i can see, you are trying to pass the url to the function rather than the returned data for the web query to the url
you would need to scrape the returned data from a web page, or better, use a winhttp request (or similar), to get the data, then pass the data to the function
what code have you been using to get the data previously?

if you are going to use split to put the lines (and later words) into array, no need to replace the end of line characters, as you can just split on the existing ones

[iMadejoolaff]

as far as i can see, you are trying to pass the url to the function rather than the returned data for the web query to the url you would need to scrape the returned data from a web page, or better, use a winhttp request (or similar), to get the data, then pass the data to the function what code have you been using to get the data previously?

So, essentially, the MyData Function is just trying to get info from the "URL", rather than performing the function on the results of the URL? You're answer makes sense and I thought that was the issue, but I also assumed it would pass the URL to the function (post #2) where it would be processed and return the string as a response in the MsgBox.
There hasn't been an existing data pull for Outlook. I've been working on this as a side project to ensure Subject Line compliance and making multiple emails from our database easier to access. I've noticed a thing or two about scraping and the winhttp request, so when I get time again I'll look into that.

if you are going to use split to put the lines (and later words) into array, no need to replace the end of line characters, as you can just split on the existing ones

If I can get the macro running, I'll revisit this during clean up. Thanks for the pointer.

[gmayor]

The MyData function processes the data that you have already recovered from the web site. It does not do anything with the web site. It assumes that you have a text string in the format that you described (strData) and it puts that data into an array which you can then process to get the information you want. Consider the array as a virtual table.

[iMadejoolaff]

gmayor and westconn1, I appreciate the help and I think you've almost gotten me there. Thanks for the piggyback ride!

If I use the portion in between the asterisks and pass strData to a MsgBox, I get the raw info, so that's working.

Function MyData(strData As String) As Variant
    Dim vWords() As String, vLines() As String, myURL As String
    Dim i As Long, j As Long

'************************
    Dim WinHTTPReq As Object
    
    Set WinHTTPReq = CreateObject("WinHttp.WinHttpRequest.5.1")
 
    myURL = "Full URL String"
 
    WinHTTPReq.Open "GET", myURL, False
    WinHTTPReq.Send
 
    strData = WinHTTPReq.responseText
'************************
    
     'replace the field separators with commas
    strData = Replace(strData, Chr(27) & Chr(9), Chr(44))
     'replace the record separators with vbCr (Chr(13)
    strData = Replace(strData, Chr(27) & Chr(13) & Chr(10), vbCr)
     'Split the data at the line breaks
    vLines = Split(strData, vbCr)
     'Split the lines at the commas
    vWords = Split(vLines(0), Chr(44))
     'setup the array to receive the data
    ReDim DataArray(UBound(vLines), UBound(vWords))
     'Process each line
        For i = 0 To UBound(vLines)
            'Process each field
            vWords = Split(vLines(i), Chr(44))
            'add them to the array
            For j = 0 To UBound(vWords)
                DataArray(i, j) = vWords(j)
            Next j
        Next i
    MyData = DataArray

End Function

gmayor, I did some research on this before asking again, but this is where the Run stops. It's giving an Object Required error on the MsgBox line. I tried seeing what kind of variables I should be using when a MsgBox calls a function, but I'm coming up empty. From what I see, it's asking for the information from MyData that's on the ActiveDocument? I'm confused because there wouldn't be an active document for the WinHTTPReq, and nothing will get passed until the data is formatted and can be placed appropriately in the address/subject of a new email.

 'Read a field
Sub Test()
     'Call the array and supply it with the source of the string - here a document
    MsgBox MyData(ActiveDocument.Range.Text)(1, 1) 'Row,Col
End Sub

This all gets called from a UserForm and an OK button, so should I just be referencing the Array when it's time to populate the new email? Below is what I use to populate with what I have so far. How does MyData play into that? Do I need to dim a variable for every "cell" in the array to use it?

        With oMail
            .Subject = JobNum & Priv & Repl & "_PO" & FOnly & " - " & StrConv(JobNam, vbProperCase) _
                & " - " & StrConv(Subj, vbProperCase)
            .Recipients.Add ("email at emaildotcom")
            .BCC = BidLst
        End With

[iMadejoolaff]

Sw00t! Making headway! I had to change:

MsgBox MyData(ActiveDocument.Range.Text)(1, 1) 'Row,Col

to:

MsgBox MyData(vbArray)(1, 4) 'Row,Col

The power of test-cycling through Properties/Methods!

Private Sub CommandButton1_Click()
    tbJoNa.Value = MyData(vbArray)(1, 1)
End Sub

I'm able to call variables from the Array into my userform, but is there any reason this isn't a preferred method?

[westconn1]

but is there any reason this isn't a preferred method?

because we can not see your userform or other parts of your project
the test sub was just an example, provided, by graham, to show how to call a function with parameters, from some other procedure, reread the comment line included

[iMadejoolaff]

Yeah, westconn1, I figured it was an example and it took me a bit of playing to get it work with my stuff. I just wasn't sure if I should be using the "vbArray" to get the info, or if that was acceptable practice. If there's a more resource-friendly method of reading the Array to pass info... well, I guess that would be for another thread, huh?

I really appreciate all the help you two have given me! Marking solved.

[westconn1]

possibly the normal practice would be to return the entire array to the calling procedure then place the elements from the array to where ever required, especially if more than 1 element is to be returned