Calling IE with access 2007 vba

[trixxnixon]

this code worked in access 97 and i am getting an error on the internet explorer variable. Is there an updated way of having vba open this program?

Function getEmail(enumber As String)
Dim url As String
Dim IE As InternetExplorer
Dim HTMLDoc As HTMLDocument
Dim html As String
Dim atLocation As Long, startLocation As Long, endLocation As Long
Dim x As Long

'Create a new instance of Internet Explorer
Set IE = Nothing
Set IE = New InternetExplorer

'Define the URL we will be loading
url = "http://people.aflacworkplace.com//Person.aspx?accountname=AFLHQ\" & enumber

'Ensure that the IE Window is hidden and navigate to the website defined above
IE.Visible = False
IE.navigate url

'Loop until the website is completely loaded...
Do While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
    DoEvents
Loop

'Save the html behind the page we are on to a variable (html) and determine which character contains the first @
html = IE.Document.body.outerHTML
atLocation = InStr(1, html, "@")

'Loop backwards from the "@" location to find the opening ":" (the E-Mail address is first listed in the html as 'email@aflac.com')
For x = atLocation To 0 Step -1
    If Mid(html, x, 1) = ":" Then
        startLocation = x + 1
        Exit For
    End If
Next

'Loop forwards from the "@" location to find the closing "'"
For x = atLocation To (atLocation + 100) Step 1
    If Mid(html, x, 1) = "'" Then
        endLocation = x
        Exit For
    End If
Next

'Close IE and Destroy Object
IE.Quit
Set IE = Nothing

'Return Value
getEmail = Mid(html, startLocation, endLocation - startLocation)
End Function

[Rabbit]

Try CreateObject("I nternetExplorer .Application")

[trixxnixon]

It seems like most of this doesn’t really work, and the problem is focused around actually successfully calling IE and opening the source.

maybe you can offer a better process for getting this done.


What im doing is pulling the id number of the logged in user, accessing the active directory via internet explorer, converting the webpage to the source, storing it in an html document, searching for the @, and generating an email(separate module)
I am not the original author of this code and am not a vba expert at all. fixing this is a bit above my head.

[Rabbit]

Well, you're trying to pull data from Active Directory, you could use an LDAP query.

The url string looks like it is incorrectly formatted. Have you tried outputting the string and then attempting to go to the url directly?

[TheSmileyCoder]

Which line does the debugger point to when it errors, and whats the exact error number and description.

[trixxnixon]

its stopping at "dim html as HTMLdocument"
i cant find the correct reference to add within the menu.
Is there a way I can install it manually?

[Rabbit]

That line doesn't exist in your code. You have dim htmldoc as HTMLdocument but not dim html as HTMLdocument. You're not even using the htmldoc variable so you can get rid of that line.