Word 2007 - Mail Merges, Merge Fields and Rules

Skip to end of metadata
Go to start of metadata

The General Process

Setting Up the Excel Source

The first step is to set up the Excel file with information for people receiving the mailings.  This may be provided for you already.  If not, you will want to make sure that you put headings on all the columns.  For example, on top of the column with first names, you will want a heading like FirstName.  It is best not to use spaces or punctuation in this heading.

Setting Up the Letter

Next, open Word and write the letter, omitting any information that will be filled in from, or based on, the Excel file.

Starting the Mail Merge

Now, click on the Mailings tab.  Click the Start Mail Merge button, and from the dropdown that appears, choose Letters. Nothing happens - don't worry.


 

Connecting to the Excel File

To connect your document to the Excel file, click the Select Recipients button and choose Use Existing List...  Find the Excel file on your computer.   When you are presented with a list of all the spreadsheets in your Excel file, choose the one with the data you would like to use for the merge.   There is a "My Data Has Headers" checkbox which is checked by default - if you set up your Excel file with headers as recommended above, you should leave it checked.

Inserting a Merge Field

Now that your Word document has been associated with your Excel spreadsheet, you'll be able to insert data from any of your Excel columns.  To do this:

  1. First put your cursor at a location in which you'd like to enter information from the spreadsheet.  For example, you might put your cursor after the greeting where the first name will be displayed.
  2. Now, click Insert Merge Field.  You will see a list of all Merge Fields available to you - in other words, headings from your Excel spreadsheet.  Choose the one which holds the information you'd like to reference.
    Repeat this process until you have added all of the fields you need.

Previewing the Results

To see what your letters will look like with the Excel data, click Preview Results and use the arrows to page through each individually generated letter.

Finishing Up

If the results look good, click the Finish and Merge button.  Choose Edit Individual Documents.  This will allow you to put all the letters into one long Word document, each starting a new page.  You can make any necessary edits before printing.

More Advanced Tricks

Using Preformatted Blocks

Word has two preformatted items for letters - an Address Block and a Greeting Line.  To add these, click the Address Block or Greeting Line button.  You then have to make sure that Word knows which fields in your Excel sheet correspond to common fields such as first and last name.  To do this, click the Match Fields button in the pop-up and choose the fields in your Excel that match the fields in Word.

Conditional Text (Rules)

Sometimes you don't want to display a field directly from the spreadsheet, but use the field's content to make a decision about what will appear in the letter.  For example, if someone has donated more than a certain amount, you may want to use more effusive language than usual.   This can be accomplished by adding an If...Then...Else rule.

To add a rule to your document:

  1. Click the "Rules" button and choose If...Then...Else
  2. Choose the field name you would like to examine
  3. Choose what kind of comparison you are making. You have a choice of: equal to, not equal to, less than, greater than, less than or equal to, greater than or equal to, is blank, is not blank.
  4. Type in what you would like to compare the field to.  If you chose "is blank" or "is not blank", don't enter anything here.
  5. In the "Insert This Text" box, enter the text you'd like to appear if your comparison is true.
  6. In the "Otherwise Insert This Text" box, enter the text you'd like to appear if your comparison is NOT true.
    If you'd like, you can leave either the "Insert This Text" or "Otherwise Insert This Text" boxes blank.

Viewing Merge Fields

To toggle between previewing your letters and viewing the code for your Merge Fields and Rules, use Alt-F9.
Your merge fields will look like

{ MERGEFIELD nameOfField }

Where nameOfField is replaced with the name of your field.

Inserting Merge Fields in Rules / Manually Editing Merge Code

Let's say you want to use an If rule (a.k.a. conditional), but you also want to display the content of a merge field in the result. You might have noticed that Word doesn't let you insert a merge field in the "Insert this text" or "Otherwise this text" areas of the pop-up when writing the rule.  You can insert a merge field, but you will have to do it manually.

Here's an example of when you might want to use this.  If someone has just made a commitment, but not donated, you might want to thank them for their commitment. But if they have donated, you might want to include the amount of their donation in the letter.
To do this, insert a rule and modify the result manually to include a merge field:

  1. Follow the steps to add a rule to your document and make it as complete as possible (you just won't be able to add the merge field).  
  2. Then, click Alt-F9 to see the actual code for your If...Then...Else on the page.
  3. The last two sets of quotes are "Insert This Text" and "Otherwise Insert This Text".  For example:
    { IF { MERGEFIELD zPledgeDue } = "y" "commitment" "gift" }
    Place your cursor inside the quotes of the one to which you'd like to add the merge field.  Then, click the Insert Merge Field button at the top and choose the merge field you'd like to add.

If you'd like, you could also write all of the code from scratch.  There isn't really a reason to do this unless you are excited about the prospect, but if you'd like, you can insert a new piece of code into your document by clicking Control-F9.

Nested conditionals are also possible.  In a nested conditional, you are doing another comparison inside the Then or Else section of a conditional.  You can insert another Rule just as you insert a merge field into a conditional.

Example: Planning a Nested Conditional

You might be planning a more complex merge where you will have to nest conditionals. How will you know? If you describe your situation and find you have an 'if' followed by another question/choice, then you have a nested conditional.

For example, let's say you're writing a letter to everyone in your contact list. You want to personalize the letter based on how recently you have spent time with the person. If you haven't met them, you'll tell them you're looking forward to it. If you have met them, have you met them in the last year? If not, let them know that it's been a while. If so, tell them how happy you are about it.  Sometimes, it helps to make a chart.

Now, let's write the code. Let's pretend we have two merge fields, one called Met (Y/N) and one called LastSeen (# of months ago you last saw them).

First Conditional - Have you met them?
First, insert an If...Then...Else rule to determine whether you have met them. We will use the dropdowns to select Met Equal to Y. To make it easier to find your place later, type notes to yourself in the Insert this Text - like: Check when you last met them. We are not doing a second check if we have not met them, so we can simply type a message in the Otherwise Insert This Text (I am looking forward to meeting you).

After this step, your code will look like:
{ IF { MERGEFIELD Met } = "Y" "Check when you last met them" "I am looking forward to meeting you." }

Second Conditional - If you met them, when did you last see them?
Now, as per in the instructions in the Inserting Merge Fields in Rules section above, we are going to use Alt-F9 to view our Field Codes. Locate where you made your note to "Check when you last met them". Select this text - leaving the quotes - and delete it. Now we will add that second conditional here to check when we last met them.

Insert an If...Then...Else rule, this time checking when we've last seen them. The dropdowns would look something like LastSeen Less Than 12 (since 12 months are in a year, and we're checking if we met them this year. From here on it's easy - we just write our happy message in Insert This Text (I'm so happy we've gotten to spend time together recently) and, in Otherwise This Text, It's been a long time since we've seen each other.

Now, your code will look like:
{ IF { MERGEFIELD Met } = "Y" "{ IF { MERGEFIELD LastSeen } < 12 "I'm so happy we've gotten to spend time together recently." "It's been a long time since we've seen each other."}" "I am looking forward to meeting you." }


Labels

kb kb Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.