The Raiser's Edge Data Cleanup Series: Advanced Mail Merges

Hi everyone! Today I'm here to introduce one of my favorite topics for The Raiser's Edge. And just like my post on Almighty Vlookup, sometimes the most useful features available in The Raiser's Edge come from software integrated with the program.

If you've ever performed a mail merge in The Raiser's Edge, you're likely familiar with the difference between simple and conditional. But for those new to the party, a quick refresher: a simple mail merge uses the same template to merge each recipient’s unique information. For example, using the same letter text is useful for general mailings, such as an update letter or simple solicitation.

A conditional mail merge uses multiple templates to create letters for a single mailing. For example, you want to announce the opening of your new library; however, those living in your state will be asked to visit the library, while out-of-state constituents will receive a link to the library's website for a virtual tour. In a conditional mail merge, the parameter file selects a single field (in this example, State) for Microsoft Word to determine the correct letter template.

With me so far? Now let’s explore a more advanced feature using conditional text inside a single template rather than separate templates. Perhaps you're sending a letter updating your constituents on the progress of your Capital Campaign. Those with the Board Member constituent code will see a customized paragraph about the next board meeting. Those on a committee (stored as an attribute) will see instead a paragraph about your progress scheduling upcoming committee meetings. Finally, constituents who have given over $10,000 to the Capital Campaign will see a special thank you without any mention of the board or committees.

Customizing letter text uses an option called If-Then-Else in Microsoft Word, useful whenever you want to vary paragraph text in a simple mail merge. Let's get started with a new mail merge. Since this feature is located inside Microsoft Word itself, I can use it either from the Mail module, or from Export mail merges. For this example, we'll construct the Capital Campaign example using an Export, as this allows full flexibility to use any field available on a Constituent record.

Creating the Export

I created a new Constituent Export with the format Blackbaud Simple Word Merge DOCX (used in Office 2007 or later). Every constituent will receive the same basic letter, but we'll use If-Then-Else for customization. I included an Ask query that, on merging my letters, will group together donors to a campaign and gift date range of my choosing. I've also chosen to exclude inactive, deceased, and constituents with no valid address:



On the Output tab, I include both fields I will need for the letter itself (such as Addressee, Salutation, and an address block) but also the fields required for the If-Then-Else process:



We will select all addresses using the Address Processing group, which will factor in seasonal addresses, along with a phone number and email address. In this example, we are using the primary constituent code from Constituent Information, as we'll only key off of their most important relationship with our organization. For the purposes of this example, constituents can only be in one committee. Finally for the Summary, I included all gift dates, for the Capital Campaign only, for all realized gift types (meaning I removed Pledge, MG Pledge, and Recurring Gift, all of which are the promise of future donations).

Now, let’s create the letter template. After adding the main part of the letter (that everyone will receive), let's add our first If-Then-Else conditional text. Access this feature by clicking the Add-Ins ribbon toolbar, then Insert Word Field, and select If-Then-Else.

If you're familiar with the operation of a Raiser's Edge query, this should be old hat. Let's investigate.

A: Select the field which determines if the conditional text will be used. In this case, we're looking to include a special message for our board members, identified using the constituent code.

B: Select the operator to compare the Field from the record to the Value that should result in using the conditional text, aka step A to step C. Not visible in the (B) Comparison screenshot are Blank and Not Blank, exactly like query.

C: Add the appropriate value from the Field name (A). You will need to type the value exactly as it appears in The Raiser’s Edge.

D: Add the text used if someone has the appropriate field value, in this case the constituent code equal to Board Member.

E: You can include an alternate text for everyone else or leave blank, as in our present example.

Click the OK button for the grand unveil, and I see..... nothing in the letter! Nothing at all. Here's why: Microsoft Word normally hides conditional information. In order to view the If-Then-Else statement, you must use the super-easy-to-remember key combination: Alt+F9.

You should now see:



You can use Alt + F9 again to toggle back to hiding the merge code. To complete our letter, we need to create the remaining If-Then-Else statements for our two other groups of recipients: committee members and major donors.

For committee members, I want to include text specifically for committee members but an invitation to join a committee to everyone else. We’ll use the attribute description Not Blank for the former and the alternate text for the latter:



And just for our Capital Campaign major donors:



Now, let’s try an even more advanced use of this conditional text that requires editing the merge code. Suppose we want to include our constituent's phone number or email address, or if those are blank, give them the option to write in their missing information on a blank line.

This advanced procedure can be tricky, and thus merits discussion. Logically, we want to use a blank line if the phone or email is blank or include the value if not. We'll begin like we have before:



However, this dialogue box does not allow the "Otherwise insert this text" input box to include a merge field. What we need to do is partially complete the process here and finish the rest by editing the merge code back in our letter:



The MERGEFIELD syntax uses two values after the equals (=) sign, the first for the “Insert this text” and the second for the “Otherwise..” Each value is enclosed in quotation marks. So, what we need to do is look for the two consecutive quotation marks at the end of the line, which represents no “Otherise…” text is used. Between these quotations we can manually insert our Raiser's Edge field! So click in between the quotation marks, and from the Add Ins ribbon toolbar click Raiser's Edge Field and select the email address and phone number respectively. The end result will be:



All that's left is the merging.

I didn't call this Advanced Mail Merges for nothing, as this is certainly something that will take some practice. But by following these steps, you can customize your letters to a tremendous extent, resulting in more professional output that's also much more efficient to produce.

Looking for a tune-up on some of the core concepts discussed in this class? Take a look at Export in The Raiser's Edge for an introduction into the Export module. For information on Mail Merges with the Mail module, check out Mail Essentials in The Raiser's Edge. And finally, one of the most useful areas for Advanced Mail Merges are Generating Donor Acknowledgement Letters and Receipts.

Use this feature before? Got suggestions for other The Raiser's Edge users? Anything else on your mind? Let me know below in the comments, and I’ll see you in class.