Word Mail Merge Using Excel

It’s that time of the year when the kids return to school and politicians send out letters. Based on the mail I’m getting, I think folks might benefit from learning how to create mail merge letters with Microsoft Word and Excel. Even if you’re not running a campaign, you’ll probably need to create custom letters for a mailing at some point.

Contrary to popular belief, I don’t dislike politicians. I like some. What I dislike is getting impersonal letters. I realize that in some cases, it makes sense to do a generic letter. In most cases, I think people can insert a little customization. This Word mail merge process isn’t difficult.

Understanding the Mail Merge Process

It helps me to think of the mail merge process in terms of three files.

  1. Mail merge letter with codes for the personalized fields
  2. A data source file such as Excel that has a row of information for each recipient
  3. Your final Word letter file with the personalized letters

The mail merge process uses Files 1 + 2 to create File 3.

The first step is to decide what to personalize. This requires knowing the content of your letter and your recipients. Using politicians as our example, they can get a CSV data extract about voters from their election office. This file usually contains fields for:

  • First Name
  • Last Name
  • Address 1
  • Address 2
  • City
  • State
  • Zip

You may add other columns to meet your needs. For example, you might add a field called “Event”. This could show an event time to meet the candidate.

Fixing Data Records before the Merge

Before writing your letter, you should check your data file. I don’t think I’ve seen a data source file that didn’t need correction. This is especially true if you didn’t input the information. Some common problems I look for are:

  • Capitalization errors such as all CAPS
  • Duplicate records
  • Missing data
  • Initials as a first name
  • Missing apostrophes in last names (Oneill)
  • Misspellings

The top two items can be fixed using the Excel add-on ASAP Utilities. You don’t need to worry about deleting columns you won’t use in the letter.

You may need to worry about the First name field if you want to convey that you know someone. Your data source may have a formal name such as “Robert” but you know the person as “Rob”. Salutations can also be an issue if you’re not familiar with first names. I can attest to knowing female “Michael”s and male “Merideth”s.

I like to seed the file with a fictitious record for myself. I use this record to see how long it takes to receive the mail and to spot if anyone else has used the file. If I get a new mailing with that name and address, I know my source file is being reused.

Before saving your Excel file, make sure you have a header row with descriptive column names. This makes it easier to match and merge fields. There is a link at the bottom to download a sample Excel file.

Linking to the Data Source File

As with many tasks, Microsoft created a helpful wizard. Before starting, make sure you have created your recipient list with the fields you wish to use.

  1. Open Word with a blank document or your letterhead.
  2. From the Mailings tab, select Start Mail Merge
  3. From the Start Mail Merge menu, select Step by Step Mail Merge Wizard.The Mail Merge task pane will appear on the right side of your screen.
  4. Word's mail merge task pane

  5. Click the Next: Starting document link at the bottom of this panel. The Wizard advances you to Step 2.
  6. Keep the default option for Current document and click the Next: Select recipients link at the bottom.
  7. Click the Browse … link in the middle of the panel. This opens the Select Data Source dialog.
  8. select data source dialog

  9. Navigate to your Excel file and click Open. This opens the Select Table dialog. If you named your Excel worksheet, the name will show. Otherwise, it will appear as Sheet1$.
  10. excel table

  11. Check the box at the bottom for First row of data has column headers.
  12. Click OK. Your file name should show where the Browse… link was on the Mail Merge task pane.
  13. Click Next again.

Adding your Address Merge Codes

The Mail Merge wizard should be on Step 4 of 6. Now that we’ve assigned the data source, we can insert the merge fields in our letter. The first code most people start with is the address. While you can insert merge codes for each address element, Word allows you to enter all the fields with an Address block.

  1. On the Mail Merge task pane, click Address block… .The Insert Address Block dialog should appear.
  2. mail merge address block

  3. Check the options that best suit your addresses.
  4. Click the Match Fields… button in the lower right.
  5. Fix any address fields that you’ll use which show as (not matched) by selecting the proper field using the arrow control.
  6. Click OK.

On your Word document, you should see «AddressBlock». This code represents the address fields you selected. And no, you can’t wrap chevrons around your field names. You won’t see any real data until you merge the information.

Adding Other Merge Codes

While adding address information is useful, you probably want some custom fields. These fields should have been defined on the Excel spreadsheet you linked. As example, you might tell people of an upcoming event. The process of inserting more fields is similar to the address block.

  1. From the Mail Merge task pane go to Step 4 of 6.
  2. Add your letter text up to the point where you want to insert the field.
  3. Click More Items… from the task pane. The Insert Merge Field dialog appears.
  4. Insert Merge Fields dialog

  5. Highlight your field.
  6. Click Insert.

Your merge field should show next to your cursor. As before, the chevron’s are wrapped on either end.

Pulling it All Together

Once you’ve created your letter and added your merge codes, you’re ready to pull real data. If you glance at your Mail Merge task pane, you should be on Step 4 of 6. To see your letters with merged data, click Next: Preview your letters. Each merge code should show data from your Excel data source file. You should not see any codes.

While you may be tempted to print your letters, I would check several examples. In particular, look for addresses that need an extra line such as suites or apartments. This is also a good time to look for seeded records to make sure the correct fields imported. This can be done using the Find a recipient link on the panel. You can move through your letters by using the directional controls at the top of the task pane. If you don’t like a record, you can exclude it from the set.

If you’re using a window envelope or letterhead, you should print a sample letter to make sure the alignment. If it isn’t, click the Previous link to go back to Step 4 of 6 and make adjustments.

Printing and Saving the Files

The final part of the wizard lets you print your letters. This is done by clicking Next: Complete the merge, which advances you to Step 6.

Completing the mail merge
You’ll note in the screen snap you can print your letters or edit individual letters. Even if my previews looked fine, I still like to click Edit Individual letters. One benefit is that it creates a separate file called letters1.doc. This file is useful if you need to keep a copy of what was sent. You can rename the file to something descriptive.

If the document you created is one that you plan to reuse, you should save it. When you save this document, you’ll see one letter with prefilled values. You won’t see the chevrons and field names. What’s nice about saving this letter is it maintains the relationship with your data file and merge codes. The next time you open the file, it will read your data file and populate the merge codes. If you want to send to a different set of people, you edit your Excel file.

Creating a mail merge letter with Word and Excel may not win you an election, but it can set you apart. I think people prefer correspondence that is more personalized than a “Dear friends” letter.