How can you download the Outlook global address list to excel 1

[Pack10]

Is there a way to download the Outlook Global Address List to Excel, or CSV? If I can get hold of that list, I could write simple
queries to compare the GAL with the names we have in our databases. I tried to attach as if it were a new table in Access
but I receive a message that that "that object or class does not support the set of events"

 

[SkipVought]

hi,

You can export your global as a .csv and then IMPORT (no OPEN) into an existing Excel workbook. First time the IMPORT takes working thru the IMPORT Wizard, and subsequent IMPORTs can be accomplished with REFRESH of the QueryTable.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

>I tried to attach as if it were a new table in Access

You should certainly be able to import it as a new table - providing that you have Outlook installed on the machine on which you are trying to do the import.

In Access 2010 it's under External Data/More/Outlook Folder then elect to 'Import the source data into a new table in the current database' (or whichever option is most appropriate), then click 'OK'. In the resulting dialog (you might be prompted to log into Outlook first) choose "Global Address List". And you are basically done.

On older version of Access it is under File/Get External Data/Import, then open the drop-down menu for 'Files of Type' in the resulting dialog and select Exchange() from the list. This will result in more-or-less the same dialog as in Access 2010, where you simply choose "Global Address List". And you are basically done.

or, if you want to do it all in code ...

[blue][green]' requires reference to appropriate Outlook library[/green]
Public Sub GetGALEntries()
    Dim myAddrList As AddressList
    Dim myAddrEntries As AddressEntry
    Dim myEntry As ExchangeUser
        
    [green]' Outlook 2007 and later ... [/green]
     Set myAddrList = CreateObject("Outlook.Application").Session.GetGlobalAddressList
     
    [green]' Outlook versions prior to 2007
    ' but if version IS earlier than 2007 then ExchangeUser (and consequently GetExchangeUser) does not exist, and you wuill have to retrieve exchnage properties using PropertyAccessor, which is a bit of a pain[/green]
    ' Set myAddrList = CreateObject("Outlook.Application").GetNamespace("MAPI").AddressLists("Global Address List")
    
    For Each myAddrEntries In myAddrList.AddressEntries
        Set myEntry = myAddrEntries.GetExchangeUser
        If Not myEntry Is Nothing Then
            [green]' Example output[/green]
            Debug.Print myEntry.FirstName, myEntry.LastName, myEntry.PrimarySmtpAddress
        End If
    Next
End Sub[/blue]

 

[Pack10]

On my system I receive an error message which states....Object or class does not support the set of events
So I found some code to pull the names from the global address list. That works fine.
I am able to save them to a table. But I dont see where the email address is. If someone knows where it is or
what the syntax is for pulling the email address from the gloabl address list, that would do it.


Public Sub Get_EM()


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

Set NameSpace = MyObj.GetNamespace("MAPI")
Set GAL = NameSpace.AddressLists("Global Address List")
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

rs.AddNew
rs!FirstName = GAL.AddressEntries.Item(i).Name
rs!EmailAddress = GAL.AddressEntries.Item(i).Address
rs.Update

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

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

End Sub

 

[strongm]

>On my system I receive an error message which states....Object or class does not support the set of events

To clarify: this happens when you try to import to Access? At what point during the process I outlined for importing (above) does this error occur? This suggests there may be a problem with your Access installation.

>If someone knows where it is or what the syntax is for pulling the email address from the gloabl address list
Er... did you read my code? It does recover the SMTP address - as long as you are using Outlook 2007 or later. Previous versions of Outlook only expose a much more limited set of properties from the GAL (and then my advice of using PropertyAccessor unfortunately would also not work, since I'd forgotten that it too was only introduced with Outlook 2007).

With previous versions, therefore, you need something like Outlook Redemption, which provides full access to the underlying Extended MAPI model

So, let's see:

What version of Access are you using?
What version of Outlook are you using?
What version of Windows are you using?
What version of Exchange are you using?

 

[strongm]

>Object or class does not support the set of events

Others with the same problem

One possible fix

 

[Pack10]

I have my routine working. I was able to load a table from the global email list and thanks to code that was posted i was able
to re work it.
We send out emails quarterly to certain individuals. So we need a way to check if the person is still with the firm and HR and IT
are no help. I would like the next step to be where the user enters the name, and we do a lookup (find, seek) against the GAL
pulling the employee info. I table is too large to send around, and we dont have the Sharepoint site yet.