Do-It-Yourself Year-End Tax Letters

Create letters with giving statements using only Salesforce, Excel, and Word

I attended my first NPSP Day in Seattle this year (if you haven’t been to an NPSP Day, I highly recommend checking it out). Activities included a “Speed-Geeking” session (similar to speed-dating, but with demonstrations of cool apps, Salesforce tricks, etc.). I shared a demo of how to create year-end tax letters/statements (or any other similar document) without a third-party application.

The few minutes I had to demonstrate weren’t quite enough to go into all the details of this process, and I promised to share a tutorial online. At long last, here are the instructions so you can try it yourself!

Step 1: Create the report in Salesforce.

I recommend using the Opportunities report type (if you use Payments, then an Opportunities with Payments report type; and/or if you aren’t on the Household Account model, you may want to include Households in the report type).

• Include the following filters:
  • Closed/Won (or Paid=TRUE) records
  • Amount greater than $0
  • Select the Record Types or Types you want included in the statements on your letters
  • Close Date (or Payment Date) in the time period you prefer (e.g. last year)
  • Billing Street and Billing Zip aren’t blank (this helps filter out those without valid mailing addresses)
  • Account Record Type equals Household Account (if you want to include businesses, you don’t need this filter, and may need to adjust these instructions a bit)

• Include the following fields:
  • Formal Greeting
  • Informal Greeting
  • Opportunity Record Type and/or Opportunity Type
  • Amount
  • Auction Non-Deductible Amount (or any other field you use to track the non tax-deductible amount on ticket purchases, etc., this is a field from the Auctions app)
  • Close Date (or Payment Date if using Payments)
  • Address fields (Billing Address Line 1, Billing City, etc.)
  • If you have households/Accounts with the same Formal Greeting, include the Account ID and/or Account Name fields – so you have a field to use to identify the different households/recipients

PRO TIP: Save this report to use again in future years.
Once you’ve “dialed in” your report, export it (details only) and open it using Excel.

Step 2: Prepare the spreadsheet.

• If you’re using Classic, delete those extra rows at the bottom of the sheet (with the report name and such)
• In the Formal Greeting column, enter a zero (“0”) in the next available cell
• Sort the entire sheet by the Close/Payment Date (ascending), then sort it by Formal Greeting Column A-Z (ascending), this should put the mostly empty row with a zero in the Formal Greeting column at the top of your report
• Enter an “X” (or “ZZ” – something at the end of the alphabet) in the blank row at the end of the report in the Formal Greeting column
• If your Opportunity Record Type or Type names are a little admin-y (e.g. “GW Auction Ticket”), use the find/replace function to make them more donor-friendly (e.g. “Event Ticket”)
• Insert a column next to the Formal Greeting column (right-click the top of the column to the right and click “Insert”). Enter the following in that new column:
  • Give it a heading (I use “Count”)
  • Enter a formula in the second cell and copy it down to the last row – the one with an “X” or “ZZ” in it.
    • If Formal Greeting is column C and Count is column D, the formula in the first cell (D2 in this example) would be:

=IF(COUNTIF(C:C,C2)>1,IF(C2=C3,”many”,”last”),1)

Save your report as a “.xlsx” file.

Step 3: Prepare the letter/statement template.

• Start with a new/clear Word document (if you have a letterhead to use, go ahead and use/set that up)
• Begin the mail merge by selecting Mailings > Start Mail Merge, and select DIRECTORY (this is the most important step – if you forget it, you’ll sit there troubleshooting until the cows come home)
• Select the spreadsheet you prepared/saved as the data source
• Now things get complex, we’re going to set up the merge fields on the template. Unfortunately, you cannot copy and paste the text, you’ll have to type it in manually.
  • This letter is made up of several IF/THEN statements, which are unique in Word
  • For each set of {} brackets, you will need to insert them by pressing Ctrl + F9 (Mac users: press Command + fn + F9)
    • This video demonstrates how to type merge information/set up those brackets (it isn’t specific to these letters, but this is the video that taught me what I needed to know to do this)
  • Every part of the template is within at least one set of brackets.
    • Remember: each opening bracket “{” is typed using Ctrl+F9, then type within the brackets that are created – do not type any brackets yourself
  • If you “lose” the brackets (that is, if Word just shows the text, and you don’t see the merge information anymore), simply highlight all the text (Ctrl+A or ⌘+A), right-click, and select “Toggle Field Codes”
    • You can also do this to “hide” the brackets/merge info
  • Any set of brackets that begins with “MERGEFIELD” (e.g. “{ MERGEFIELD Informal_Greeting}”) can be inserted using the “Insert Merge Field” option in the “Mailings” tab of the Word ribbon – I recommend inserting fields this way to account for differences in field/column names

Following is how the first page of the template should read.

You will notice that the majority of the page is typed within the brackets that begin

{ IF {Place2} <> {Place1} "

The text begins after the quotation mark, and continues until after the Date/Type/Amount/etc. line. After that line, another IF statement comes into play (Ctrl + F9 – don’t forget!), and the rest of the page, along with the page break, fall after the quotation mark in that IF statement.

Watch out for the quotation marks – they’re very important.

Keep in mind you have to type the text of your letter, you can’t copy it from somewhere else. You can insert an image (the signature, for example), as long as you place the cursor where you want the image (still within a set of brackets), then insert the image.

Important: part of the template is inserting a page break after the end of page 1.

I initially tried to set up the statement portion of the letter using a table, but that did not work out well. Instead, you can specify indentation points (not sure of their official name) in the ruler. Much of the document will have the left margin all the way to the left, but the parts with the “statement” will be indented. Here’s what mine looks like, as a reference:

Page 2 continues the IF statements from page 1

Note that this page begins with a quotation mark (right after the page break from page 1). It also ends with another page break (page breaks can be added via Insert > Page Break).

This page also closes out the second IF statement from page 1 (the one you created under the Date/Type/Amount/Etc. line).

Page 3 – the final page

This page also begins with a quotation mark. It includes the brackets that close the IF statement you began on page 1. Keep in mind that bracket is there as soon as you set up the IF statement, you’re simply typing within the brackets (within a set of quotation marks within the brackets, to be specific).

Note: The final paragraph/line is important (that last ¶ on the page). It’s the one piece of writing that falls outside of the brackets.

After you select all and “Toggle Field Codes” to hide the brackets/merge information, your document/template should look similar to the following (plus an extra blank-looking page).

Step 4: Merge the letters and clean up.

Because you selected “Directory” in the mail merge process, you will not be able to use the Preview feature to preview your merge. Instead, select Mailings > Finish & Merge > Edit Individual Documents to generate a Word document with all of your letters.

Your new document will include a letter at the beginning and a letter at the end that each need to be deleted. This is why we added the “0” and “X” (or “ZZ”) items in the Formal Greeting column of the spreadsheet. Select the text for each letter, including the page break, and delete.

I recommend saving the document of merged letters (just in case anything happens), and doing a brief spot-check for any issues or errors. Donors with a high number of opportunities may spill into a second page, be sure to double-check the formatting on those. Then you’re ready to print!

Pro tip: if you need labels, you can set them up using the same spreadsheet you used for the letters:

1. In the Excel document with the spreadsheet, right-click the spreadsheet name (displayed as a tab below the sheet) and select “Move or Copy”
2. Mark the “Create a Copy” checkbox and select a spot in the same Excel workbook, or a new one – whatever you prefer
3. In the new/copied sheet, select “Remove Duplicates” in the Data tab of the ribbon
4. Mark that your list has headers, and tell it to compare based on the column with Formal Greeting (de-select the rest of the columns)
5. Save the document, then use Mail Merge to create labels, using the new sheet as the list for your labels

Congratulations, you did it! “It” meaning you stuck it out through this very long tutorial and, more importantly, created your tax letters! There are a variety of customizations you can apply to this process – addressing to businesses, emailing the statements, adding donation totals, playing with the format. As you feel comfortable, try different things and see what works best for you.

---

Let me know your thoughts and questions in the comments. If there’s enough interest, I’m thinking a video tutorial may be helpful as well. We will see!