How-to create mailing labels or stickers – Osoitetarrojen luominen

lähettänyt Jukka Niiranen 6.12.2012 klo 7.54 [ 7.12.2012 klo 9.05 päivitetty ]

In these days creation of mailing labels should be an easy everyday task any person could perform any day and in any place. But it's not. For some weird reason office software developers want to do this simple task in unbelievable complex way. In this article we go thru a step-by-step process how to easily and quickly create mailing labels for a SME company's billing or marketing purposes or for example for your personal Christmas card mailing.

This how-to has been created based on free and open source tools: Libre Office 3 (version: 3.5.4.2) running on Ubuntu 12.04. However things should go similarly even if you are using Open Office or running either of the two office software on other operating systems like Mac or Windows.

Suomenkielinen tiivistelmä - Finnish summary of this text

Osoitetarrojen luominen pitäisi tänä päivänä olla sellainen joka päiväinen toimenpide, minkä pitäisi onnistua keneltä tahansa ja milloin tahansa ilman mitään ponnistelua. Jostakin syystä toimisto-ohjelmistojen tekijät haluavat kuitenkin tehdä tästä yksinkertaisesta ja tarpeellisesta toimenpiteestä aivan käsittämättömän hankalaa. Lisää haastetta asiaan tekee tietysti vielä amerikkalaisten hassu epästandardi letter-arkkikoko ja parin jenkkifirman aktiivinen ote omien tarra-arkkiformaattien markkinoinnissa toimisto-ohjelmistojen tekijöille.

Tässä artikkelissa käymme askel askeleelta läpi postitustarrojen luomisen käytännössä. Näkökulmana on PK-yrityksen laskuttajan, markkinoijan tai joka kodin joulukorttien postittajan lähtökohta. Eli nopeasti ja tehokkaasti valmista käyttämällä standardia A4-paperia, käytännössä mitä tahansa tulostinta ja mitä tahansa kirjakaupasta löytyvää osoitetarra-arkkia.

Avainsanoja: postitustarrojen luominen, osoitetarrat, osoitetarrojen tekeminen, joulukorttiosoitetarra

Process overview

Unfortunately there are rather many steps in this process, but bear with me, this will work and for example software installation and creation of the sticker need only be done once.

Installation of the needed software
Creation of your custom label sheet (physical sheet of label stickers) to LibreOffice
Creation of a list of addresses to a spreadsheet (= address database)
Creation of a database connection to the spreadsheet of addresses
Creating the labels and defining their contents
Printing or saving the mail merged labels
Final manual editing of the labels before printing

1. Installation of the needed software

You need to have complete version of LibreOffice installed on your computer, including LibreOffice Base, the database application. If you are using Ubuntu, just click and open “Ubuntu Software Centre”, type in “LibreOffice” (no space in between the words!) to the search box and look for all needed LibreOffice parts from the list. To install certain part, click “Install” button. You need at least “Writer”, “Calc” and “Base”. On other operating systems follow the installation instructions available at LibreOffice's web site.

2. Creation of your custom label sheet (stickers)

Open LibreOffice Writer and from the top menu choose: File > New > Labels
Now you have to have the retail box of your label stickers at hand. First of all you should check if your stickers are already listed on the 'Brand' and 'Type' drop down boxes (in 'Labels' inter-leaf’s 'Format' section). If your stickers are not listed, which is the case practically every time at least in northern Europe, you will have to create a custom label. To do this, get a ruler and go to 'Format' inter-leaf. (see picture below)
Now you just have to measure your sticker sheet (or read the information from the box), count your stickers and put in correct values to the fields. When you are ready just click Save button and give your custom labels 'Brand' and 'Type' names, so you can easily find it later.

In the picture there are the measures I used for my “Talex 140.401” stickers. My sticker sheet has 5mm physical margins at the top and the bottom. I also put 5mm margin to the left as printers tend to leave couple of millimetres white space on both sides. (Note: there seems to be a small bug, in this version of LibreOffice. When label measures are set correctly as below in the picture, value in "Page Width" is automatically calculated to be larger than the paper sheet. But fortunately this doesn't seem to make any difference and most likely this will be fixed soon.)

3. Creation of a list of addresses to a spreadsheet
(= address database)

This phase is easy, just open normal spreadsheet (LibreOffice Calc) and type in all contacts and addresses you want to create the labels for. Use the first line as a header and give logical names to each column. For example like this:

Firstname	Surname	Spouse name	Children	Street address	Post address
Susan	Smith	John	Jean, Peter	Bondstreet 1	53900 Bigcity
Paul	Duck	Paulina	Dean, Dylan	Churchstreet 10	59100 Smallvillage

Naturally you can have whatever fields you want and as many fields as you want. After creating of the spreadsheet, just save it in a known location and close the spreadsheet. To put it in tech words, this spreadsheet file will now act as your address database.

4. Creation of a database connection to the spreadsheet of addresses

Warning: this is the most technical part of the entire process. But if you just follow these steps, things will work. Do not be alarmed by the technical terminology. All we do here is that we tell LibreOffice to use your address spreadsheet as an address source.

Open LibreOffice Base
In the first view you will see the Welcome / Select database dialogue box shown below. What you want to do is to “Connect to an existing database”. Choose Spreadsheet as an existing database and click “Next”.

New “Set up a connection to spreadsheets” dialogue opens. Click Browse and point to the spreadsheet file you created earlier. (the spreadsheet that contains your addresses) Click “Next”.

Now we have to “Decide how to proceed after saving the database”. All you have to do is to tick-in the box “Yes, register the database for me”. You can un-tick the “Open the database for editing”, if you do not want to edit your data now. Click “Finish” and you are done.

5. Creating the labels and defining their contents

Now finally we'll get to the point. First of all, open LibreOffice Writer and go to the top menu File > New > Labels. The same label dialogue opens as earlier, when we created the custom label stickers.

This time we do not have to touch the “Format” inter-leaf at all, but please make sure that there is a tick in the box “Synchronize contents” on the “Options” inter-leaf. See picture below.

Now back to inter-leaf “Labels”, see picture below. Do the following:

From "Database" drop-down menu choose the database / spreadsheet which you added to LibreOffice Base earlier.
From "Table" drop-down menu choose the appropriate table from within your database / spreadsheet. Most likely there is only one item to choose from.
Now you'll see all your data fields (column titles from the spreadsheet) in "Database field" drop-down menu. Pick them one by one and click the big left pointing arrow to add each field to the “Label text” box on the left. All fields you now add to the “Label text” box, will be included in your labels. Vice versa, do not add those fields you do not want to show in your final labels. When you are done, click “New document”.

Now you see a sticker / label sheet in front of you. See picture below. The merging of your own address data has not yet been done. But instead you see the field names (address spreadsheet column titles) you chose in the previous phase on each sticker / label.

If you want, you can now edit the looks of the upcoming address stickers by editing the first label (top left corner). You can add for example empty lines or text formatting like fonts, colours, bold / italic etc. When you are done, just click the “Synchronize labels” button on the floating window. This will copy the formatting changes to all labels / stickers.

If you want you can now save this document for later use.

6. Printing or saving the mail merged labels

A bit surprisingly the next step is to print the labels!

From top menu choose File > Print. Now LibreOffice automatically notes that “Your document contains address database fields. Do you want to print a form letter?”. Click “Yes”.
A Mail Merge window opens. You can now choose to print or save all labels, a range of labels (use “from” and “to” fields to define the range) or random labels (ctrl+click those addresses you wish to print/save). Let's now choose “All”.
From “Output” section you can choose if you want to directly print the labels to a printer or output the mail merge to a file and thus save the labels in their own document. Let's now choose “File”.
You can leave the “Save merged document” option “Save as single document” as it is. I guess this is most useful when you are mail merging contact information to for example business letter and want to save each letter in their own files. Click “OK”.
Now you will be asked for a file name and location for the document with your final labels / stickers.

You can close all LibreOffice windows.

7. Final manual editing of the labels before printing

Open the just created final label document.

You can now see your address data on labels / stickers. If everything looks good, you can just print the labels on physical stickers.

However, quite often there is need for some final fine tuning of random labels (typos and other “oops” situations). For some odd reason you are not allowed to edit the labels by default. If you try, LibreOffice just says “Readonly content cannot be changed. No modifications will be accepted.” To enable the editing do the following:

From the top menu choose Format > Sections
Click any of the listed sections on the left to activate the field. Then type ctrl+A to choose all. And finally un-tick the box “Protected” from “Write protection” section of the window. Click "OK".
Now you can edit and fine tune your labels freely.

Save and print the labels according to your personal needs.

Comments

Niiranen Home @ Web

For Sale!