Year end receipts

Emily Leary 3

How are you sending cumulative receipts for households at the end of the year? Is there a way to export all gifts within a calendar year by household? Looking to export and merge into something that we can mail, not a canned report.

Austen Brown

Hi @Emily Leary - You will likely need to run an export and then manually combine household giving data to then merge. You may be able to speed up the process through using various excel formulas but I don't know of any way to make the process easier.

Larry Wheeler

We run Financial Report>Annual Statement report for all gifts Last Calendar Year, in PDF format. The checkbox for Create a Gift Query is checked and the static output query is then used to pull needed contact information on the constituents in CSV format.

The CSV is converted to an Excel worksheet and used in a word mail merge to create the letters. The letters and statements are sent to printing services to combine with a couple of paragraphs of words. The envelopes are stuffed and sent.

Our system may be a little over kill, but the Administration wants very formal and nice looking tax statements.

Good luck @Emily Leary

Dana Burton

This is a ‘how to’ I wrote up for future years (we do have an entire year to forget!) and others to do their job. I've tweaked it for reading. This was written and queries built for someone with limited knowledge of how to do this process/not computer savvy.

In Raiser’s Edge, navigate to Query, the Tax Letters folder, then choose the pre-built query for the needed letters. (Below are the queries I built specifically for tax letters for our organization.)

• ~EMAIL Tax Receipt - Rcvd TY Letters~ This is for constituents who have received receipts throughout the year and request their year-end tax letter to be emailed.
• ~EMAIL Yearly Tax Receipt - NO Letter~ This is for constituents who have not received receipts throughout the year and request their year-end tax letter to be emailed.
• ~MAIL Yearly Tax Receipt - Rcvd TY Letter~ This is for constituents who have received receipts throughout the year and request their year-end tax letter to be mailed.
• ~MAIL Yearly Tax Receipt - NO Letter~ This is for constituents who have not received receipts throughout the year and request their year-end tax letter to be mailed.
• Individual Tax Letter This is for constituents who request a tax letter after the above groups have been processed. For this query, simply change the existing donor ID to the constituent’s ID(s) requesting a statement. As many new ID’s as necessary may be added.

For constituents who receive a cover letter due to not receiving receipts, a query has been built for all that are mailed and emailed: 2021 All Tax Letters-NOT Receipted

To export the necessary data, choose the query (each of the first four will need to be run) then click on the results tab. After generating the constituent, their addresses and giving history for the previous year, click the export icon.

A window will pop up allowing you to choose the file location and name (save as csv.) Click on the 3 dots to choose location and always save to the U drive, Blackbaud, Receipts, Tax Letters, then the appropriate year tax letters file. (A new file will need to be created each year in the Tax Letters folder by right clicking anywhere within the Receipts folder, choosing new in the pop up window, and clicking on folder or by clicking new folder in the export window. Name the consecutive folder the same with the year of the tax letters. Ex: 2022 Tax Letters) Name the file according to the query-mail/email, receipted/no receipt. (Our tax files are saved on a shared drive that finance department has access to.)

Next, open File Explorer and navigate to the U drive, Blackbaud, Receipts, Tax Letters and double click the tax folder you are working in. Choose the file you just saved and open it by double clicking.

By clicking and holding down the mouse button on the number line 3, drag the mouse to highlight all but 1 of each name so only one is not highlighted. Right click in the highlighted field and click on delete. Do this for every name listed so each name is only listed once. This must be saved as a new file name in the same location. In the upper left corner, click file, then save as. Choose the same file path to the current tax folder adding 1 line to the file name. This will distinguish this as the beginning file for Microsoft Word. When you are finished editing and saving this file, close it out.

Ex: 21 Email Tax Letters Receipted-1Line

In the Tax Letters folder, double click on the Master Tax Letter-Rcptd.docx file to open the master letter all constituents will receive. Do not save over this file. Any changes to this file will be saved as a new file, within the year’s tax folder. Click on Mailings and then Start Mail Merge, choosing Step-by-Step Mail Merge Wizard.

This will open a walk through, giving instructions for the merge, allowing editing of individual records at the end. (This is when the giving information will be added.) Edit the date in the letter for the day the statements intend to be signed by the treasurer and mailed, also the date in “summary of your giving for 2021” in the body of the letter. In the bottom right corner of the mail merge walk through window, click next until step 3. Here you will Select Recipients (beside Start Mail Merge button) and choose use existing list.

Navigate to the tax folder you are working in for the year and choose the csv file you created with 1Line added to the name. This will not work unless there is only one name per line in the excel file. (You will have one page for each line-thus the necessity for one name per line.)

Click next in the mail merge walk through window, including Next: Complete the merge, then click the link Edit Individual Letters at the right. A window pops up asking if you want to merge all documents, always choose All.

This will open a new document window with each constituent’s name, address, and primary salutation on each page. The top of the document should be 1 ½” from the top (consecutive pages for gifts that don’t fit on one page should be 1” from the top as it is printed on WMP letterhead also.)

To add each constituent’s giving history, go to the current year’s tax folder and double click on the original file you exported. (The file you edited to one name per line.) Be sure to change the file type you are searching for from All excel Files to All Files.

Click on the Gift Date letter at the top, right click in the field and choose to Format Cells. Click on Date and choose the date which will give mm/dd/yy then click ok.

Along the field names, click and drag to expand the Gift Date and Gift Amount fields, with the gift amount being much longer. Click and drag to highligh all gift dates and gift amounts for a constituent then copy (Ctrl + C), go to Word to paste (Ctrl + V) the giving history into the appropriate donor’s letter.

If the dates or amount do not align neatly under the designation, undo the paste (Ctrl + Z) and change the width of the fields in the excel file. Then copy and paste again until the width is perfect. (It will be correct for the remainder of the letters.)

Next, copy one of the Total Amount of Gifts from the field and paste it below the line in the Word document, aligning it under the gift amounts.

Continue doing this for each constituent (and each of the four queries that will need to be completed.) When all letters are filled in and have been double checked for errors and spacing, save the file again (not the master file), then print the letters on letterhead to be signed.

Celebrate the end of another tax season! You made it!

Shelley Bell

@Larry Wheeler
Hi Larry - Since we lost our ability to integrate with Crystal Reports, I am looking for another way to run tax statements this year. I am curious about your procedure mentioned in the above post. Would you mind sharing your procedure instructions? Many thanks!

Ido Millet 2

Here is a 7-minute video demo of how you can automate this using donations data you export to Excel. Disclosure: I am the developer of Visual CUT: