If you want to export Outlook contacts to Excel for email marketing, business contacts organization, or backup purposes, you’ve come to the right place!
You could be using Outlook as the email client for personal and professional emailing purposes. Did you know that you could effortlessly export contacts from Outlook to Excel to create a Vcard of business or personal contacts?
That’s right! You can. You no longer need to remember emails or write them down on a notepad from Outlook and enter the email in an Excel worksheet.
In this article, I’ll demonstrate how you can export Outlook contacts into an Excel worksheet in various ways and in different Outlook apps. Let’s get started!
Reasons to Export Outlook Contacts to Excel
Primarily, you’d want to export contacts from Outlook to Excel as a measure to safeguard the extensive list of email contacts you’ve amassed over the past decade or even longer.
In an unpredictable digital landscape, including potential security breaches or unforeseen events impacting Microsoft 365 or Microsoft 365 Exchange, having a comprehensive backup of your email addresses becomes a vital necessity.
Other major reasons to export email addresses from Outlook are as below:
- Transfer contacts to other platforms or email services that accept Excel-compatible formats.
- Organize and categorize contacts for better management and quick access.
- Use Excel’s features for data analysis, sorting, filtering, and reporting on contact details.
- Easily integrate contact information with other applications or software.
- Customize contact lists, add additional details, or create specific views for various purposes.
- Share contact lists with colleagues, team members, or partners who don’t use Outlook.
- Create a handy contact list in Excel for email marketing and email newsletter sending purposes.
- Import contacts into Customer Relationship Management (CRM) systems or other databases.
- Access contact information without an internet connection, as you can store Excel files locally.
Now that you know the basis of exporting contacts from Outlook, find below several methods that you can try out:
Copy and Paste Outlook Contacts to Excel Worksheet
This is a primitive way of exporting your email contacts from Outlook to Excel. Should you need to send a few contact names and emails to Excel, you can follow this method. Also, the method is suitable for exporting email contacts when you do it rarely.
Start by creating an Excel worksheet as shown above. I included only the Name and Email columns for presentation purposes. You could add more fields as shown on Outlook, like Company, Phone, Workplace Address, and so on.
Now, open your Outlook desktop or web app and navigate to the Contacts section. You should see a list of Outlook email contacts on the left side panel and details of the selected contact on the right side.
Select a contact you want to export to Excel. Highlight the full name and press Ctrl + C to copy the name text.
Go to your Excel worksheet and paste the name into the first cell (
A2) under the Name column.
Now, go back to the Outlook app and copy the email address of this contact.
Paste the data below the Email column header into the cell
B2 by pressing Ctrl + V.
Repeat the steps mentioned above until you’ve copied all the necessary email contacts.
Export Contacts From Outlook to Excel Using Address Book
This is yet another manual method to send Outlook contacts to Excel but in bulk. Here’s how:
- Open Outlook and go to the Mail section.
- Click the New Email button to get an email draft.
- On the ribbon menu of the email draft window, click on the Address Book button inside the Names block.
- You should now see the Offline Global Address List dialog.
- Select the first contact in the list and drag the mouse cursor until the end to highlight all the contacts.
- Click To to enter the selected email addresses into the To field of the Global Address list.
- Click OK to enter emails to the To field of the draft email.
- Copy emails from the To field.
So far, you’ve successfully copied all of your Outlook email contacts. Now, follow these steps to paste it on an Excel worksheet:
- Open an Excel worksheet where you want the Outlook email contacts.
- Select any cell.
- Press Ctrl + V to paste the data
Excel will paste the data column-wise. To convert that to rows, follow these instructions:
- Highlight a cell where you want the email contacts row-wise.
- Copy and paste the following formula into the cell:
- Press Enter to transpose horizontal email contacts to vertical structure.
- Copy the transposed data again.
- Press Ctrl + Alt + V to call the Past Special dialog.
- Click Values and choose OK.
- The spillover data resulting from the TRANSPOSE formula will be converted to text strings.
Export Outlook Contacts Using the Import/Export Tool
The Import/Export tool enables you to export email addresses from Outlook in a CSV file. Then, you can import the CSV file in Excel to retrieve your email contacts. Here are the steps to try:
- Click the File tab on the Outlook app.
- Click Open & Export on the left side navigation panel.
- Now, click Import/Export on the right side.
- You should see the Import and Export Wizard.
- Select the Export to a file option and click Next.
- On the Comma Separated Values window, click Next again.
- Click Contacts on the folder selection screen and click Next.
- Choose a destination drive and file name and hit Next.
- Click Finish to complete the export process.
You’ll find the exported contact data in CSV format in the designated directory. Open the CSV file using your Excel app to access the email contacts data.
Scroll to the right of the screen until you find a bunch of email addresses. That’s the data you’ve been looking for.
Export Contacts From Outlook to Excel Using Outlook VBA
If you’re okay with creating a few lines of Outlook VBA codes to automate the entire process, this is the perfect method for you. Find below the script to automate Outlook contacts exporting and also learn how to do it for the first time:
Add Developer Tab to the Outlook App
- Click the File tab on the Outlook home screen.
- On the left-side menu that appears, choose Options.
- On the Outlook Options dialog, click Customize Ribbon.
- Checkmark the Developer checkbox on the Main Tabs box.
- Click OK to apply the changes you made.
Create the Script
- Click the Developer tab on the Outlook ribbon menu.
- Go to the Code commands block and click on the Visual Basic icon.
- Outlook VBA editor should now show up.
- Click Insert on the VBA toolbar.
- Choose Module from the context menu.
- Copy and paste the following script inside the blank module:
Sub ExportAddressBookToExcel() Dim objOutlook As Object Dim objNamespace As Object Dim objFolder As Object Dim objItem As Object Dim xlApp As Object Dim xlWorkbook As Object Dim xlWorksheet As Object Dim iRow As Integer ' Create an Outlook Application object Set objOutlook = CreateObject("Outlook.Application") ' Get the MAPI namespace Set objNamespace = objOutlook.GetNamespace("MAPI") ' Set the Outlook folder as the Contacts folder Set objFolder = objNamespace.GetDefaultFolder(olFolderContacts) ' Create a new Excel application Set xlApp = CreateObject("Excel.Application") ' Create a new Excel workbook Set xlWorkbook = xlApp.Workbooks.Add ' Create a new worksheet in the Excel workbook Set xlWorksheet = xlWorkbook.Sheets(1) ' Set the initial row in Excel iRow = 1 ' Loop through the contact items in the Outlook Contacts folder For Each objItem In objFolder.Items If objItem.Class = olContact Then xlWorksheet.Cells(iRow, 1).Value = objItem.Email1Address iRow = iRow + 1 End If Next objItem ' Save the Excel workbook to a specific location xlWorkbook.SaveAs "D:\File.xlsx" ' Clean up and close Excel xlApp.Quit Set xlWorksheet = Nothing Set xlWorkbook = Nothing Set xlApp = Nothing ' Release Outlook objects Set objFolder = Nothing Set objNamespace = Nothing Set objOutlook = Nothing End Sub
- Click the Save button.
- Close the Outlook VBA editor.
Run the Script
- Press Alt + F8 to call the Macros dialog box.
- Choose the ExportAddressBookToExcel macro from the list.
- Click Run.
You should find a CSV file containing all the email contacts from the Contacts folder of your OIutlook desktop application in the drive D of your PC.
The above is a screenshot of the exported email contacts in an Excel (XLS) file using Outlook VBA. The script also cleans and structures your exported data so that you can find the email addresses quickly.
There are comments on the script starting with single quotes. Read those comments to learn which code element you can modify to personalize several references of this VBA script, like changing the destination directory for the output file or exporting a group contact list instead of the whole contacts list.
In this guide, you’ve been introduced to a range of options for exporting email addresses from Outlook to Excel. These include two manual approaches for occasional use and two automated methods designed for routine exports of your Outlook contacts to Excel.
Select the method that aligns most closely with your unique requirements when it comes to exporting your Outlook contacts. Your experience with these methods matters, so we encourage you to share your insights in the comments below.
Your feedback can be invaluable to others seeking the best approach for their email contact management needs.