Database mail merge
Nick Peers reveals how to save time and master mail merge in LibreOffice using a combination of Base, Calc and Writer.
Nick Peers is getting everywhere, so much so, he’s having to make a cutting-edge mail merge to keep his Christmas card list up to date. Libre Office helps out!
Anyone who’s had to write the same letter to many different people, dutifully substituting names and addresses for each one, will appreciate what mail merge can offer. (So will those too lazy to write names and addresses on envelopes at Christmas time). Thankfully, LibreOfficeWriter possesses a tool that can simplify the process in the form of its Mail Merge Wizard. A mail merge works by taking a list of contacts and automatically inserting key information from that list into specific parts of a document – eg the address block in a letter, or the personalised greeting that opens it. The contacts list is maintained separately and can originate from one of three places: a database, a properly formatted spreadsheet or a CSV file.
LibreOffice even allows you to use an existing Thunderbird or Icedove address book as the source for your contacts list, although this involves converting it into a read-only database using the File > Wizards > Address Data Source… option. For the purposes of this tutorial, though, we’re going to focus on using a properly formatted spreadsheet as the basis of our contacts list. This spreadsheet can then be directly imported into the Mail Merge wizard ( seeMailmergeusingWriterand Calc,below) or used to populate a Base database that gives you greater flexibility over its use.
Set up your contacts list
Your contacts list should – in the grand manner of all databases – be organised into fields and records. Each individual’s contact details form the basis of each record, and the component parts of that record are organised into fields: such as first name, surname, street address etc. This data is then represented as a table, with each field assigned its own column and individual records recorded along each row.
The first row in any spreadsheet should contain the column headers, which help identify each field. If you’re starting out from scratch, open Calc and create a blank database. Type each field name into the columns along row A – here’s an example of what to include: first_name, last_ name, address, town, county, country, postal code, home_ phone, work_phone, mobile, email, website, category.
Obviously, you may need to include more details – eg some addresses require a second line, so include address1 and address2. Similarly, you may have no need for a country
or website, so feel free to exclude these. One particularly important column header is category. This allows you to assign each contact to a particular group, such as ‘home’ or ‘work’. To find out more about this field – and add it to an existing list of contacts – check out the Prep Calc Database for Transfer box ( seep77).
With your column headers in place, you’re now free to record each contact’s details. Remember, each contact takes up a row in your database, so your first record is recorded in row 2, the second in row 3 and so on. Don’t forget to save as you go, and once you’re done, congratulations – you’ve created your contacts database. Don’t worry too much about recording people’s information in alphabetic order – simply select the column containing your surnames and click the ‘Sort Ascending’ button on the toolbar should you want to keep things nice and neat.
Transfer to Base
As things stand you’re now ready to jump straight into using Writer’s Mail Merge tool ( theboxonp77revealshowthis worksusingyourCalcspreadsheet). But while Calc is adequate for the task, there are reasons to go further and use the data from your spreadsheet to create a fully blown database using LibreOffice’s database component, Base.
First, Base supports more complex databases – including relational databases that allow you to create multiple tables of data and link them together. If, eg, you have a large DVD collection that you lend out to friends, you could track lending using three tables: your original contacts list, a second table listing all your DVDs, and, finally, a third table that allows you to link DVDs with the people who’ve borrowed them.
Second, Base supports queries, which allow you to create custom subsets of your contacts list based on a series of filters. In this tutorial, we’ll use queries to allow you to set up mail merged documents targeted specifically at contacts that reside in a particular group or category of contacts, but you can go further. You could set up a query to filter your contacts by the town they live in, or their surname ( Base allows you to match contacts based on multiple criteria, so eg you could include three or more different surnames to cover everyone in your family).
Base also provides a better way of inputting data into your contacts list by allowing you to set up a form-based entry screen, which is easier to navigate than a table. And finally, you can use your contacts list to generate reports of your data using Base’s Reports tools too – eg a neat way to generate a formatted printout of your contacts.
The good news is that moving from Calc to Base doesn’t mean you have to start your contacts list again from scratch. Here’s how to import your data from your spreadsheet: first, open Base and the Database Wizard will open. Leave ‘Create a new database’ with the default ‘HSQLDB Embedded’ option selected and click ‘Next’. Leave the default settings as they are and click ‘Finish’. Browse to your desired location – typically Documents, then give your database a name and click ‘Save’.
Now switch to Calc and open your spreadsheet. Select the data to be copied – including the all-important column header – and choose Edit > Copy. Move back to Base, make sure in you’re in Table view and choose Edit > Paste to open the Copy Table window. Leave ‘Definition and data’ selected, then make sure both boxes – including ‘Create primary key’ – are ticked before clicking ‘Next’. Review which fields you want to import – click ‘>>’ to import them all, or select each one in turn and click ‘>’ to add them to the list. Click ‘Create’. You should see ‘Table 1’ appear under ‘Tables:’ double-click it to verify all your records have been imported. Close the window when you’re done. Now your database has been created, it’s time to add some queries to it, which will allow you to filter your contacts according to friends, work colleagues or whatever criteria you’ve set. Follow the three-step walkthrough to do so ( seep76).
Let’s merge
Once your contacts list and queries are in place, you’re ready to start taking advantage of them in Writer. The good news is that a mail merge wizard is provided for letters and emails, which makes the process as straightforward as possible. It works by generating a single document that contains multiple pages or sections, one for each person in your contact list.
The six-step walkthrough steps you through the entire wizard: select your data source, which is one of your database’s queries for a filtered list, then set up your two mail-merged elements, namely the address block and personalised greeting. Then it’s on to creating the document, adding personal touches to individual letters and finally saving, printing or emailing the finished result. Make sure you’re happy with the generic version of your letter before you jump to step seven of the wizard, where you can edit specific letters if you wish. If you go back and edit the main document, those personalisations will be lost.
Once you’ve finished creating your mail-merged letter, you’re given four choices: the first is to save the starting document, which saves the basic document. Second is ‘Save merged document’, which gives you the option of saving a single multi-page document or saving each personalised document separately – choose a filename, and Writer will append a number to the end of each one. You can also save a specific range of documents by entering the start and end numbers.
Option three allows you to print off your merged documents – again you can print them all or specify a range. Finally, you can use the email field in your contacts list to send out emails of the document to your contacts (plus manually enter addresses in the CC and BCC fields by clicking ‘Copy to…’). Specify a subject, choose to send the message as plain text, HTML or in an attachment ( OpenOffice, PDF or Word are supported) and again opt between sending to everyone or specific people in the list only. Click ‘Send documents’ and then – if necessary – enter your email account information into LibreOffice for it to use to send the email.
Printing labels and envelopes
One final way in which you can use your database of contacts in Writer is to print out a series of envelopes (Insert > Envelope) or labels (File > New. Labels). Both work in a similar way: in the case of labels, leave the Address box unticked – this simply refers to your own return address as defined under Tools > Options > LibreOffice > User Data. Next, select your contacts list from the Database drop-down menu, then pick the table or query containing the addresses you wish to use from the Table menu beneath it.
Next, you need to select each database field in turn and click the left arrow button to add them to the Label text box. Things can get a little tricky here with placement – eg, don’t forget to add a space between the first and last name fields, plus add a line return between each part of the address.
Once done, use the Format options to set the dimensions of your label. Click the Brand dropdown menu to see if your labels are covered – there’s Avery, Herma, Leitz, Sigel and Tower. If it’s here, click the Type dropdown menu to pick the exact sheet; if not, switch to the Format tab to enter this information manually using your labels as a guide. Finally, the Options tab allows you to distribute your labels across an entire page or print them singly, plus configure your printer using the Setup… button.
When you’re ready, click the ‘New Document’ button and wait for Writer to generate the labels. You won’t actually see the names and addresses in your contacts list – just a series of field names. This is normal – when you select File > Print, you’ll be warned that your document contains address fields and asked if you want to print a form letter: click ‘Yes’.
The Print dialog will appear with your chosen contacts shown – you can then print them all, select a numbered range or hold the left Ctrl button as you click individual rows next to contacts to pick and choose exactly which addresses to print. You can then choose to output the file to your printer or save it as a file that, when opened, replaces the field information with the actual names and addresses of your chosen contacts.