How would I pull all names and email addr from Outlook global address list into an access table.

[Pack10]

I am trying to load the Outlook Global Address List into a table in a db. I thought I had it, but its missing people.
If I go into Outlook I can find the person....but my code is not and i can't figure out why.
I found the code and have adjusted it to write to a table.


Public Sub Get_EM2()

'--------This function pulls names and email addresses from the global address book -------------------------

Dim MyObj As New Outlook.Application
Dim NameSpace As Outlook.NameSpace
Dim GAL As AddressList, sName As String, allGAL As AddressEntries
Dim db As Database
Dim rs As Recordset
Dim i As Long

Dim entry As AddressEntry
Dim exUser As ExchangeUser
Dim strFirstName$, strLastName$, strBusPhone$, strMobilePhone$, strEmail1$

Set NameSpace = MyObj.GetNamespace("MAPI")
Set GAL = NameSpace.AddressLists("Global Address List")
Set allGAL = GAL.AddressEntries
GAL.AddressEntries.Sort

Set db = CurrentDb
Set rs = db.OpenRecordset("empTable")

For i = 1 To GAL.AddressEntries.Count - 1
'.List1.AddItem GAL.AddressEntries.Item(i).Name

Set entry = allGAL.Item(i)
If entry.AddressEntryUserType = olExchangeUserAddressEntry Then
Set exUser = entry.GetExchangeUser
' check for blank last name

If exUser.LastName <> "" Then
' Get field values
strFirstName$ = exUser.FirstName
strLastName$ = exUser.LastName
strBusPhone$ = exUser.BusinessTelephoneNumber
strMobilePhone$ = exUser.MobileTelephoneNumber
strEmail1$ = exUser.PrimarySmtpAddress

rs.AddNew
' rs!FirstName = GAL.AddressEntries.Item(i).Name
rs!FirstName = strFirstName$
rs!LastName = strLastName$
rs!emailAddress = strEmail1$
rs!BusinessTelNum = strBusPhone$
rs.Update

Debug.Print "Working On: " & i
End If
End If
Next i

Debug.Print "Successful Completion..."

rs.Close
Set NameSpace = Nothing
Set GAL = Nothing
Set db = Nothing
Set rs = Nothing

End Sub

 

[dhookom]

I only see successful debug.print in your code. You should have posted enough here that you would use TGML code tags.

[COLOR=#4E9A06]' Else code added to see if there is an issue[/color]
             Else
                Debug.Print "No Last Name"
            End If
         Else
            Debug.Print "Not correct type: " & entry.AddressEntryUserType
        End If
    Next i

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Pack10 . . .

Its been a long time for me since I've done any work in Access ... thru OutLook ... and I've been digging thru your request. I hav'nt come across anything yet that says yay or na ... but there are a few simple things that could prevent it. Example ...

[blue]change: Dim db As Database
        Dim rs As Recordset
to:     Dim db As [purple][b]DAO.[/b][/purple]Database
        Dim rs As [purple][b]DAO.[/b][/purple]Recordset[/blue]

Let me know how this fairs! ...


See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[Pack10]

It has something to do with outlook exchangeuser I believe. The names that I dont capture, when I look them up in outlook have no dields filled in...when I look up a name that I do capture, all the fields like address, business telephone and email are populated.

 

[dhookom]

So is your question resolved or are you stillookong for assistance? Have you tried either of the suggestions?

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

hi
No, its still not resolved. I am capturing exchange users. When I check someone (by looking them up in Outlook) that the code finds, I find their Outlook record fully populated from the general tab, and those that I don't find, their Outlook record only shows the SMTP email address and perhaps the state but none of the details like phone # zip code address etc......
Does anyone know or can explain the Exchange User...what the object means code wise.

 

[dhookom]

This sounds like an Exchange/Outlook question. Have you searched other resources?

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

yes, I havent found a code scenario like what I am trying to do. Most people seem to have some sort of lookup routine.
Essentially i want to download the GAL into a table to compare against spreadsheets we get from our users.
We do a (email) mailing quarterly and would like to cross reference the list to the Global Address list to make sure we have the correct address. This is a time consuming task to do manually. So, thats why I need every email address. This is a large company with over 70K employees.

 

[strongm]

>The names that I dont capture, when I look them up in outlook have no dields filled in

Are you sure these missing names names are GAL entries, and not Contacts or other Outlook (rather than Exchange) address books?

 

[Pack10]

They could be Outlook (rather than Exchange) address books...they probably are....how do I get them?

 

[strongm]

>They could be Outlook (rather than Exchange) address books.

Well, it may be that you are using a combination of different address books.

I think you need to research how Outlook and Exchange use address books and the different types before proceeding. And then determine how your company actually uses them before proceeding with this task.

Looking at your posts on this subject since the beginning of October it looks like you are hoping/expecting all your company email addresses to be in (single) Global Address list. This may not be the case.