Mail merge master class: Exporting Outlook contacts to Excel for data management

Mail Merge is a wonderful tool for anyone who’s charged with managing and distributing a company’s communications. Regardless of whether the medium is electronic or hardcopy letters, it’s a huge, time-consuming task if the mailing list exceeds 300 contacts, and the communications contain multiple custom fields. For example, in addition to the standard contact information (name, address, city, state, etc.), additional custom fields might include the recipient’s skills, years of service, awards earned, salary, bonus structure, college degrees, previous employment, etc.

Outlook has a substantial Mail Merge feature that permits users to create some custom fields. And, it provides many special fields for personal information such as birthdays, spouse’s name, number of children, anniversaries, etc., plus multiple addresses and phone numbers. But there aren’t enough “custom” fields available for all the details mentioned in the first paragraph. And, continually changing this information for each recipient, for each mass mail distribution, is a prolonged and cumbersome chore.

The easiest, most efficient method for such a job, especially if your distribution tasks are repetitive and innumerable, is to maintain a database in Excel, updated from your Outlook contacts, and then merged with templates in Word. Part One of this three-part series covers how to export your contact list into Excel. Part Two covers how to maintain and manage that database, and Part Three shows how to create a document with custom merged fields from Excel, then save it as a template to reuse later.

1. Start file export

Open Outlook, then open your Address Book/Contacts folder (click the people icon at the bottom of the left panel). From the Contacts screen, click File > Open & Export > Import/Export. In the Import and Export Wizard dialog window, choose Export to a File, then click Next. In the first Export to a File dialog window, under Create a File Type, choose Comma Separated Values (CSV), then click Next. In the following (second) Export to a File dialog window, under Select Folder to Export From, choose Contacts, then click Next.

JD Sartain

2. Name and save exported file

In the third Export to a File dialog window, under Save Exported File As, enter a filename, then click Browse to enter the location for this file (such as C:UsersOwnerDocuments…). Browse to the folder(s) where you want this file saved, and then click OK. The fourth Export to a File dialog window appears and displays the path (folders and filename) of the new file’s location. If the path is correct, click Next.

JD Sartain

3. Map custom fields for export

The fifth Export to a File dialog window appears and displays the export information: Export “Contacts” from folder Contacts with a check box. You don’t need to export every field, because most of the fields are unused (and therefore, empty). Instead, click the button Map Custom Fields. In the Map Custom Fields dialog window, notice the instructions at the top: Drag the values from the source file on the left, and drop them on the appropriate destination field on the right. Drag the item from right to left to remove.

4. Clearing data fields you don’t need

First decide if you want to delete all the fields (around 60+) and only drag over the fields you need, or keep the Default fields and remove those you don’t need. Click the Clear Map button to start with a clean slate or the Default Map button to keep all the fields and then remove the unnecessary ones. Easiest method? Click the Clear Map button and start clean because it’s more likely that you’ll only need a few of these fields for this project.

5. How to move custom fields

Regardless of the choice you make above: click a field on the left, hold down the left mouse button, and drag it to the right. Do the reverse to remove fields from the right.

JD Sartain

Note that only the fields on the right—of the Map Custom Fields dialog window in the To: Comma Separated Values pane—are exported to the CSV file. When finished, click OK. Then, in the sixth Export to a File dialog window, click the Finish button. The Import and Export Progress dialog shows the export progress.

6. Check your exported file

When it’s complete, go to the location (path/folders) where you saved the file and verify the file and file size (to ensure the data transferred and not just an empty file). In our example, the filename is 5-15-15 Contact List.CSV and the file size is 521KB. Check back next week for Part Two: How to Maintain and Manage Your Contact Database in Excel.

JD Sartain