Excel VBA to load Outlook GAL data

ossuary

Active Member
Joined
Sep 5, 2004
Messages
279
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. :)
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

wnazzaro

New Member
Joined
Jun 28, 2010
Messages
7
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,512
Messages
5,523,323
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top