Ceci est une ancienne révision du document !
LibreOffice Series 7: Mail Merge
Up until now we have been working in LibreOffice Writer, but today we will step away for just a second to do something a little different. We're going to create a poor man's database in Calc and then return to Writer and create a mail merge. This will give you some ideas about how you can use a spreadsheet as a database. In order to do a mail merge, we need to work with some sort of database. We could create our own in Base, but for a simple address book, Calc is an easier solution.
We will start by opening LibreOffice and clicking on Spreadsheet. This will open Calc with a blank spreadsheet. We won't go into a lot of details about the layout of Calc; we'll save that for another day. What you do need to know is that a spreadsheet is laid out as a series of columns and rows. The columns are labeled above with letters (A,B,C,…) and the rows are down the left side labeled with numbers (1,2,3,…). When we use Calc as a database, the first row must contain the field titles for the database. These are the database field headers. We will put the following field headers in the first row: Title, First Name, Last Name, Street Address, City, State, Postal Code, and Country. Each field must be in a separate column starting at column A, so put the cursor in column A of the first row and type in Title. You can use the right arrow key on the keyboard to move to the next column, and fill in the rest of the fields.
Starting with the second row, we begin to fill in our data. Each row is a record. Think of a record as all the information on one person. For our purposes today, you don't need to worry too much if the data is wider than the columns. If you want to adjust the width of the columns while you input your information, just place your mouse over the line separating the two columns. The cursor will become a double arrow, and you can click and drag to increase or decrease the width of the column. Once we have entered all our data, we need to save the spreadsheet. I saved mine as Poorman_db.ods. Once it is saved, close it.
Before we can use our poor man's database, we need to register it in LibreOffice. File > New > Database will bring up the database wizard. Select “connect to an existing database”, and in the drop-down box, select spreadsheet. Click the Next button. Use the Browse button to find your spreadsheet. Click Next. Make sure “Yes, register the database for me” is selected, and uncheck “Open database for editing.” Click Finish, and give the database a name (I named mine Addresses). The database you just created is linked to the spreadsheet. Any updates you make to the spreadsheet will show in the database. The only catch is you cannot edit the spreadsheet when you have Writer opened. Basically, your spreadsheet database can be the only LibreOffice document open. Otherwise, LibreOffice will open the spreadsheet in read-only mode.
Now that we have created our database, and registered it in LibreOffice, we can use it to create a mail-merge. Mail-merge is great for sending form letters to customers or prospects. The cool thing is you can put any kind of data you want in your database and then use it in the letter. For example, if you have a rewards program for your customers, you could have a field for points, and include the points each customer has earned in the letter, but you would have to type the letter only once.
Click on the Data Sources icon [database_icon.png], View > Data sources, or press F4. This will display the registered databases below the formatting toolbar. In the left part of the data sources, Addresses (or whatever you named your database) > Tables > Sheet1. On the right side, you will see your data laid out much like you entered it in the spreadsheet. We insert the fields into the document by clicking on the field header and dragging it into the document. This will create a field placeholder for every field you drag into the document. Make sure you drag the field header and not a data block in a record.
Now, you can type the body of your letter. When you need a field from the database in your letter, you can drag it into the document. The database fields can be used any place in the document. As I said before, your database can contain whatever information you need it to contain. A teacher could even use it to report students' grades in a letter to parents.
Once you have finished typing your letter, you can print or send to a file. You might want to send a letter to file when you need to go back and add personal information for certain individuals. Like a teacher might want to request a parent-teacher conference for a student failing the class. File > Print, just like you would for a normal document. However, LibreOffice will tell you that your document contains address database fields and asks you if you want to print a form letter. Answer yes. The Mail Merge dialog will appear. In the dialog, you can choose to print to the printer or to a file. Also, you can choose to print a letter only for selected records or a range of records. You select records by clicking on the gray box at the beginning of the record, or use CTRL-click to select only certain records, or SHIFT-click to select a series of records. When you print to a file, you can choose to print to one file, in which case each letter will begin on a new page, or save as individual files, in which case each letter should have its own file. You can select which database field to use for the file name. On my version of LibreOffice (3.3.3), I got one document whichever I chose. Apparently, they are still working on this feature.
You can also use your database to generate labels. File > New > Labels opens the labels dialog. Select your database and table. Move the fields over into the label information box. Select your label type and click New Document. As with the form letters, when you get ready to print, you will have an opportunity to select the records you want to print, and you can print to a printer or a document. You can also create envelopes much in the same manner.
There is another way to create a form letter, using the Mail Merge Wizard: Tools > Mail Merge Wizard. The wizard has some restrictions, and when I tried to use the wizard, I found it more difficult to get good results. I found the manual method I have described here to give better results, giving the creator more control over the output.
Mail merge is a great time saver. Bang out your letter, drop in some database fields, and print. Sure beats typing 100s of letters or manually editing each one to change the information. In my next article, we will start to learn more about Calc by creating a simple budget spreadsheet.