Mail merge

How to Use Conditional Fields in a Word Mail Merge

When a simple mail merge isn’t enough, consider adding conditional fields to clarify data and even make decisions.

Image: Szepy, Getty Images/iStockphoto

Even the simplest mail merge task can benefit from conditional statements. You can use them to clean up an address by removing unwanted spaces or adding commas in the correct place. Or, you may store state abbreviations and want to use the full state name in the address. Maybe you want to use gender-specific pronouns like he/she, his/her, etc. All of the above and more can be solved by combining Word fields with a mail merge.

In this article, we’ll quickly do a simple mail merge and then add an IF field to manage membership types (family, friends, and individual) in the body of the letter. Direct mail setup instructions are minimal, as that is not the focus of this article. If you need basic information about mail merge, read How to Use Word Mail Merge. We will use the same demo files (with minor updates).

I use Office 365 Excel and Word (desktop), but you can work with older versions. You can also work with your own data or download the demo files. Mail merge is not supported by the online version of Word.

SEE: System Update Policy Template Download (Tech Pro Research)

The pieces

Any merge requires a Word document and information. Our information is in an Excel workbook. Figure A shows the two pieces. We will merge the new membership details from the Excel sheet into the letter (a Word document). The Membership Type field contains conditional data. Specifically, we will convert F, Fr, and I to family, friends, and individual, respectively.

wordconditionalmma.jpg

Figure A: Our mail merge depends on information in an Excel workbook.

The set up

the [ ] characters in the Word document (Figure A) indicate where the mail merge will insert values ​​from the Excel workbook. The first step is to identify the type of merge: click the Mailings tab, in the Start Mailings group, click Start Mailings and choose Letters. Next, identify the recipients: click Select recipients, choose Use an existing list, identify the data source (the Excel workbook file), click Open, identify the appropriate sheet, and click OK.

Now that Word knows what type of merge you’re performing and where the details are coming from, it’s time to map Word placeholders to Excel fields. To do this, click Match Fields in Write Groups and Insert Field. Word will match a few of the items for you: City, State, and ZIP Code. Continue to match details as follows:

  • First name: 1st name
  • Family name: 1st family name
  • Address 1: Civic address

Use Figure B as a guideline for carrying out the mapping process.

wordconditionalmmb.jpg

Figure B: Map Word placeholders to Excel fields.

You are now ready to insert the mapped fields into the actual document. I’ll walk you through the first one:

  1. To select [first name] in the first line of the address element. Include the [ ] characters in the selection.
  2. Click Insert Merge Field in the Write & Insert Fields group and choose M_1st_First_Name from the resulting list.
  3. Keep replacing the text placeholders with the appropriate merge fields until you’re done (Figure C). You can add spaces and commas as you would with normal text.

wordconditionalmmc.jpg

Figure C: Replace placeholders with merge fields.

There are no predefined fields for membership number and membership type, but don’t worry about that. The predefined map is a shortcut you’ll want to take advantage of when possible, but it won’t always have all the fields you need. You can work around the lack of a predefined mapping field by inserting an unmapped merge field. Don’t forget to insert these two fields before continuing.

At this point, all merge fields are in place and you can run the merge. However, the membership details in the Excel workbook – F, Fr and I – may mean nothing to new members. So, let’s replace that merge field with an IF field that can turn that meaningless data into something members understand.

The IF field

If you run the merge as is, the membership type merge field will return F, Fr, and I, which you probably want to avoid. This next bit might sound like uncharted waters if you’re unfamiliar with Word fields. In a nutshell, these are pre-programmed codes similar to the merge fields you inserted earlier. In this case, we use the following nested IF fields:

{ IF {MERGEFIELD Membership_Type } = “F” “Family” { IF {MERGEFIELD Membership_Type } = “Fr” “Friends” { IF {MERGEFIELD Membership_Type } = “I” “Individual” } }}

Inserting these fields is a bit tricky – at first. You can use the Insert tab interface: In the Text group, choose Field from the Quick Parts drop-down list. Or, you can press Ctrl+F9 to insert the {} characters and type the rest yourself. Either way, replace the merge field> by the IF fields above, as shown in Figure D. Then run the merge as you normally would by clicking Finish and Merge in the Finish group. If you prefer, click Preview Results in the Preview Results group to review the merge before committing it to the finished documents.

wordconditionalmmd.jpg

Figure D: Replace Membership type insert field with IF fields.

Figure E displays one of four new member letters, ready to be saved and/or printed. Don’t forget: you can not type the characters {} yourself.

wordconditionalmme.jpg

Figure E: The merge generates four letters, ready to save or print.

The IF field looks complex, but its purpose is simple:

  • If the membership type value of the current record is F, print Family.
  • If the membership type value of the current record is Fr, print Family.
  • If the membership type value of the current record is I, print Individual.

In this case, there should never be an empty membership type value, but if it does, you’ll want to account for it by adding an extra IF. Note that the three IF fields are nested, all in all – this syntax is important.

There are other ways to modify this letter using conditional fields instead of merge fields. For example, if the membership type value is Fr, you can add an additional sentence reminding the new member that the Friends membership includes two additional guests on each visit.

Word fields are a powerful feature. If you don’t know them, consider reading 10 Things You Need to Know About Using Word Fields.

Send me your question about Office

I answer readers’ questions when I can, but there are no guarantees. Do not send files unless requested; initial help requests that arrive with attached files will be deleted without being read. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” could be able. Please mention the application and version you are using. I am not reimbursed by TechRepublic for my time or expertise when I help readers, nor do I charge a fee for readers I help. You can contact me at [email protected]

Also see