Mail Merge master class: How to merge your Excel contact database with custom letters in Word

In part three, you're finally ready to use the data you exported from Outlook to Excel, to create a mail merge with a Word document.

pcw office primary 2
Credit: Rob Schultz

The last piece of this three-part article is about merging your custom Word documents with your client list (exported from Outlook to Excel) for distribution. Part One explains how to selectively choose certain fields for exporting to Excel and Part Two explains how to customize, manage, and maintain that contact list in Excel. 

Step 1: Start the Mail Merge Wizard

1. Open an existing document in Word or type a new custom letter.

2. From the Mailings tab, click Start Mail Merge, then select Step-by-Step Mail Merge Wizard from the dropdown menu. A Mail Merge window opens on the right side of the screen.

Step 2: Select a starting document

1. The first window prompts to select a document type: Letters, Email Messages, Envelopes, Labels, Directory. Click each option on the list to view descriptions. For this exercise, choose Letters.

2. Click Next to continue, then choose one of these three options: Use Current Document, Start from a Template, or Start from Existing Document. When finished, click Next to continue.

figure1 mail merge step 1 select starting document JD Sartain

Figure1—Step 2: Select starting document.

Step 3: Select your recipients

1. The next window prompts you to select recipients. You can: Use an Existing List, Select from Outlook Contacts, or Type a New List. Since we added custom fields to our database and expect to add more in the future, we’re using an existing list from our Excel database. Click the first option, then click Browse.

2. Select your database from the Select Data Source dialog window, click Open, then choose the applicable table from the Select Table dialog window, Click OK.

NOTE: Confused? Excel files open on Sheet1 with additional tabs for Sheet2 and Sheet3. Excel calls these tables. When we saved the imported CSV file in Excel (as an .xlsx file), Excel used the filename (5-15-15 Contact List.xlsx) for the first tab (or table). So, choose the table called 5-15-15 Contact List.

3. Word displays the selected database in the Mail Merge Recipients window. As indicated by the instructions, use the options in the bottom panel to make changes and/or refine your database before you merge it with your document. When finished, click OK. Then click Next to continue.

figure2 mail merge step 2 select recipients JD Sartain

Figure2—Step 3: Select recipients.

Step 4: Insert an address block

1. The traditional letter format includes the date, contact information for both sender and receiver, plus a greeting. In our example, the sender’s contact information is printed on the corporate letterhead, followed by the date. To insert the recipient’s contact info, position your cursor two lines below the date and select Address Block from the Write Your Letter window pane.

2. The Insert Address Block dialog opens with options for how the address block is displayed on this document. Check the boxes that apply and select a name format from the list. Notice the Preview pane on the right displays how your Address Block will appear on your letter. If the Postal Address is checked, but not displayed in the Preview pane on the right, click the Match Fields button to help Word find the missing fields.

figure3 mail merge step 3 write letter address block JD Sartain

 Figure3—Step 4 - Insert Address Block

4. The Match Fields dialog opens. The field names in the left column are the default names for these fields. Notice that over half of the field boxes say “not matched.” Click the down arrow on the right to display the field names used in your Excel database.

5. For example, click the “not matched” field box arrow beside the line that says Address1. Select your field bStreet, and Word replaces the words “not matched” with bStreet. Repeat this same procedure for City, State, Postal Code, and Country, then click OK.

6. Word takes you back to the Insert Address Block dialog. Verify that the complete address now displays in the Preview pane, then click OK. Word places the field called <<AddressBlock>> on the second line beneath the date.

figure4 mail merge step 3 write letter address block JD Sartain

Figure4—Step 4 - Verify address block

7. Move your cursor down two lines from the Address Block. Click Greeting Line in the Mail Merge window pane. The Insert Greeting Line dialog opens. Select a salutation from the dropdown list under Greeting Line Format. Next, choose a format for the recipient’s name.

8. Next, choose a salutation for recipients with invalid or missing names. Use the arrows under the sample to browse and/or verify that the records are accurate.

9. If any of the fields are missing or inaccurate, click the Match Fields button and follow the instructions above to make repairs. When finished, click OK.

figure5 mail merge step 3 write letter greeting JD Sartain

Figure5—Step 4 - Insert a greeting line

10. The next option on this menu is Electronic Postage. Choose this option if you have the Electronic Postage software and an account set up to use this feature.

11. Click More Items to insert the custom fields we created in Excel. Move your cursor to the first placeholder in the letter, select the field “Expertise” from the More Items > Insert Merge Field list, then click the Insert button. Word places this field «Expertise» in your letter.

12. Repeat this procedure to insert “Innovations” and “Fees.” Note if any field is missing or inaccurate, click the Match Fields button and follow the instructions above to make repairs. When finished, click Close, then click Next to continue.

figure6 mail merge step 3 write letter more items JD Sartain

Figure6—Step 4 - Insert custom fields from the More Items list.

Step 5: Preview your letters

1. As soon as you click Next: Preview Your Letters, Word displays the first merged letter. Check the custom fields to ensure they printed correctly and in the right location.

2. If you want to view a specific record/recipient, click Find a Recipient, then enter the search criteria in the search box, or click the double arrows until you find the correct record.

3. If there’s a mistake in any of the merged fields, click Edit Recipient List and edit the database instantly.

figure7 mail merge step 4 preview your letters JD Sartain

Figure7—Step 5: Preview Your Letters

Step 6: Complete the merge

1. To complete the merge, select Print. Choose All, Current Record, or a range of records, and then click OK.

2. You can also select Edit Individual Letters and merge all the records, the current Record, or a range of records to a new file, then browse through the individual letters and make changes accordingly.

3. When finished, click OK; then print the letters.

figure8 mail merge step 5 complete the merge

Figure8—Step 6: Complete the Merge

To comment on this article and other PCWorld content, visit our Facebook page or our Twitter feed.
Shop Tech Products at Amazon
Notice to our Readers
We're now using social media to take your comments and feedback. Learn more about this here.