Excel VBA to load Outlook GAL data

[ossuary]

Hello,

I'm trying to find a way to pull several pieces of information out of the Global Address List in Outlook 2007. In Excel, I will have either the person's name, the person's email address, or the person's alias, and I would like to use that information to pull the following pieces of information from the GAL:

Last name, first name, and/or full name
email address
alias

(in other words, I want to use 1 piece of information to capture all 3 pieces of information for each person)

I've searched on these forums and elsewhere on the internet, but can't seem to track down an example that works properly or meets my needs. Unfortunately, due to limitations of our office network, I do not have access to the CDO reference library, and have been told by our IT department that they cannot or will not install it on our systems... so the only reference I can use is the Microsoft Outlook Object Libary.

Can anyone give me some pointers or example code that I could use to extract the necessary information from Outlook?

Thanks in advance.

 

[ossuary]

Can anyone help with this?

 

[wnazzaro]

This may help you get moving in the right direction. This was written for Outlook 2003, so I don't know if it all still works.

Look into the Outlook.propertyAccessor as that may help.

If you have the Alias in Excel in Column A, this will return the Full Name from Outlook.

Public Sub GetUsers()
    Dim myolApp As Outlook.Application
    Dim myNameSpace As NameSpace
    Dim myAddrList As AddressList
    Dim myAddrEntries As AddressEntry
    Dim AliasName As String
    Dim i As Integer, r As Integer
    Dim EndRow As Integer, n As Integer
    Dim myStr As String, c As Range
    Dim myPhone As String
    'Dim propertyAccessor As Outlook.propertyAccessor  'This only works with 2007 and may help you out
    
    Set myolApp = CreateObject("Outlook.Application")
    Set myNameSpace = myolApp.GetNamespace("MAPI")
    Set myAddrList = myNameSpace.AddressLists("Global Address List")
    
    Dim FullName As String, LastName As String, FirstName As String
    Dim StartRow As Integer
    
    EndRow = Cells(Rows.Count, 3).End(xlUp).Row
    
    StartRow = InputBox("At which row should this start?", "Start Row", 4)
    
    For Each c In Range("A" & StartRow & ":A" & CStr(EndRow))
        AliasName = LCase(Trim(c))
        c = AliasName
        Set myAddrEntries = myAddrList.AddressEntries(AliasName)
        
        FullName = myAddrEntries.Name
        FirstName = Trim(Mid(FullName, InStr(FullName, "(") + 1, _
                        InStrRev(FullName, " ") - InStr(FullName, "(")))
        LastName = Right(FullName, Len(FullName) - InStrRev(FullName, " "))
        LastName = Left(LastName, Len(LastName) - 1)
        
        c.Offset(0, 1) = FirstName
        c.Offset(0, 2) = LastName
        c.Offset(0, 3) = FirstName & " " & LastName
    Next c
End Sub

 

[JP2112]

I have a few examples that you may be able to adapt:

http://www.jpsoftwaretech.com/link-excel-cells-to-outlook-data-fields/
(shows how to grab information from GAL)

http://www.jpsoftwaretech.com/quicksteps-week-part-2/
(shows how to grab manager name from GAL entry)

http://www.jpsoftwaretech.com/extract-gal-members-to-excel/
(how to write GAL information to Excel)