Build client letters in bulk by combining a client list with a master letter in Word or Google Docs using an add-on.
Key takeaways
- Confirm the list has one row for each client and a header row with clear column names.
- Remove duplicates and mark any missing details.
- Build the letters from the approved master copy, and save it as a new version.
- Review a sample against the source data, with a second person looking at it.
- Approve the full run in writing, produce it, and record the date, document version, document count, and reviewer.
It is copying names, addresses, and amounts into each document one at a time that slows the work. Every retyped detail is another chance for an error. Bulk methods do that copying for you, so the work becomes one master letter and one list instead of many separate documents.
There are two mail merge routes: Word and Excel, and Google Docs with an add-on. For forms, Drake Accounting also lists an article about creating 1099 forms on the fly. Each needs some setup before the first batch goes out. The goal is to pick one method, test it on a few records, and then repeat it the same way every time.
Method one: Mail merge from Word and Excel
Microsoft Word and Excel can combine a client list with a letter you write once. Intuit's help article on engagement letters in Lacerte describes this capability, saying Microsoft Office "is able to perform a Mail Merge to combine an Excel document of client information such as names and addresses, with your document created in Word." That article is written in the context of Lacerte, so use it to understand what the Office feature does, and confirm the details in your own setup.
The method uses two files. The first is the letter, written once in Word with blank spots where each client's details will go. The second is the Excel list, with one row for each client and one column for each detail the letter needs. For a letter, the columns might include a salutation, first and last name, street address, city, state, and ZIP code. Name the columns in plain words, such as "First Name," and keep them in the same order from one batch to the next. Save the list under a name that shows its date and purpose, and keep the Word letter beside it with a version number in the file name.
Before you build the letter, decide what each field should contain and what the letter should say when a detail is missing. A letter that opens with "Dear ," because a salutation cell was empty is easy to avoid if you plan for blank fields in advance. A simple master letter might read:
> Dear [First Name] [Last Name], > > Thank you for working with us this year. Please review the attached information and contact our office with any questions.
Once the Word letter is connected to the Excel list, run the merge but do not print the full set yet. Generate a preview of a few letters and read them the way a client would. Choose records that test the edges of your list: a long name, a second address line, a name with an apostrophe or an accent, and a record with a missing field.
If a field looks wrong, fix the spreadsheet and run the merge again rather than editing the output by hand. Hand edits work for one or two letters, but the next batch will repeat the same mistake.
Keep the merged output apart from the master letter and the list. Save the final output in its own folder, named for the batch, so a later edit to the letter cannot change a set that has already been reviewed. If you need to resend a batch, regenerate it from the approved master letter and list rather than copying an old file.
Method two: Mail merge in Google Docs with an add-on
The same Intuit article says that "while Google Docs does not appear to natively support it, there are addons available to perform a mail merge." Consider an add-on for a mail merge in Google Docs, checking its current instructions before use. No single add-on is recommended here, so compare options using each add-on's own documentation.
Start the letter the way you would start any document. Google's help page on sending signature requests includes the step "Create a new Google Doc or open an existing one," so the letter can begin as a new document or from one your firm already uses.
Build the client list in a spreadsheet with the same layout as the Word and Excel method: one header row, one row for each client, and one column for each detail. Field formats can differ between tools, so follow the add-on's instructions for marking where each field goes. Then run the same sample review described above before you process a full batch.
A shared master letter that many people can change is easy to alter by accident. Keep a master copy that only one or two people can edit, have the rest of the team work from copies, and save a dated copy of each approved master letter so you can later see which version went out.
Test the add-on using synthetic records before you connect it to real client data. Check what permissions it requests and what it can read or change in your files. Record which add-on and which version you used, since the next person who runs the batch will need that information. Use the client data access controls guide when reviewing who can reach the files.
For forms: Check Drake Accounting's article
Drake Accounting has an article titled "Creating Forms On the Fly (Forms 1099, 1098, W-2, W-3, 94x)," which names Forms 1099 among the forms it covers. The title appears on a Drake Software knowledge base page, and that is where to find the article.
Read the article itself before you plan a batch around it. If your firm uses Drake and needs 1099 forms, check whether the feature covers the form types you need, supports batch processing, and fits the size of the batch you have in mind.
If you already keep payee information in your tax software, check whether the form-creation feature can use those records before planning to move files between programs. Before a live batch, run a small test with records you can check against your own files, and note any step that differs from your usual process. If the feature does not cover a form you need, plan a separate route for that form rather than forcing the whole batch through one tool. Whichever tool you use, keep a list of which payees received forms, so you can answer questions later without reconstructing the batch.
Compare the routes before you commit
The right method depends on what you are producing and what your firm already uses. Mail merge suits letters built from one master document, and both mail merge routes handle that job. Treat the Drake article as a separate starting point for checking form creation before planning a workflow.
| Method | Suited to | Confirm before you rely on it |
|---|---|---|
| Word and Excel mail merge | Letters built from one master document and a client list | Field names match the list headings in a test run |
| Google Docs with an add-on | Letters, if your firm works in Google Docs | The add-on's permissions and its field instructions |
| Drake Accounting article | Checking form-creation options | Supported forms, batch processing, and batch size |
Ask four questions before you choose:
- What will you produce: letters, forms, or both?
- Which programs do your staff already know well?
- Who will review the output, and can they review it in the program you choose?
- Can you keep a master copy of the letter somewhere only a few people can change?
If you produce both letters and forms, you may use more than one method. That works as long as each method has its own master document, its own list, and its own review step. Mixing a letter list and a form list in one spreadsheet makes mistakes easier to miss.
Plan the workflow for each batch
A repeatable batch moves through a few stages: prepare the data, build the documents, review a sample, approve the full run, and record what went out. Writing these stages down, even as a short checklist, helps a second person run the batch if the first person is unavailable. Use the client intake checklist to organize the handoff.
Prepare the data first. Confirm that the client list is current, remove duplicates, and flag any missing details. Then build the documents from the master version you approved last time. If the wording has changed, have someone review the new master letter before anyone merges client data into it.
Work backward from the date the letters or forms need to go out. Put the review step on the calendar as its own item, with enough time for a correction and a second look. A review squeezed into the last hour leaves no room for a fix.
Give each step a named owner so that nothing waits on an unclear handoff. The order matters because each step depends on the one before it. A sample built from an unfinished list tells you very little about the full run.
Check each batch before it goes out
Pull a few records, including the unusual ones, and compare the generated output with your source data line by line. Look for wrong names, missing lines, wrong amounts, and any field that printed as a blank or as an unfilled field code.
Then check the count. Compare the document count with the included data records, excluding the header row and records deliberately omitted from the batch, to check for skipped or doubled output. If the counts differ, stop and find out why before you continue.
Compare amounts with the current source data, not with memory or last year's figures. An amount can look right on the page while being out of date, and the layout of a form will not reveal that.
A good sample review answers three questions. Does each document read correctly from start to finish? Does each field hold the right value for that client? Does the record match the source for the unusual cases you picked? If any answer is no, fix the source data or the master document, run the sample again, and do not move on until it passes.
Illustrative example: Check the included records
An illustrative letter list has 50 synthetic client records, and 10 are deliberately omitted from the batch. If it produces 50, the omitted records were included, so correct the selection and repeat the sample review before producing the full batch.
Keep your client data clean
Avoid merged cells, notes typed into data cells, and totals placed in the middle of the list, because each of those can confuse a merge.
Duplicates are easy to miss in a long list, and a single duplicate can produce two letters or two forms for one client. Confirm addresses against your current client records, and mark any client whose details are incomplete so someone can fill the gap before the run.
Decide who owns the list. One person should be responsible for updates, and changes should go into the master list rather than into copies. Copies drift apart, and a letter built from an old copy is hard to spot until a client points it out.
Treat the list as confidential. It contains names, addresses, and financial details. Store the spreadsheet and the merged output where only the people who need them can reach them, and delete working copies once the batch is finished and recorded.
Handle the letters that need a personal touch
Not every letter belongs in a batch. Some situations call for wording that a master letter cannot cover, and forcing those letters through a merge can produce text that does not fit the client. Keep those letters out of the run and write them one at a time.
Before you decide a letter needs a personal touch, check whether the master letter can be adjusted to cover the situation. If it can, use the master letter. If it cannot, write the letter by hand and note it in the batch record so the set is complete.
Mark these clients so the run leaves them out. You can keep a separate list or add a column in the master list that the run excludes. Either way, the person running the batch should see at a glance which clients were left out and why.
If a standard letter needs one extra sentence for a group of clients, consider a second master letter for that group rather than editing each letter by hand. A second master letter is easier to review once, and it keeps the wording consistent for everyone who receives it.
Common problems and how to fix them
- Blank fields print as gaps or empty salutations. Fix the missing data in the list, and decide in advance what the master letter should say when a field is empty.
- Fields do not fill in. Compare the field names in the master letter with the column headings in the list. A small difference in spelling can keep a field from filling in.
- Names with apostrophes, accents, or hyphens look wrong. Check them in the preview, and confirm they match how they are written in your records.
- Long addresses wrap badly. Test the longest address in your list in the preview, and adjust the layout of the master letter if needed.
- Amounts are out of date. Export a fresh copy of the data before every run, and do not reuse a file from an earlier batch.
Start with one batch
Start with one small batch and one method, then expand once the review step works every time. A first run shows which columns are missing, which field names need to change, and which steps your team tends to skip. Write those lessons into the checklist before the next batch.
Keep the master letter, the list format, and the check steps the same from batch to batch. Changes to the master letter should be a deliberate step, saved as a new version and reviewed before use, not an edit made in the middle of a run.
Today, create a short list of synthetic records and use it to test one letter in the method your team already knows.
Common questions
Which method should we test first?
Test the mail merge method that uses programs your staff already know well. Choose Word and Excel or Google Docs with an add-on based on your current workflow.
What should we record after a batch?
Record the date, document version, document count, and reviewer. Keep that record with the reviewed output so later questions do not require rebuilding the batch.