Introduction | Example | Tutorial | Applications | Comments

Introduction - Export Outlook Contacts to Excel

Use VBA to export your Outlook Contacts to Excel. With VBA macros, you can choose which Outlook Address Book properties you want to export to your Excel spreadsheet.

Once your contacts list is in Excel, you can play sort, filter and play with your data to your hearts content!

Let’s get right to it.


Example - Export Outlook Contacts to Excel

Sub ExportOutlookAddressBook()
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: Exports your Outlook Address Book to Excel.
'NOTES: This macro runs on Excel.
'       Add the Microsoft Outlook Reference library to the project to get this to run
Application.ScreenUpdating = False
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olAL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntry
Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olAL = olNS.AddressLists("Contacts") 'Change name if different contacts list name
ActiveWorkbook.ActiveSheet.Range("a1").Select
For Each olEntry In olAL.AddressEntries
     ' your looping code here
     ActiveCell.Value = olEntry.GetContact.FullName 'display name
     ActiveCell.Offset(0, 1).Value = olEntry.Address 'email address
     ActiveCell.Offset(0, 2).Value = olEntry.GetContact.MobileTelephoneNumber 'cell phone number
     ActiveCell.Offset(1, 0).Select
Next olEntry
Set olApp = Nothing
Set olNS = Nothing
Set olAL = Nothing
Application.ScreenUpdating = True
End Sub

Can't get enough VBA?
Subscribe to our free wellsrPRO VBA training program for more VBA tricks. I'll share my top VBA secrets and show you how to import my entire macro library directly into your spreadsheet with just one click.

I want to join the free wellsrPRO VBA Training program

Tutorial - Export Outlook Contacts to Excel

Here’s an abridged and censored copy of what my Outlook contacts list looks like when I show it in Excel:

Show Outlook Contacts in Excel

I’m going to make this bold so everyone can see it: This macro runs in Excel - not Outlook. You can modify it to run in Outlook if you’d like, but I designed it to run from Excel. To get the macro to work, you need to add a reference to the Microsoft Outlook Object Library from your Excel VBA Editor.

Microsoft Outlook Object Library

In the example above, I export your contacts full name, primary email address and cell phone number to a spreadsheet. You can pull different Address Book properties from your contacts list by entering a different property after = olEntry.GetContact. Here are a few of your choices:

Export Outlook Contacts with VBA

There are dozens of contact list properties to choose from!

If your Outlook address book is not titled Contacts, you can change the quoted string in the line that reads:

Set olAL = olNS.AddressLists("Contacts")


Exporting from Microsoft Exchange

If you get an error when running the above macro, it’s likely because your contacts are saved via Microsoft Exchange. To export your contact list from the Exchange, run this macro which replaces the For Loop with a Microsoft Exchange compatible loop:

Sub ExportOutlookAddressBook()
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: Exports your Microsoft Exchange Outlook Address Book to Excel.
'NOTES: This macro runs on Excel.
' Add the Microsoft Outlook Reference library to the project to get this to run
Application.ScreenUpdating = False
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olAL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntry
Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olAL = olNS.AddressLists("Contacts") 'Change name if different contacts list name
ActiveWorkbook.ActiveSheet.Range("a1").Select
For Each olEntry In olAL.AddressEntries
     ' your looping code here
     On Error Resume Next
     ActiveCell.Value = olEntry.GetExchangeUser.Name 'display name
     ActiveCell.Offset(0, 1).Value = olEntry.GetExchangeUser.PrimarySmtpAddress 'email address
     ActiveCell.Offset(1, 0).Select
Next olEntry
Set olApp = Nothing
Set olNS = Nothing
Set olAL = Nothing
Application.ScreenUpdating = True
End Sub

I want to thank readers ChuckD2011 and YasserKhalil for encouraging me to develop this alternate solution.


Application Ideas - Export Outlook Contacts to Excel

Years ago, I exported my Outlook contacts list at work to Excel just to see how big it was. There were 80630 entries at that time. Shocking! If your contacts list is that big, expect this macro to take a long time to run.

Once you get your email addresses in Excel, you can manipulate the data however you like. Because VBA is so powerful, you can choose to send an email to all the email addresses in your contacts list or a subset of your contacts. For example, you can choose to email all the people in your address book with a first name of “Jim.” I’m not sure why you would want to do that, but the point is you can!


Comments

It’s not often I feature a macro that interacts with Outlook. As a matter of fact, it’s been over a year since I showed you how to use VBA to search your Outlook emails. Because Outlook posts are so rare, I like to make them worth your time. I hope you found this one helpful - comment below to share how you’ll use this macro!

Also, subscribe to my email list, share this article on social media and follow me on Twitter for more great VBA content. Check out our VBA Q&A community if you still have questions or want to learn more.