- This Week's Free Computer Tip -
122 - Creating Form Letters from Excel Data (using Mail Merge)
What is "mail merge" . . .
. . . and how it is used?
This is a case where an example provides the best answer.
Let's say you decide to start a new home-based business and you want to send a monthly newsletter to your customers. Not only that, you want each letter to be addressed "Dear Sally", instead of the generic, "Dear Customer". How can you do that and not have to type 50 individual letters, month after month? The answer is, you build a database of your customer names and addresses, write one newsletter and use the "mail merge" feature of Word to generate 50 copies of your newsletter with each customized to an individual. Once it's set up just right, it's a piece of cake to repeat the process each month.
Getting it set up initially can be tricky, but here's a step-by-step to help you get it done.
For this example, I assume you know how to set up a very simple Excel spreadsheet for your "database".
There are columns for name, street address, city, state, zip, and first name.
If you get a little fancier, you might want a column for the proper salutation (Mr or Ms).
You get the idea, and don't worry, you can always go back and add a column when you discover it's needed.
Here are the instructions -
- Build or select the Excel spreadsheet which contains your mail merge data.
If it does not already have a “named range” defining the area containing the data, you can create one now.
I suggest naming that range “data area”.
(If you don’t know how to do this, simply make a note of the cell references, the upper left most cell and the lower right most cell in which your data exists.)
This range should NOT include column titles.
Only the actual data should be included in the named range or cell locations.
- Save and Close your Excel file.
- Open a new Microsoft Word document (or a letter you previously typed and now want to use as a form letter).
Wherever you plan to have the mail merge data appear, type in a “placeholder”, such as the letters “xxx” for example, to identify that location.
It does not have to be unique.
You can use the “xxx” to mark every place where mail merge data will be used.
Typically, that would include the name, address, city/state/zip, and the opening salutation lines.
In step 11, we will be replacing these with mail merge fields.
- On the Tools menu in Word, click Mail Merge.
- Click Create, and select Form Letters.
(If another pop-up window appears, click Active Window, which will create the letter on the Word document that you currently have open.)
- Next, click Get Data, and then choose Open Data Source.
- In the Open Data Source dialog box, open the folder that contains you Microsoft Excel workbook.
- Look for the “Files of type” box and click MS Excel worksheets (*.xls).
- Double-click the workbook that contains your data.
(You may be asked to Confirm Data Source, click OK.)
- In the Microsoft Excel dialog box, select the named range mentioned is step 1 above, or type the cell references that identify the data you want to use, and then click OK.
- When Word displays a message, click Edit Main Document or Set Up Main Document.
- A mail merge toolbar will appear, along with the form letter page.
Here is where you insert your merge fields into the letter.
When your letter is done, click on the Merge button on the toolbar.
- Double click each of your placeholders, one at a time, and click the toolbar button Insert Merge Field.
- A drop-down box will appear, showing a list of the actual fields available to be inserted.
Pick the appropriate field and click on it.
The merge code now replaces your placeholder
- Some print lines will require more than one merge field, and these should be added as well.
For example, Mr. Jones might be two separate merge fields stored as “salutation” and “last name” in the Excel database.
- Spaces, punctuation, and/or actual words may be needed along with the merge fields.
They should be typed in as you do this.
For example, the “Mr. Jones” in the above example needs a period after “Mr”.
If that is not stored in the database, type it in as you build this print line.
Always review the resulting documents to see to see that you have though of every possible data combination which may cause improper letters.
If there are errors found, close the resulting document, without saving it, and repeat steps 12 & 13 until the document merges correctly.
Now you can print your letters, but don’t forget to save the final version of the merge document for later use.
That's it. Hope you find it useful.
About our company |
Why call us? |
What we do |
Why teach on YOUR computer? |
Pricing & Payment |
How will I benefit?
This Week's Free Computer Tip
Computer Tips Revisited