ExtremeCPA - Technology for Business

Thursday, September 22, 2005

Word & Excel - Creating Standard Letters with Mail Merge

Well, I began writing the second piece to my last article about scripting, but I soon realized that I'm going to have to make it a three-part series to do it justice, and so I haven't had the time to finish it up. But I thought I'd write another post in the meantime. Have you ever had to write a "standard" letter (such as a confirmation letter or a bill to clients who are overdue)? Typically, it involves figuring out who to send the information to and then writing up all those letters. A simple technique you can use instead involves the use of Microsoft Word's "Mail Merge" feature. I'll walk you through a simple example.

To begin, you might want to start with a blank document. Once opened, go to "Tools | Letters and Mailings | Mail Merge", and choose "Letters" to continue on. The next step you could use templates or from an existing document (if you've already started to write the letter), but in this case we simply want to start with the "current document". In the next step, you are asked to select the recipients of the letter. If you feel comfortable selecting queries or tables from an ODBC connection, this is the most direct route. Otherwise, if you have a dB that allows you to download your information into Excel (or the information is already there), you can just select the Excel file ... or if you're writing to a set of clients about a new project, maybe you want to use "Outlook contacts" if you use Microsoft Outlook. We'll choose "Use an existing list" and then click the "Browse" link to find an Excel file. You should already have a list started*, and when you browse to that file you should select the sheet or named range with the contact information (and any other relevant information you want to include) from the "Tables" option that pops up. Typically, Word is pretty good at figuring out what information should be included.

The next step is to click the "Next" link at the bottom, and begin to write your letter. You'll write your letter as you normally would, but instead of writing any particular piece of information that is only associated with a particular person or company (like their account balance), you can choose to insert fields (either "standard" items, or you can be more flexible and click the "More items..." link) and precisely define what fields should be inserted where. Once you have completed the letter, you are then able to preview an example letter in the next step. If you need to make corrections, simply go back with the "Previous" link at the bottom.

The final step is to create the new document. If you want the option to edit particular letters for some reason, you can choose "Edit individual letters..." and it will create a new document with all the letters filled in. Alternatively, you can simply choose to "Print" the document and all will be printed out with the appropriate information filled in.

I hope this provided you with insights on how to use this feature. Good luck!

*NOTE: You should have a list in Excel with no extra columns and no blank rows. You should also include a "header" column that describes each column in the list, so that you can easily select which fields to insert when writing the letter. Finally, if you name the entire list (select the entire list in Excel and hit Ctrl-F3, then name it something like "Contact_Info"; see here for more information about naming ranges and cells), you can EASILY select it when Word prompts you which table to select.


Post a Comment

<< Home