Microsoft Office Word – Mail Merge, Merge Fields, Rules and Conditions

Mailmerge using Microsoft Office Word (WinWord) is an easy task, as long as you go the mainstream way .. But sometimes you need to format the text based on conditions. That’s where the IF clause comes into action.. logo_Microsoft_Office_Word_2010

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.

Microsoft_Office_Word_mailmerge1

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.

Microsoft_Office_Word_mailmerge2


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.

Microsoft_Office_Word_conditional


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.” }

44 thoughts on “Microsoft Office Word – Mail Merge, Merge Fields, Rules and Conditions

  1. Thanks, Christiano! I sorted through all of the complicated half-explanations, and I’m really glad I came across your nice, straightforward explanation. You saved me lots of grief! I was ready to make 100 copies and change the fields manually!

  2. Hi there. I clearly would like to place a nice brief note and also inform you understand that I’ve been following your particular blog for quite some time. Keep up the great work and I’m going to be returning back again much more soon.

  3. How can I write ELSEIF or multiple IF? Something like
    IF group = 1 “Job title one” ELSEIF group = 2 “Job title two” ELSEIF group = 3 “‘Job title three” and so on….This is very important because I have only one level but multiple checks. I am using this to determine which job title to print. If I use single if condition for each group then it is not printing the Job Title on correct position so it’s necessary to achieve in single IF statement. Any help would be appreciated.

  4. use a select statement (vb) or switch in (c#)
    it’s like:

    dim strJobTitle as string

    select case group
    case 1
    strJobTitle="Job Title One"
    case 2
    strJobTitle="Job Title two"
    case else
    strJobTitle="job title else"
    end case

    hope that helps!

    cheer, chris

  5. Any idea how to change the default location for mail merge when I select “use existing list?”

  6. I have an excel sheet that I want to merger with a word document list. I want the merge to continue untill all lines of the spreadsheet have been used then stop. How can I accomplish this?

  7. Thanks :) Nice and complete. Wondering how I can view/edit existing rules in a template ?

  8. How do I perform a conditional merge using different documents/letters? We have different letters for different gift categories so I’m not changing text, I want it to base my condition on the letter code. Unfortunately Blackbaud’s Raiser’s Edge has issues when trying to use the Wizard so I was trying to bypass it by just exporting the data and then merging the acknowledgment letters. Needless to say, I’m very fustrated. Thanks for any help you can provide.

  9. Hello,

    Thank you for a very informative post. I was wondering if you could help me solve a problem I’m having? I’m trying to have the mail merge rules read a field that either has a name or two names connected by and (ex: George; or George and Martha). If there is an “and” in the field, I want the program to include the text, “Please call us…”; if there is no “and”, insert the text, “Please call me…”. Any help you can provide would be most appreciated! Thank you in advance.
    Adam

  10. My new job uses Word 2010, I’m used to an older version. I’m trying to do a mail merge on an existing document with a name field and employee ID field. I get through the merge okay and everything looks fine. Then I try to print it. As soon as I click on print all, a fill in box pops up. I have to click on that box once for each page I’m printing before anything will print, which means over 100 times. I’ve tried everything to get rid of this box, and our IT guy has never seen it before. Any clues as to what it may be. I can find no problem like this on the internet.

  11. Help! I need to write a statement with 3 possible outcomes like your example above, however I need to compare 2 merge fields (both numerical) to get those outcomes.
    What I want to say is ‘If mergefield1 is null, or mergefield2 is null, then display sentence1, if mergefield1 is greater than 4.5 or mergefield2 is greater than 4.5 then display sentence2, else display sentence3.

    I have tried this using this statement:-
    {IF { = OR ({ COMPARE {MERGEFIELD1} = “”} , {COMPARE {MERGEFIELD2} = “”})} = 1 “sentence1″ “{ IF { = OR ( { COMPARE {MERGEFIELD1} > 4.5 } , {COMPARE {MERGEFIELD2} > 4.5 })} = 1 “sentence2″ “sentence3″ }”}

    This syntax when applied ignores the nested if statement and only provides either sentence1 or sentence3 – I can’t see what is wrong with it!! Desperate!! Thank you

  12. Christiano,

    How can I set up to do a Word letter merge only if the Email field of a dataset (In Excel) is blank. I don’t want to mail letters to people I have email addresses.

    Thanks,

    Mike

  13. Hi,
    I’m trying to make a mail merge document but would like to be able to position the data fields easily, so they don’t necessarily tie into the Word text. I’m trying to recreate a form in Word to do the mail merge. But I find that if I have to make a change, all lines below my change get affected.
    Is it possible to have fields that can be moved around? I realize that you can’t put a data field in a text box, but that’s essentially what I’d like to do.

    Thanks for any thoughts/suggestions.
    Regards,
    Larry Pensack

  14. When creating a Directory using Mail Merge, I’ve constructed the layout so that each record appears in a block of 10 lines per record. This is helpful because one record is not easy to read if squeezed onto one line. The problem is that some records are split between the bottom of one page and the top of the next. How can I prevent this split from happening?

  15. I am trying to create a document with a data field that could be a paragraph long, or completely empty, without changing the formatting of the pages that follow it. When the paragraph is typed in, the pages behind it get thrown off and print up 10 line lower than it would without the paragraph. I have pletny of room on that page for the paragraph. Suggestions?

  16. I have sucessfully used the if….then clause, and typed in the appropriate “Insert This Text” and “Otherwise Insert This Text”. For “Insert This Text” I typed “Pass!”. And for “Otherwise Insert This Text” I typed “Fail.” My questions is, CAN I format the “Pass” to be back-filled green or typed in Green. And the “Fail” to be back-filled Red or typed in Red. Therefore as each result is inserted it is also appropriately auto formatted.

  17. I figured it out. After you construct the If then statement, from the document, go to the result text you typed in the “Insert This Text” and “Otherwise Insert This Text” and Rt click. Choose “toggle field codes” and you will see the Macro constructed. Format the text between the ” ” on the document you typed and apply any formatting you select.

  18. It’s actually a great and helpful bit of info. I’m happy that you just shared this helpful info around. Please stay us informed similar to this. Many thanks for sharing.

  19. We absolutely love your blog and find most of your post’s to be just what I’m looking for.

    Does one offer guest writers to write content available for you?
    I wouldn’t mind creating a post or elaborating on a few of the subjects you write related to here. Again, awesome web log!

  20. Do you mind if I quote a few of your posts as long as I provide credit and sources back
    to your blog? My blog is in the exact same area of interest as
    yours and my users would really benefit from some of the information you provide here.
    Please let me know if this ok with you. Cheers!

  21. Fantastic site. Plenty of useful information here. I am sending it to a few friends ans also sharing in
    delicious. And certainly, thanks on your sweat!

  22. Its excellent as your other posts : D, thanks for putting up. “A single day is enough to make us a little larger.” by Paul Klee.

  23. hello – i’m in the middle of a fairly extensive form i’m ‘automating’. is there a way to trigger a word 2010 checkbox content control to check and uncheck depending on the value in the excel spreadsheet? thank you in advance for any information you can provide. -kk

  24. Can I write a command that says If “field name” is “not blank” then write “this” but if it is blank do not do anything NOT even leave a blank line just skip to the next field! I have my document set up just how I want it but if my field is blank it leaves a line. I want to get rid of all these spare lines but cannot work out how. I am writing an invoice for a preschool and children do different sessions to others. I want their sessions to show up on invoice but I do not want there to be spaces where there are days that they do not attend! Hope that this makes sense!

  25. Sweet blog! I found it while browsing on Yahoo News.
    Do you have any suggestions on how to get listed in Yahoo News?
    I’ve been trying for a while but I never seem to get there! Cheers

  26. I’m trying to automate a document using word 2010 mail merge. Do you know how to insert a merge field within a merge field? The text to be merged is coming from an excel spreadsheet. Getting data from excel is not a problem. Here is an example of what I’d like to do
    Contents of excel cells are:
    A1: The maximum size of the class is X, and the appropriate number of booklets will be provided.
    B1: 40

    If A1 is not blank, I want to replace the “X” in A1 with the contents of B1, so the final result would be:
    The maximum size of the class is 40, and the appropriate number of booklets will be provided.

    I have been able to do it using switches \f and \b, but was hoping to be able to insert the B1 merge field inside the A1 merge field so when I make changes/updates to the text I can do it all in excel.
    Any help would be greatly appreciated!

  27. I’m not having fun with mail merge. the sentence reads:
    You have «balance» 2013 unused vacation hours remaining as of 12/31/13. The balance is coming over correctly. However, I’m trying to add a ‘skip record if service is <=4' so the merge only picks up the balances that are 5 or greater. I've tried everything I can think of but it still comes up with everyone on the list. We have 200+ so to uncheck them in the recipients list just isn't feasible. Please help! Thanks very much!

  28. Hello,

    I have following problem with this second usage of a if statement

    IF the value from the database in field TEST_PDT and in NAT = YES then in my word document the word “internal” should appear. If PDT or NAT is NO .. then nothing.

    Here my rule:

    IF { MERGEFIELD TEST_PDT } = “YES” “{ IF {MERGEFIELD NAT } = “YES” “internal” }” “”

    Why this is not working (Word 2010) – I would appreciate help !!!

    Cheers
    Chris

  29. Thank you

    Your instructions were extremely helpful and finally allowed me to do what Word should but wouldn’t let me do!

  30. I was wondering how you view/edit current IF statements/Rules that you are using for a document.

  31. How to insert in one letter a ist of rows with the same recipient adress?
    I’m trying to use “NEXT IF …” rule, but the statement don’t compare 2 merge-fields.
    Some help?
    Thank you

  32. Hi,

    I am trying to use mail merge feature in MS 2010. I’m linking my excel to the word file. It asks for which worksheet to be used. I did select that but still it ask for workbook selection and there is no option for the same. Irony is in the same word file im able to attach another excel sheet for mail merge.

    Please help
    Thanks
    Monika

  33. Hi!! I am matching fields correctly, and still phone number (under optional fields for block address) is not showing on my label… can you please help?

    Thanks much!

  34. Hi,
    Can you let me know how to show all the labels on the page at one time in preview mode. I can only see 4 labels at any given time and the rest of the page appears blank. Thanks

  35. Please help? I have a mail merge that I must have the greeting line stay in the same line. The address above could be 3 OR 4 lines and it keeps messing up the spacing. How to I leave the space blank in word 2007? Does that make sense? I have to merge and print to a pre printed paper so the line spacing needs to fit between the letter head and body text.

  36. I was using mail merge to a great effect. But, all of a sudden the word merged document is picking up merge field from the next column specified. EX: If I have mentioned merge field in column D, contents from column E are picked up.
    Can anyone help me please?

  37. I am using mail merge on my mac. Contacts – > MS Word. Not all the fields in Contacts is being displayed as an option in the mail merge fields? Any fixes for this?

  38. I want to do a mail merge for labels from Excel to Word Mailings. The Excel file has columns with color, however when I merge to Word Mailings labels, the color changes to black. How can I keep the colors intact from Excel to Word Mailings?

  39. I understand how to toggle field codes, but instead of field values, I’m getting field titles. For example, “<>” is displayed instead of a name.

    Does anyone know how to toggle this?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>