Yeah, I know there are a lot of posts out there about this already. So here’s another.
Truth be told, when I first went searching for items on this topic, I couldn’t find what I was looking for. Granted, it’s not outside the realm of possibility that I simply suck when it comes to a decent Google search. But even when my word choice did happen to land me luckily on a site with information on this topic, I still never really found what I was looking for.
What I really needed was a way to extract some particular columns of data from the Outlook address book. I was looking for Job Title specifically, and Alias if I could get at it. After poking around the web for a bit, and finding some snippets of code here or there, I finally wised up and decided to just follow the Outlook object model.
I love a good object model. Knowing the parent/child relationships from one object to another makes navigating through the application a cinch. For me, the thing I like most about knowing those parent/child relationships, and knowing what the various collections of objects are, is that it lets me iterate through all of the items in those collections.
That’s what I ended up doing with my routine. Once I found the right object that held the properties I was looking for, it was pretty easy to walk back up the model and make sure that I had all the necessary objects in the code.
In this case, because my company is on the Exchange Server, the Outlook object I needed was the ExchangeUser. It has all of the properties I would need, and then some. In addition to what I pulled, you can get address, phone numbers, manager, all kinds of good things. From there I just needed to work backward to figure out what collection did ExchangeUser belong to, and then back up from there. Once that’s been figured out, a simple For..Next loop does the trick.
The only other nuance here is that I need to run this from Excel, so that I could write the data to a worksheet from which I could slice and dice. No problem. Microsoft includes the ability to run other application object models from Excel. All you need to do is to set a reference to the MS Outlook Object Library, and then the Outlook object model is available to you in Excel. The rest is history.
The code for extracting the data is below. The list of properties the ExchangeUser offers can be found here. Enjoy.
Public Sub GetOutlookExchangeUserInformation() ' This goes into the Global Address List for the MS Exchange Server ' and returns a selection of data to a worksheet. ' To get a view off all the potential data poitns that Outlook contains, ' see the following URL: ' https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook.exchangeuser_properties.aspx ' Variable declarations. Dim olApp As Outlook.Application Dim olNameSpace As Namespace Dim olAddrList As AddressList Dim olAddrEntry As AddressEntry Dim olExchgnUser As ExchangeUser Dim sh As Worksheet Dim lCnt As Long ' Set the Outlook object variables Set olApp = CreateObject("Outlook.Application") Set olNameSpace = olApp.GetNamespace("MAPI") Set olAddrList = olNameSpace.AddressLists("Global Address List") ' Create a new worksheet. Set sh = ThisWorkbook.Worksheets.Add ' Add some headers for our data. With sh .Cells(1, 1) = "NAME" .Cells(1, 2) = "FIRST NAME" .Cells(1, 3) = "LAST NAME" .Cells(1, 4) = "ALIAS" .Cells(1, 5) = "JOB TITLE" .Cells(1, 6) = "DEPARTMENT" End With ' Start the counter in the second row. lCnt = 2 ' Iterate through the address entires in the address list. For Each olAddrEntry In olAddrList.AddressEntries ' For each entry, set the an exchange user object. ' I'm using t exchange user object because I find that ' most companies using Outlook are set up on MS Exchange Server. ' You can also use the GetContact whcih will return the ' the same information from the Outlook Contact Address Book ' (as opposed to the Global Address List). Set olExchgnUser = olAddrEntry.GetExchangeUser ' Turn off error handling, because occasionally you hit a ' record with nothing in it and it throws an error. On Error Resume Next ' Write the Outlook data to the worksheet. With olExchgnUser sh.Cells(lCnt, 1) = .Name sh.Cells(lCnt, 2) = .FirstName sh.Cells(lCnt, 3) = .LastName sh.Cells(lCnt, 4) = .Alias sh.Cells(lCnt, 5) = .JobTitle sh.Cells(lCnt, 6) = .Department End With ' Because an exchange server could have tens of thousands of entries, ' which will cause this routine to run for several minutes, ' update the status bar to let the user know that we are in fact ' still running and not hung up. Application.StatusBar = "Processing record " & lCnt & "..." ' If we did not hit an error, increment the counter. ' (If we did hit an error, we skip this because if we do not, ' we'll get a blank row in the middle of the data set.) If Err.Number = 0 Then lCnt = lCnt + 1 ' Clear any error. Err.Clear ' Reset the error handling. On Error GoTo 0 Next olAddrEntry ' Clear the status bar. Application.StatusBar = "" ' Prompt the user that we've finished. MsgBox "Extract done." End Sub
13 thoughts on “Excel Geeking: Extracting Outlook Address Book Information To Excel Using VBA”
Thanks for the useful information above, however I am looking for a slightly modified version of this.
I want to provide the email address of the person, have it look for that and then return other information (department, phone number, etc).
How can I use this code to search by email and return other properties such as department, number etc.
Also – how can I generalize that process – is there a list of what those field names are called, is there a way to generate a list?
I am also wondering how i would get the email address itself in the list. The code works great and I would love to use it, but need the email addresses. Also, what if i do not want the Global Address list, but want a list under “Other Contacts”. Or does it not work like that? Thanks for the work on this.
Great write-up. I too struggled to find an example which simply extracted from Exchange to Excel.
EC, the e-mail address property is accessible via the “.PrimarySmtpAddress” property. Add this to the example:
sh.Cells(lCnt, 7) = .PrimarySmtpAddress
This was very helpful.
this runs on the entire GAL.
How do I do it for just a few addresses in the the cells?
The quickest way is probably to use a Select.. Case on one of the properties of the ExchangeUser object…
I am a complete newbie on this. Could you elaborate? It will very helpful
Not for a bit, I’m moving tomorrow so I’m packing packing packing tonight. I’m a couple of days I’ll be back online.
Thanks for sharing and it was very helpful.
I have the employee names(.Name attribute) displayed in column A.Need to extract the email address from outlook and display it in column B. Can you please advise how to modify this code.
This is a great and simple tool for doing something I’ve been trying to do for years. Just one question though – is it possible to get the size (number of entries) of the GAL, like you would for an array or table, without cycling through and counting them?
I haven’t tested it, but you could try olAddrList.AddressEntries.Count.
Can suggest me on my earlier request…thanks
FYI – you need to go into the vba editor (alt+f11), then hit tools->references and scroll down to Outlook.
Does anyone know if you could potentially limit the query to a specific “tree” or domain. Not sure the A/D lingo, but we have 100,000+ records and I dont want to pull all of them.
It probably possible to do so. I haven’t explored the object model for Outlook all that much, and I’m no expert on AD, so I’m not sure what branches are available for you to traverse. It probably also depends on how AD was set up by your company. I’d say add a counter variable that dumps you out after a couple of thousand records, examine the results, and adjust your code accordingly.