Word Mail Merge From Excel

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

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 makes it easy to create personalized letters.

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. You might think of this as your letter template.
  2. A data source file with a row of information for each recipient. I’m using Excel.
  3. Your final Word letter file with the personalized letters

The mail merge process uses your letter template + data source file = individual Word Letters.

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 or .XLSX data extract about voters from their election office. This file usually contains fields for:

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

You may add other fields 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 creating your letter, you should check your data source 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 data. Some common problems I look for are:

  • Capitalization errors such as all CAPS
  • Missing field data
  • Invalid email addresses
  • Initials as a first name
  • Missing apostrophes in last names (Oneill)
  • Misspellings

The top item 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.

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.

Allowable Data Source File Types

In this tutorial, I’ve used Microsoft Excel as my data source. Typically, if people have Microsoft Word, they also have Excel. However, you don’t have to use an Excel file as the mail merge wizard accepts different file types:

  • Office Database Connections (*.odc)
  • Access Database (*.mdb, *mde. *accdb, *accde)
  • Microsoft Office Address List (*.mdb)
  • Microsoft Office List shortcuts (*.ols)
  • Microsoft Data links (*.udl)
  • ODBC File DSNs (*.dsn)
  • Excel files (*.xlsx, *xlsm, *.xlsb, *.xls)
  • Web pages (*.htm, *.html, *asp, *.mht, *.mhtml)
  • Rich Text Format (*.rtf)
  • Word Documents (*.docs, *.doc, *.docm)
  • Text Files (*.txt, *prn, *.csv, *.tab, *.asc)
  • Database Queries (*.dqy, *.rqy)
  • Open Document Text Files (*.odt)

The advantage of using Microsoft Excel is that it’s easy for Word to parse the data, especially if you include column headers. When you use some of these other formats, you may have to define encodings, data fields, or field delimiters. In some cases, Word has no problems, such as with this CSV file.

With other formats, you may encounter difficulty. For example, if I try to use a Word document as my data source file, the parsing is more complex.

Linking the Excel 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.
Word Mail Merge Wizard pane.
  1. Click the Next: Starting document link at the bottom of this panel. The Wizard advances you to Step 2.
  2. Keep the default option for Use the current document.
  3. Click the Next: Select recipients link at the bottom.
  4. Click the Browse … link in the middle of the panel. This opens the Select Data Source dialog.
Seleting Excel data source file.
  1. Navigate to your Excel file and click Open. This opens the Select Table dialog. If you named your Excel worksheet, the name would show. Otherwise, it will appear as Sheet1$.
Select Table dialog with added table.
  1. Check the box at the bottom for the First row of data has column headers.
  2. Click OK. Below the Browse… link you should see an active link for Edit recipient list.. .on the Mail Merge task pane.
Task pane will link to edit recipients.
  1. A dialog will appear on your document with your recipients. You can uncheck items you don’t wish to import from the 2nd column. It’s the one which is not labeled. You can also refine the list order and remove duplicate records.
Mail Merge Recipients dialog with info from Excel.
  1. Click OK again.
  2. The Mail Merge Recipients dialog disappears. You will still see Step 3 of 6 in the Mail Merge Wizard.

Adding 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 Next : Write your letter. This will advance you to Step 4. The merge wizard will show links to various letter components.
  1. You should now write your letter.
  2. Click Address block… to add the recipient’s address.
Insert Address Block with default settings.
  1. Check the options that best suit your addresses.
  2. Use the controls under the Preview section to spot check some records.
  3. Fix any address fields that you’ll use which show as (not matched) by selecting the proper field using the arrow control.
  4. 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. For example, you might tell people about 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.
  1. Highlight your field such as “event.”
  2. Click Insert.

Your merge field should show next to your cursor. As before, the chevrons 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 chevron merge codes.

Previewing the mail merge letter and fields.

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 are 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 ensure 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 File

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. At this stage, you can either print your letters or edit individual ones.

Editing individual letters

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 it to a different set of people, you can edit your Excel file or link to a new one.

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 friend” letter.

Word 2019 For Dummies
Gookin, Dan (Author); English (Publication Language); 400 Pages - 10/23/2018 (Publication Date) - For Dummies (Publisher)

Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-03-07