LetterWriter (Auction Tracker)
LetterWriter can give you a spreadsheet of information for any group of people that supports your organization, such as guests, item donors, financial sponsors, bidders, or volunteers.
You can use LetterWriter to get a spreadsheet of their contact information from Auction Tracker. You can also then use that spreadsheet as a source for email marketing software, or for an email merge or a mail merge for printed letters in Microsoft Word.
1. Export the data from Auction Tracker, using LetterWriter
Open Auction Tracker
Go to Reporting, then LetterWriter and choose Microsoft Word (Delimited Ascii)
"Produce Letters for"
- Select either, All Constituents, or any other category needed (Donors, Sponsors, Guests, etc)
"In The Format"
- Select Names As Entered, if you wish to list spouses with each other on the same row of the spreadsheet
- Select Names Treated Individually, if you wish to list them on separate rows of the spreadsheet
"Based On"
- Choose Individuals, Businesses, or both, as needed
"Include Constituents"
- Select constituents from a particular event, multiple events, or all events
"Sort By"
- Choose from the following: Constituents Name, City, State, Zip/Postal Code
- There may be other choices offered, depending on who was selected in the first step. The following options are presented for Guests
Press "Continue"
Choose "Select Fields"
- Put a checkbox next to each field of information needed.
- Make selections in the order desired.
***The order of selections will determine the order of spreadsheet columns.
Example:
Each of these fields you select here will become a column of information in the output spreadsheet.
- Press Continue
- Under the "Save As" dialog box that appears, be mindful of the folder to which the file is being saved.
- The default is C:\Tracker\Auct-Tr\User Documents
If you wish to open the output up as a spreadsheet, save it as a .TXT file. If desired, you can then later use it for a mail merge or anything else.
If you wish to immediately do a mail merge in Microsoft Word, without first opening it as a spreadsheet, save it as a .DOC file.
Before saving, use the "Save as type" drop-down at the bottom to select "All Files"
In the File Name field enter the name of the file, with the extension .TXT, to be easily able to open it as a spreadsheet. If you do not first want to see it as a spreadsheet, and want to immediately do a Microsoft Word mail merge, save it as a .DOC file.
To have those particular selections available in the future, enter a Template Name and a Template Description then select Continue
The information has now been exported from Auction Tracker into a ^ (carat) delimited text file (essentially a spreadsheet).
2. To open this file in Excel (for Google Sheets instructions, see below)
By default, the file from Auction Tracker will save to \Tracker\Auct-Tr\User Documents
If you wish to easily find the file, save it in Desktop or another familiar folder.
A text import wizard will open. Make the following selections.
The file sent from Auction Tracker is a spreadsheet with the ^ character between each column. You need to tell Excel that the columns are separated ("delimited") by the carat ^ character and that the columns have headers (e.g. "DONOR ID"), so that it will import the file correctly.
Select Delimited
Select My Data Has Headers
Select Other and enter the ^ character.
The data towards the bottom of the text import wizard box should now appear in columns.
Select Finish
An alternative to the Text Import Wizard, is opening the file and then using Data > Text To Columns with the carat ^ character (press SHIFT-6) as the delimiter.
3. To open the output file in Google Sheets:
Make sure file has been named .txt
Open a blank document in Google Sheets
File > Import > Upload
In the Import File dialog box, make the following choices:
- "Import Location" -- Replace Spreadsheet if you are in a blank spreadsheet; Create New Spreadsheet if you are in an existing spreadsheet
- "Separator type" -- select Custom and enter the carat ^ character into the blank field to the right of "Custom"
Select Import Data
It will import the file and display “File Imported successfully” at the top of the dialog box
Select Open Now
4. Creating a Mail Merge in Microsoft Word
Here is a link from Microsoft word on how to create mail merges. https://support.microsoft.com/en-us/office/use-mail-merge-for-bulk-email-letters-labels-and-envelopes-f488ed5b-b849-4c11-9cff-932c49474705
-
** Auction-Tracker LetterWriter Macro List
For reference, the following chart comprises a complete list of merge macros available for LetterWriter. Macros available for merge depends on criteria selection made before macro selection.
Macro/Field Definition Contact Job Title Job position held by constituent Personal Email Address The primary email address listed for the individual Alternate Email Address Alternative email listed for a business or individual Individual First Name Constituent's First Name Individual Last Name Constituent's Last Name Individual Suffix Suffix of the individual constituent (Sr., Jr., etc.) Individual Title Title of the individual constituent (Mr., Mrs., etc.) Spouse First Name Constituent's Spouse First Name Spouse Last Name Constituent's Spouse Last Name Spouse Suffix Constituent's Spouse Name Suffix (Sr., Jr., etc.) Spouse Title Constituent's Spouse Name Title (Mr., Mrs., etc.) State/Province Two letter state or province abbreviation Website URL Web address listing for individual or business Zip Code Zip/Postal code Common Fields Affiliation Organization or person is associated with, professionally/personally Donor ID Unique donor management application identifier Referred By Individual or business who introduced constituent to organization Donors Items Donated List of items the constituent donated in sentence form Item Catalog Number Catalog number assigned to an item offered for bid Item Procurement Number Pre-event warehousing id number assigned to an item offered for bid Item Solicitor Individual who secures goods and services for the benefit auction Donation Value Fair Market Value of item donated Sold For Final bid or sale amount of the goods and services offered for bid or sold Solicitors Items Solicited List of items the constituent solicited in sentence form Sponsorships Solicited List of sponsorships the constituent solicited in sentence form Guests Amount Spent Amount the guest spent at the auction Bid Number Bid number assigned to the guest Entree Meal selected by the patron Fund-A-Need Amount Amount the guest donated to fund-a-need RSVP Level Name of the reservation level the guest purchased RSVP Amount Amount the guest paid for their reservation level Table Name Name of table guest was seated at for the gala event Table Number Number of the table guest was seated at for the gala event Sponsors Sponsor Date Date on which sponsorship was received Sponsor Level Name of the sponsorship level the sponsor purchased Sponsor Amount Amount of funds received for event sponsorship Solicitor Name of solicitor who obtained the sponsorship Sponsor Preferred Name How a sponsor wishes to be addressed publicly Volunteers Committees Committee that the volunteer belonged to working for your auction Positions Position that the volunteer held working for your auction Resource Lists Resource Name User-defined resource designation In-Kind Donors Date of Use Date on which the donated item was used Donation Value Fair Market Value of item donated In-Kind Item Name List of items the constituent donated in sentence form Pre-Event Purchase Amount Total amount for non-catalog goods and services sold to a constituent pre-event Fund-A-Need Amount Total amount contributed to Fund-A-Need by a constituent pre-event