Making Mailing Labels and Form Letters From Lists

If you’ve ever been stuck doing the unrewarding job of a listings editor (or assisting an event planner, for that matter), you’ve probably been stuck with large document/data files at one point at another, from which you’re supposed to generate a massive mailing. Well, if you’ve kept your information nicely organized in tables, this is easy enough. But what if your boss or predecessor has trapped you with a standard list that contains hundreds of entries that look something like this:


Name of company
Name of person
Mailing address
Website address
A few sentences about whatever.
Additional notes
Phone numbers


Well, a standard mailing-label mail-merge may not be so easy with your starting info stored in list-form like this -- in fact, it may be impossible to extract only the data you want, unless you manually change everything line-by-line. But I’ve got a simple solution. It might look complex at first, but once you get the hang of it you’ll be able to generate mailing labels and form letters in minutes from the most cumbersome files.

The following is intended for converting a list into mailing labels or form letters. It assumes that the list was put together in the standard format of having lines of information (i.e., fields) separated by paragraph marks, with a double paragraph mark dividing each listing entry (i.e., record).

(Because you’re forcing your document to do something it wasn’t exactly meant to do, you might get some bothersome little error messages now and again (“not enough fields” or “not enough data” or “too many data fields,” etc.). They’re not harmful at all – just hit “enter” repeatedly until you move on to the next step. Trust me.)

Note: This should work on multiple versions of Microsoft Word on any version of Windows and possibly in other programs and operating systems as well. The only big problem I’ve seen is when the document contains some strange characters or hidden data that can confuse the merge macro. So if the steps below don’t work, look out for that kind of thing – it’s easy enough to fix (saving as a plain TXT file and then opening and closing the TXT document will get rid of hidden data)


Part I:
Making Mailing Labels From a List

1) Erase extra data at the beginning and end of the document (e.g., any introductory paragraphs or titles or closing footnotes bookending the entire document).

2) At the beginning of the document, create a new listing entry that has the fields you want to extract from your list. Typically, the lines could simply be labeled like this:

Address 1
Address 2
Address 3
Address 4

3) Use find and replace (click ctrl-F) to set up the document for the merge by:

a) Hit ctrl-F to open the “Find” window, choose the “Replace” tab

b) Replacing all double-paragraph marks with page breaks (replace “^p^p” with “^m”)

c) Replacing all single-paragraph marks with a tab character (replace “^p” with “^t”)

d) Replacing all page breaks with a single paragraph mark (replace “^m” with “^p”).

4) Save the above as your scrap file (this is your merge data source)

5) Choose “Tools” – “Mail Merge”

6) Create “Mailing Labels” in a “New Document”. (Choose your mailing label type when prompted).

7) Choose to “Open” your data source. (Choose to create you labels in a new document.)

8) When setting up your mailing label, choose “Tab” for your field and “Enter” for your record.

9) Using the drop-down menu provided, set the mailing label to include the data you want to extract in the order and location you want them extracted (typically this will be the first 4, 5, or 6 lines of each record, e.g., this is should be the mailing address info that you want, as seen in step 2 above)

a) You can use boldface or italics or add extra data at this time – the resulting mailing labels will include the data/formatting choices that you make here. (The trick is to use short cuts to make all formatting changes, e.g., use “ctrl-B” to make a line bold, or “ctrl-I” to make it italics…. Because otherwise most formatting options will not be offered.)

10) Hit OK and then choose “Merge.” A pop-up Merge window will appear. If prompted, choose to merge into a new document. Also, click on the button to the right that says “Check Errors.” You can then choose “Complete the merge without pausing.”

11) The click “Okay” and then “Merge” (this time inside the merge screen, where you should be right now, if you haven’t screwed up). If you didn’t choose “complete merge without pausing,” you may get hundreds of errors messages around this time, but that’s OK. Just hold down on your “Enter” key until you’re past these problems (the errors are only warning you that, essentially that the list contains fields that you’re choosing not to use).

12) A new document will appear that has a template of how your data is laid out. It will obviously not have your mailing labels. You can close this document and do not have to save it. However, behind this document should be another document with your mailing labels.

13) Save the mailing-label document.

14) Finally, clear out phone numbers and other extra data entries and common formatting problems by using a series of universal find-and-replace searches:

a) To remove all standard phone numbers, for instance, search for “(^#^#^#) ^#^#^#-^#^#^#^#

b) And replace with an empty field. To remove double paragraph spaces, search for “^p^p” and replace with “^p”.

c) Change the font to a smaller size if everything doesn’t fit, or change the font type if desired.

d) Universally replace common words like “Department” with abbreviations like “Dept.” to save space, if needed. (And “University” might become “Univ.,” etc.)

e) Etc.

15) Save the document. Print labels when needed. (Always print a test page first, because every printer will print margins slightly differently, meaning you’ll probably need to make slight “Page Layout” changes to your document to make sure that all data fits correctly inside of the hard-copy labels).


Part II:

Creating Form-Letter Mail-Merge Documents From a List

First, create the Form Letter that you will use to merge all your other data into:

1) Open up a previous mailing Letter (where the letterhead and various graphics are already in place) or create a new letter. Save this document with a new name (as appropriate).

2) Make sure the letter is located in three parts on each page: The Header, which has the letter head and introduction paragraph; the Main Text, which consists of the list entries in the center of each page; and the Footer, which consists of the blank entry form and the mailing address. (If your document does not have a header and footer section, you can create them by simply choosing Insert -> Page Numbers. Then Choose to insert a page number in the header or footer, and then erase the number manually -- you'll your header and footer will now be editable).

3) Double Click on the main text area of the document (the center of the document, where the current listings are located). Hit “ctrl-A” to select the entire list. Then hit “Delete.” The document will now only be one page, with the letterhead, letter, and graphics still untouched (because they are located in the protected Header and Footer areas).

4) Double Click on the Header area of the page. You will now be able to edit the Header and Footer (while the Main Text becomes the protected area). Either re-write the header and footer area or paste it in (if you’ve created the Header and Footer text in a separate letter document).

5) Make sure that there is PLENTY of space left in the center of the document. Shorten the Header and Footer text as much as possible, since the shorter you leave these areas, the more space you’ll have for the list on each page, which will mean that cleaning up the document later will be much easier.

6) Adjust the boxes and graphics in the Header and Footer so that they fit around the new text nicely. Adjust spacing as needed so everything fits (Menu: “Format” to “Paragraph” to “Space Before and After.”)

7) Leave an extra paragraph space after the last sentence of the Header, to keep the listing from bumping up into the Header text.

8) Double Click in the center of the document again, so that you can now edit the Main Text (leaving the Header and Footer protected again)

Now, format the List document:

9) Open up the list that you need and save it as a “scrap” file to insure that you do not accidentally destroy the main list document.

10) Erase extra data at the beginning and end of the document.

11) Use find and replace (click ctrl-F) to set up the document for the merge by replacing all double-paragraph marks with page breaks (Hit ctrl-F to open the “Find” window, choose the “Replace” tab, and then replace “^p^p” with “^m”)

12) Now each listing should be located on its own page. Hit “ctrl-A” to select everything in the document. Hit “ctrl-C” to copy all data.

13) Finally, go back to you main Letter document that you created. Click inside of the Main Text area. Hit “ctrl-V” to paste your list into the letter document.

14) The Letter document should now expand to the appropriate number of pages, with a different listing on each page, and the same Header and Footer beginning and ending each page.

15) Check to make sure that all of the listings fit. If a lot of listings do not fit, adjust the Header and Footer text as needed. If only a couple of listings do not fit, adjust these listings so that they fit.

16) Print.


Note: if the mailing Letter is created from scratch instead of using a past Letter as a template, then you’ll need to create all graphics and letterhead from scratch or bring in that material from a different letter. To create an editable Header and Footer in a new document, simply choose Menu: “Insert” and then “Page Numbers” and insert a page number both in the Header and then in the Footer of the document. Once you’ve added new text to the header and footer, you can erase the page numbers and the Header and Footer will not go away.

You will also need to choose Menu: “File” – “Page Setup” to adjust the page margins and gutters and Header and Footer Margins so that the maximum amount of space can be used on each page, thus insuring that every form letter will fit on exactly one page each. Print out a test page to make sure that you have not gone outside of the printable area. By using a past document as a template, you will not have to worry about any of this as much, since most of these adjustments will have been already made.

ALSO: There are fancier ways to set up mail-merge documents, but in order to fully use the advanced mail-merge capabilities of MS Word, it’s usually best to keep your data in a table or database instead of in a list -- unless you're prepared to make sure that every single entry in the list contains EXACTLY the same number of fields and all info is uniformly organized.

However, the instructions above are, in reality, a workaround system that I invented to keep from having to change every list document into a proper table or database prior to merging, even when varying numbers of fields are provided in each record. There might be better systems available, but I couldn't find anything like this when I found myself merging dozens of documents into mailing lists and letters. So I hope you find this article useful.

1 comment:

ThePurpleSeal said...

Hi there,

I just thought it may be of some interest to you to know, a while back i came across a british labels company who sold me a batch of plain labels at a really low price. If you are at all interested then it may be worth visiting their website so see if you could save some money on your labels.