by Scott Lowe MCSE | Apr 05, 2006 7:08:00 PM

Takeaway: For a few simple form letters, Microsoft Word's internal address book may be all you need when using the built-in mail merge features. However, the true power of Word's mail merge is only revealed after you integrate it with the more advanced database features of Microsoft Excel. In this How do I… article Scott Lowe shows you how to integrate Excel data when using Word's mail merge feature.

But, how many of you actually keep all of your contact information in Word? If you keep it in any Office program, you probably keep it in Excel, or in Access. Or, if you keep it in some other system, it's generally an easy process to get, for example, customer address information into Excel. In this article, I will show you how to make use of Excel data when using Word's mail merge feature.

I'm assuming that you know how to enter data into Excel and understand the purpose of Word's mail merge feature. If you need a refresher on either of these topics, take a look at previous articles in this series. I will be repeating some of the mail merge information from my previous article in this article in the interest of completeness. However, if you're interested in Word mail merge, you should read that article before you read this one.

Inventory sheets

For this article, rather than the typical form letter, we'll use an existing Excel used vehicle inventory to create inventory sheets that can be posted on each vehicle on the lot. Here's a snapshot of the Excel inventory table.

Figure A

We'll use this inventory table for the examples in this article.

Note the column headings in Figure A. Every column has some kind of heading. While this isn't absolutely required, I do highly recommend that you assign a column heading to every column in your spreadsheet. Word will use these column headings as field names. If you fail to provide a column heading, you'll get things like "Column A", "Column B", which can be rather confusing. "Leather seats" and "Sunroof" are much more descriptive.

The Word mail merge process

To get started, open a new document in Word and choose Tools | Letters and Mailings | Mail viagra brand cheap Merge from the menu bar. In the sidebar area, Word opens a Mail Merge helper that provides you with a wizard-like interface that walks you through the process.

Step 1: Select document type

Word's mail merge can be used to create form letters, e-mail messages, envelopes, labels, or directories. With a little imagination, these choices let you create just about any kind of document or communication you would need.

Figure B

Mail merge step 1: Determine your document type

Feel free to experiment with the different document types. For this example, we'll be creating an inventory sheet using Excel data. The best option to choose for this kind of mail merge is Letters, so I'll choose that option from the list of selections. When you've made your selection, click "Next: Starting Document".

Step 2: Select starting document

Any document you've created can be converted into a form letter. Or, if you want to start from scratch, you can use the current document (which, for me, is a blank document) and type your letter. You can also use any document template on your computer, or on Office Online, Microsoft's Web repository that contains hundreds of templates available for download.

This starting document is often referred to as the "master document" as well. The terms are interchangeable, although other Word features also use the term "master document", so be sure to keep things straight.

The starting document is the document that will ultimately contain the base form letter.

I'm going to use the blank document I have on the screen as the starting document.

Figure C

Mail merge step 2: Choose the starting document.

Once you've decided on the document that gets the honor of being the starting document, click the "Next: Select recipients" option. Notice that the wizard also allows you to go back to the previous step if you need to.

In step 4 of the wizard, you will be able to modify your letter.

Step 3: Select recipients

In the previous step, you chose what document to use as a form letter. In this step, you need to decide to which recipients you want to send the form letter. Word's mail merge feature lets you use a wide variety of data sources. In this case, we'll use an existing list. Click the Browse button to look for the Excel file you want to use for your mail merge.

Figure D

Mail merge step 3: Tell Word from where you intend to get your recipients.

The Browse button opens a window called "Select Data Source" from which you can connect Word to just about any database you like. From this window, choose your Excel inventory file and click the Open button.

Figure E

Select your Excel data file and click Open.

Most people are not familiar with "Named Ranges" in Excel (I'll cover them in a future article), so the resulting window might be a little confusing at first since it looks like gibberish. Notice that three of the four choices are the names of the individual sheets (Sheet1, Sheet2 and Sheet3). In my Excel file, the inventory information is on "Sheet1", so I will choose the corresponding option from the Select Table window. Note also the "First row of data contains column headers" option at the bottom of this window. As I mentioned before, it's really a good idea if you include headers in your spreadsheet. However, if you don't want to do so, make sure you uncheck this box. If you don't, Word will assume that the first row of your data contains headings.

Figure F

Select the sheet on which your information resides.

On the resulting window—the Mail Merge Recipients window—are a number of options that let you control what records you want to include in your merge. Note the checkbox to the left of each record. The check in the box indicates that this record will be included in your merge. If you don't want to include a particular record, click the box to remove the checkmark. You can also filter the records that you want to include in the merge by using the down arrows next to each heading. The buttons at the bottom of the window let you find specific records (Find), quickly select and deselect all of the records (Select All, Clear All), make sure that addresses are valid (Validate) and more. Click OK when you're done. Figure G below shows you the Mail Merge Recipients window while Figure H shows you your Word sidebar after you're done deciding which records to import.

Figure G

Which records do you want to include in your mail merge?

Figure H

If you want to make a change to your list, click the "Edit recipient list" option.

Step 4: Write your letter

Ok – this one isn't a letter (it's a form to slap onto the cars in our inventory), but Word doesn't know that. To Word, everything is a letter! On this step, you have the Word sidebar shown below in Figure I.

Figure I

The "More items" link is most appropriate for this example.

On this step, create your inventory sheet/flyer as your normally would. However, instead of typing in the individual inventory information, use fields instead. In this way, you're keeping your letter generic and letting Word do the work. You should also see a new toolbar added to your tool arsenal.

I'm not going to go over every option on the toolbar in this article, but will provide details on which buttons you need for most mail merge operations.

The first three buttons are pretty important. In order from left to right:

• Main document setup: This is the same as step one of the wizard in that you select the type of document you intend to create.
• Open data source: Again, this button loosely matches a step in the wizard; in this case, Step 2. When you click this button, Word opens up a dialog window asking you to choose the data file you want to use. If you want to connect to a different kind of source, such as an Access database, click the New Source button.
• Mail merge recipients: This button opens the window you saw in the previous step where you can decide which recipients should be included in the final output.

Now, we'll on to making the appropriate changes to your letter to make it generic. Notice in the sidebar, now entitled "Write your letter", there are a number of options, including "Greeting line", "Postal bar code", and "More Items…"

Figure J

The mail merge toolbar makes using mail merge a little easier.

Take particular note of the sixth button from the left. I've highlighted it for you in Figure J. Click this button to open the Insert Merge Field window. Alternatively, you can click the "More Items…" option in the sidebar. Either way you do it, Word opens up a window from which you can select fields from Excel to add to your flyer. As you're typing your flyer, use this window to add a field where you would normally put in specific information about a vehicle. For example, rather than typing in the price of a vehicle, insert the "Price" field instead. Figure K shows you what my sample flyer looks like.

Figure K

My sample letter, with merge codes.

Bear in mind that you need to put spaces, commas and tabs where they belong. In the previous article on this topic, we used address fields, but still had to manually put the comma between the city and the state fields.

Click "Next: Preview your letters" when you're done with your flyer.

Step 5: Preview your letters

With your flyer crated, it's time to see the results. From the sidebar, click the "Next: Preview your letters" option. The result should look similar to what you see below in Figure L.

Figure L

Here's a preview of the flyer.

One thing you may notice right away is the formatting of the price is wrong. There is no dollar sign or comma where it belongs. To fix this, click the "Previous: Write your letter" option in the task pane. This will take you back to the "write your letter" step of the wizard.

Figure M

Use the "Previous" option to go back to the previous step.

Now, click somewhere in the Price field and press Alt + F9 on your keyboard, or, right-click the field and choose "Toggle Field Codes". Either way, you will get the result shown below in Figure N.

Figure N

The fields look a little different here. This is what the full field code looks like behind the scenes.

Note, if you use the Alt+F9 option, you will always get the field codes for all of the fields. If you right-click a field and choose Toggle Field Codes, you will get the field codes for just that field. It doesn't hurt to show the field codes, but, as you can probably see, it can muddy up your document.

In these field codes, we want to change the format so that it comes out as currency rather than as a plain number. To do so, we need to type in a little code, such as "{ MERGEFIELD "Price" \# $#,### }" The "\# $#,###" portion of this stuff tells Word that we want to format this field as a number with a dollar sign followed by # signs indicating where numbers from the price will go. Also note the inclusion of the comma. Don't worry that there is only a single # sign between the dollar sign and the comma. Word knows enough to out multiple digits in that space for amounts larger than or equal to $10,000. Look at Figure O. When you're done, click the "Next" option to go to the "Preview Your Letters" step again.

Figure O

The field codes are used to make granular changes to the field.

Now, take a look at Figure P below. This time, the price of the car is properly formatted and will look a lot more professional. Choose the "Next: Complete the merge" option.

Figure P

The result: A properly formatted dollar amount.

Step 6: Complete the merge

This part is easy! Your sidebar again changes to match the step you're on and looks a whole lot like what you see below (Figure Q).

Figure Q

Step 6: Finish your mail merge.

At this point, Word has not officially performed the merge. That is, your letter and data file are still mostly separate. When you click one of the two options—(1) Print; or (2) Edit individual letters—Word asks you what you want to merge.

Figure R

What records do you want to have printed?

Your options here are to merge all of your records, in which case you will get one page per recipient, or choose which records you want to include. When you choose the print option, Word sends your flyer right to your printer. If you made a mistake, you have to reprint the flyers. By choosing the "Edit individual letters" option, you make Word create a huge document that includes every letter just as if you'd typed the letter manually for each recipient. I usually prefer to use the latter option so I don't have to reprint things.

Harness the power

Now, you've seen Word's mail merge in action using its own limited database and with its ability to harness Excel's power. You've also seen two examples of what mail merge can do: create simple letters, or create non-standard documents such as flyers.