Mail merge labels in OpenOffice

Are you letting your customers know what work you do best and how you can make their business more successful? Effective marketing establishes and promotes your brand. Your web site and marketing materials should, powerfully, demonstrate why they should do business with you.

Getting rid of pesky blank lines in OpenOffice mail merge

I mentioned OpenOffice in a previous article, especially how it is really good and a free alternative to Microsoft Office.

I recently needed to create my Christmas card labels. This is a task that I do not like. I keep my name and address data in a spreadsheet that I update throughout the year as I get new information about friends and family. At least, that is the theory. In practice I forget and many of my labels are inaccurate. But, that is not really what I dislike. The biggest pain is the practical aspect of label production, have I got any labels left, are my labels correctly aligned in the printer, do the label pages load properly in the printer and printer jams.

And the biggest practical pain, heretofore, has been using OpenOffice to create the labels, that is until now….

I had two main problem areas with OpenOffice:

  1. A lot of my friends and family live in the USA, but many live in the UK and elsewhere. UK addresses are not as formalized as US ones and can be any number of lines, for example:
    1. Line 1: Name of house
    2. Line 2: Street address
    3. Line 3: Name of village
    4. Line 4: Name of town
    5. Line 5: Postal code
    6. Line 6: Name of county
    7. Line 7: Country

    Since I store both the US zip code and the UK postal code in the same field, I end up with several blank lines on my US address labels.

  2. I store country in the ‘country’ field, BUT for USA labels it looks silly to include the country when they are being posted from within the USA.

Getting rid of blank lines on address labels in OpenOffice

Unlike
MS Office, OpenOffice does not get rid of the blank lines by default. There is simple way of doing it that is described here here.

I created my labels in a slightly different way (described below) but I used the method of suppressing blank lines described in the tutorial mentioned above.

Creating labels in OpenOffice

1. Create a data source in a spreadsheet. I use the following format:

Name Address1 Address2 Address3 Address4 Address5 Address6 Country

Save the spreadsheet.

One pitfall I found was that using a label such as ‘Address 2’ with a space between the ‘Address’ and the ‘2’ meant that the method described below, did not work.

2. Open OpenOffice.org Writer. Press Tool – Mail Merge Wizard.

Tell OpenOffice about the data source

3. Click on 3. Insert address book and then click the Select Different Address List button on the right.

Define data source

4. Press the add button:

Define data source

5. And add the data source (i.e. the location of your spreadsheet)

6. In OpenOffice.org Writer. Press File – New – Labels.

Open new label file

7. Find the database that was set up in 4 and 5. Click on the database field and copy the items to the label in the order that you want them by pressing the left pointing arrow. Also select the correct label type, then press new document.

Create label fields

8. In the document you will need to change the type face so the lines will fit on the label. You only need to do this on the first label and then press the ”Synchronize Labels’ button to copy the effect to all the other labels.

Modify first label and then press synchronize now

9. If you are not concerned about the blank lines you could go ahead and create you labels now. Simply press File – Print and you will see the following dialogue:

Mail merge labels

Press yes.

10. If you ARE concerned about the blank lines, there are a few more steps before merging and printing the labels. Firstly make sure that the lines on the label are separated by pressing return, rather than line breaks. You can make sure by moving the cursor to the end of a line, pressing delete and then return.

Move the cursor to the beginning of a line that may contain a blank field for some records in the database. Press Insert – Fields – Other. You should see the following:

Enter the hiddden paragraph condition

Make sure that hidden paragraph is selected and in the condition field, enter NOT(field_name), where field_name is the name of the relevant field (e.g. Address3). Press Insert. Repeat for each line that may contain empty fields in the database. Make sure that you press the Synchronize Labels button to copy the change to all the other labels.

Remove a specific country from addresses

11. If you have a situation like I describe earlier, where, for example you do NOT want the country to appear if it is USA, then follow this procedure. Insert the cursor at the beginning of the row that contains the Country field. Press Insert – Fields – Other (like we did above), select the hidden paragraph item and in the condition box enter: Country EQ “USA” Press the Insert button followed by the Synchronize Labels button.

12. You are now ready to print the labels as described in 9 above.

 Use OpenOffice.org

Microsoft, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

All other trademarks are the property of their respective owners.