Interesting: Fill out form on webpage using VBA in Outlook 1

[cthaxter]

Has anyone done this before? Any pointers?

Here's what I'm trying to do. Based on certain information housed within my mailbox in Outlook 2000, I want to go to a website and fill out a form there using the information that I have in Outlook.

How do I reference each form control on the webpage? Do I even need to, or can I do it some other way?

For illustration's sake, let's say that on the Startup event in Outlook, I want to look at all my items in my inbox, find any that contain the word "ebay" from a certain sender, then find a sentence in the body of the message that's in the format "keywords: keyword1 keyword2 keyword3", then start Internet Explorer (if it's not already open) and go to Ebay's home page and enter those keywords, submit the form, and the results will be displayed.

I already pretty much know how to do everything on the Outlook side, but I don't know what to do once the code starts Internet Explorer. I need to know how to go to a website, wait for the website to load, find the proper form control, and submit the form.

Any ideas? Thanks a million in advance.

-Christopher

 

[jonscott8]

You may want to head over to the MSDN and read a few articles on programming in the web browser. Here's you some VBA to log you into Tek-tips.

Sub LoginTT()
Dim oIE As Object
Set oIE = CreateObject("InternetExplorer.Application&quot
oIE.Silent = True
oIE.Left = 0: oIE.Top = 0
oIE.Navigate "

http://www.tek-tips.com"

WaitForIE oIE
oIE.Document.Forms(0).Handle.Value = "YourHandle"
oIE.Document.Forms(0).Pass.Value = "YourPassword"
oIE.Document.Forms(0).Submit
WaitForIE oIE
oIE.Visible = True
oIE.Silent = False
MsgBox "Click OK to close IE"
oIE.Quit
Set oIE = Nothing
End Sub
Sub WaitForIE(pIE As Object)
Do While pIE.Busy Or pIE.ReadyState <> 4
DoEvents
Loop
End Sub
Jon Hawkins

 

[cthaxter]

Thanks, that code looks promising... but when I run it, I get an error at line

oIE.Document.Forms(0).Handle.Value = &quot;YourHandle&quot;

&quot;Object doesn't support this property or method.&quot;

Any solutions?

 

[jonscott8]

Try replacing

oIE.Document.Forms(0).Handle.Value = &quot;YourHandle&quot;
oIE.Document.Forms(0).Pass.Value = &quot;YourPassword&quot;
oIE.Document.Forms(0).Submit

with

oIE.Document.Forms(&quot;pass&quot.Elements(&quot;Handle&quot.Value=&quot;Handle&quot;
oIE.Document.Forms(&quot;pass&quot.Elements(&quot;Pass&quot.Value=&quot;Password&quot;
oIE.Document.Forms(&quot;pass&quot.Submit

What version of IE are you running? Jon Hawkins

 

[cthaxter]

I was running 5.0 with the 128-bit-security service pack. I went ahead and upgraded to the latest version (6.0), but I still got the same results.

With both versions of Explorer, I still got an error, albeit a different one, using your suggested alternative. It said &quot;Object variable or With block variable not set.&quot;

I'm presuming that the only text that I need to substitute is that on the right side of the equation. Anything before it I leave intact, right?

I also checked my referenced libraries. I think I got all the ones that seem to have something to do with Internet Explorer. Could there be any library missing from my references? Here are the ones that I have referenced:

* Visual Basic for Applications
* Microsoft Outlook 9.0 Object Library
* OLE Automation
* Microsoft Office 9.0 Object Library
* DHTML Edit Control for IE5
* Microsoft Internet Controls
* iextag 1.0 Type Library
* Microsoft Browser Helpers

Do I need any others?

Any ideas why I still get both of those errors? What is your environment, since you obviously are not getting any errors? I'm running Office 2000 on top of Windows 98, by the way, if that would make any difference.

Anyway, thanks again for your help.

 

[jonscott8]

I'm running the above thru an Outlook macro. Outlook2K SR1, IE 5.5 SP2,Win98 SE.

AFA the references, IIRC, all you need for IE is the one labeled Microsoft Internet Controls. But for clarification, you should be able to run the code without it set.

Are you stepping thru the code in the debugger? Verify the WaitForIE procedure isnt returning prior to IE fully loading the page. To test that, add

oIE.Visible = True

just above the first call to the WaitForIE procedure and throw a breakpoint on the first call to the WaitForIE procedure. When the code breaks, switch to IE and wait for the page to finish loading. Then, finish stepping thru your code. Jon Hawkins

 

[cthaxter]

Excellent.

The

oIE.Visible = True

line did the trick. The code works beautifully.

I think you're right about the libraries. I don't think they necessarily have much to do with this. Nor, perhaps, do the actual versions of everything that we're running.

This begs the question, then: how do you transfer this to other forms on other web pages? Well, I went into the source code of yellowpages.com as a second test example, and discovered that pretty much everything I needed to know was somewhere between the <form> and </form> tags. The names of the controls were in the <input> and <select> tags. I reviewed some of the JavaScript to help me figure out exactly what is what.

I'd presume a refresher on forms programming in HTML and JavaScript would be helpful to really and fully be able to implement this type of code to manipulate any webpage from Outlook, right? Do you have any online resource suggestions?

For the record, here is the code I used in Outlook to look for Employment companies in Los Angeles in the online yellow pages. I changed a few minor details, such as the name of the object (just personal style) and an error handling line, because if the website doesn't load, one will get a VBA error. In any case, this code worked for me:

Sub LookupYP()
Dim objMSIE As Object
On Error GoTo exitprocedure
Set objMSIE = CreateObject(&quot;InternetExplorer.Application&quot

objMSIE.Silent = True
objMSIE.Left = 0: objMSIE.Top = 0
objMSIE.Navigate &quot;yellowpages.com&quot;
objMSIE.Visible = True
WaitForIE objMSIE

objMSIE.Document.forms(0).choicebusnameorcat.selectedIndex = &quot;0&quot;
'Index 0 is for Type of Business; 1 is for Name of Business
objMSIE.Document.forms(0).search.Value = &quot;Employment&quot;
objMSIE.Document.forms(0).query.Value = &quot;Employment&quot;
'Set equal to search.value when searching for business type, _
else set to &quot;&quot;
objMSIE.Document.forms(0).qname.Value = &quot;&quot;
'Set equal to search.value when searching for business name, _
else set to &quot;&quot;
objMSIE.Document.forms(0).scity.Value = &quot;Los Angeles&quot;
objMSIE.Document.forms(0).sstate.selectedIndex = &quot;5&quot;
'Index 5 is for California
objMSIE.Document.forms(0).Submit

WaitForIE objMSIE
objMSIE.Visible = True
objMSIE.Silent = False
Set objMSIE = Nothing

exitprocedure:
End Sub


Oh, and one more thing: How do you determine the

forms(0)

parameter value? I mean, is it always going to be just that, or will it be

forms(1)

for the second form on a web page, if there is one? Any further light on this matter?

Thank you very much for your help.

-Christopher Thaxter

 

[cthaxter]

Since my last posting, I came up with two further questions.

If the web page has two submit buttons, how to I call the one that I need? In other words, how do I specify with which button to submit?

Take

http://www.google.com,

for example. When you enter a search query, you can choose a Google search or an &quot;I'm Feeling Lucky&quot; search. The source code for this page contains the following code:

<input type=submit value=&quot;Google Search&quot; name=btnG>
<input type=submit value=&quot;I'm Feeling Lucky&quot; name=btnI>

How do I get it to do an &quot;I'm Feeling Lucky&quot; search?

Also, if I'm doing this from a custom form in Outlook, how do I make VBScript yield to Internet Explorer? DoEvents doesn't seem to work. Right now I just have it pause for 15 seconds while the web page loads (I have a dialup connection). I make it pause by using the system time and looping until 15 seconds have passed. How can I do the WaitForIE procedure in VBScript like you've suggested for VBA?

Thanks again.

-Christopher Thaxter

 

[jonscott8]

Yes, IIRC, the Index of the forms collection will depend on the amount of forms in the document.

oIE.Document.Forms(&quot;f&quot.Elements(&quot;btnI&quot.Click

will simulate the user pressing the &quot;I'm feeling lucky&quot; button.

In VBS thru the Win. Script Host, I'd use something like:

Sub WaitForIE
Do While pIE.Busy Or pIE.ReadyState <> 4
WScript.Sleep 500 'wait half a second, try again
Loop
End Sub Jon Hawkins