Nearly a third of all giving happens in December alone and 53.8% of non-profits start their year-end appeal in October—the end of the year is crunch time for nonprofits and generates most of the year’s total donations. Unfortunately, the end of year giving rush sometimes makes other important tasks fall through, like end-of-year tax letters. Donors rely on these letters in preparation for tax season, and failing to provide professional, detailed correspondence in a timely manner may have a dramatic impact on the future donor base loyalty.

Tax letters require “nested” mail merge. One letter is produced for each donor, and this letter contains a formatted listing of each donation with a total. Our recommended solution is Conga Composer. Not only does it handle this elegantly, but it also provides non-profits a robust solution for email, Excel and PowerPoint output.

Unfortunately, many small non-profits cannot afford a package such as Conga even with discounts. Recently we encountered this with a non-profit. Finding a no-cost, relatively simple solution proved more challenging than expected. One post with threads spanning several years underscores the complexity of this task. But the Simplus team was able to prepare a simple solution to this common problem:

 

Prerequisites

If you are not familiar with Declarative Lookup Rollup Summary, this is your chance to up your game. Once installed, and assuming you have access to Microsoft Word and Excel, you are ready to proceed.

Most non-profits take advantage of the Salesforce Nonprofit Success Pack. Opportunities are used to log pledges and posted donations. We will be using this object.

 

The Goal

Here is a snippet of a letter we need to produce:

The body of the letter is pulled from the account (household) object. The three-column table points to data in the opportunity object, a related list off the account. This is where things get difficult using the Word/Excel approach. The key to this solution is that everything you see here is related to data on the account object, not on the opportunity object!

 

Step 1: Create Formula Fields on the Opportunity

The first three steps are the key to this solution. We need to concatenate the three columns into a single field. We labeled it “Tax Letter Line”.

The currency (Amount) is formatted as text. You can modify this as required. The <TAB> and <BR> pieces are necessary when the final formatting is done in Word.

This next field is also a formula field and is labeled “Include in Tax Letter”:

We will use this in the rollup logic. Only certain categories of donations are tax-deductible. This checkbox formula field is only checked when the category is tax-deductible. Another key element is the stage, and finally, we filter it by the close date being in the previous year. This assumes that tax letters will be printed in January for the previous year. You can add custom logic such as the first line of this formula.

 

Step 2: Create the Receiving Fields on the Account

Create a Long Text Area field on Account called “Tax Letter Lines” and a currency field called “Tax Letter Total”.

 

Step 3: Create the Rollup Logic

This assumes knowledge of DLRS. The link provided above provides documentation for the installation and use of DLRS.

 

Rollup 1 – Populate Tax Letter Total

This is a simple “sum” of all the opportunity amounts that are included in the previous year’s total.

 

Rollup 2 – Populate Tax Letter Lines

This is where DLRS is indispensable. The Aggregate Operator is “Concatenate”. This will lump all our tax letter lines from the opportunity into a single long text field on the account using the Concatenate Delimiter which is empty in this case.

 

Here is the result:

It looks ugly right now. What it becomes in Word is amazing!

 

Step 4: Create the Report

This is a very simple step. Create a report at the account level. Include all fields you need on the tax letter including the two new account fields. Apply filters as required.

 

Step 5: Create the Word Template

This is not a tutorial on Word. You need to know how to create a Mail Merge template like this:

Note the “table” section. This is not a table at all. All three lines (Date, Tax_Letter_Lines, Total for 2019) are indented with tab stops.

 

Step 6: Perform the Merge

Ready for the good news? Everything so far is a one-time setup. You will never need to repeat it again. The rest of the steps are performed once a year and literally take 30 minutes or less.

 

1-Export

Run the report and export details only. It will be in the older Excel format which Word does not appreciate. Open it in Excel and save it as an Excel Workbook.

2-Merge

Run the Mail Merge Wizard in Microsoft Word from your tax letter template.

Choose Step-By-Step Wizard from the drop-down menu. A panel will appear on the right side of the template. Follow the steps, navigating to your Excel workbook when prompted to select from an existing list. Preview the letter. It should look like this:

Complete the merge and choose to “Edit individual letters”. You are almost done!

 

Format

Your first letter may look something like this:

Replace the <TAB> marker. Open the find/replace dialog and enter the following:

Note that a special character is used for the Tab. This is the result:

Replace the <BR> marker. Open the find/replace dialog and enter the following:

Note that a special character is used for the Paragraph Mark. This is the result:

If your tab stops are correct, you are done!

 

Note that we did not cover the total on the next line. That is a simple currency merge field that is connected to the “tax letter total” field in the report. Consult Microsoft Word documentation to format the currency appropriately.

 

Summary

Salesforce is a powerful tool, and there are many add-ons such as Conga Composer that allow non-profits to effectively communicate with their donors. Smaller non-profits cannot afford the minimum license costs of these add-ons.

An alternative is to use what is normally available; namely Microsoft Word and Excel, along with the free DLRS package. The initial setup may take a few hours. Once configured, yearly tax letters can be generated in a matter of minutes. This solution is more efficient than any alternative we have seen