Mail merge labels in OpenOffice

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.

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

4. Press the add button:

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

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

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.

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.

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:

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:

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.

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.