If you haven’t tried OpenOffice.org’s mail merge feature because you find it confusing or difficult to use, you are in luck. Mail Merges in OpenOffice.org and StarOffice provides a detailed description of the mail merge feature from start to finish. Among other things, it shows how you can use the mail merge to create letters, labels, and envelopes. Download the free PDF ebook for your persusal or read the article online.

by Solveig Haugland

Overview of the Steps in Creating and Printing Mail Merges in OpenOffice.org or StarOffice

Step 1: How to Create or Double-Check Your Data

Creating the Middle-Man .odb Database File That Reads the Data

Spreadsheet

Text File

Access

Address book

Databases like mySQL requiring drivers

Creating the Mail Merge Document

Create a Mail Merge Document: Letter

Create a Mail Merge Document: Labels

Create a Mail Merge Document: Envelopes

Printing Your Mail Merge Document to Create the Merged Versions

Printing Specific Records

Printing a Consecutive Range of Records

Sorting Records

Printing to the Printer

Printing to a File for Previewing or Customizing Before Printing on Paper

Making Changes

DOWNLOAD THE COMPLETE EBOOK (PDF)

Conceptual Mail Merge Overview

A mail merge is a way to take a letter you’ve written and send it to a whole bunch of people, personalizing it with information about them so they might think that you typed that letter personally for them. A mail merge can also be a quick way to take a list of people’s mailing addresses and generate labels or envelopes with the address for a different person on each label or envelope. In short, it’s a way to be personal, yet efficient. It’s essential for any person or organization that has a lot of clients, partners, parents and children, or other people to communicate with.

Here’s a simplified conceptual overview of how a mail merge works in any application.

Let’s say that you need to send out an invitation to every person in your mailing list, thanking them for coming to an event. You make sure you’ve got the data set up correctly in a database, and you create a letter saying what you want to say. Then you display the database, you pull in a few fields from it like the name, city, or whatever specific information about each person that you need in the letter. Then you print the letter, choosing all the people or just specific people, and you get a copy for each one.

If you want to use the letter again, you just open it up again and print it again, specifying either all the people in the database, or just the ones you want.

To add people to the database, you just open up the spreadsheet, or the text file or other data source where the data is stored, and add them there. The mail merge document will be able to pick up on the new data as well as all the old data the next time you print.

Advanced option example: You could also choose to only print a copy for people who came to the event and donated $100 for fundraising. To do this, you’d need to have a column, a field, in your data that specifies the amount of their donation. When it comes time to print, you’d just sort by the donation field and print the range of records that have a value of $100 or more.

Overview of the Steps in Creating and Printing Mail Merges in OpenOffice.org or StarOffice

Creating a mail merge isn’t quick the first time, but once you’ve got your data source set up (the data, and the database file that points to it), creating the mail merge document and creating the actual documents you send to people is really quick and easy.

1. Create or find your data and make sure it’s set up correctly. You can have it in a spreadsheet, your address book, a text file, or a database like Access or mySQL. Once you do this once, you don’t have to do it again.

2. Create what I call the “middle man” file, a database file that functions as a go-between, between the data and your letter or your label documents. It’s easy to create and its role is to just let the letter or the labels pull information out of the data. As with the data, once you do this once, you don’t have to do it again.

3. Create a letter or label or envelope document, in Writer. It can be a blank document that you start typing in, it can be a template that you open and modify, it can be labels or envelopes you’re creating through the label wizard–any Writer document.

Once you’ve done this once, you don’t have to do it again. You can re-used the document just open it up and print it. If you’ve changed the data in the data source (your spreadsheet or database) since the last time you printed, you’ll get the latest up-to-date information when you print it now. You don’t have to do anything; the Writer mail merge document knows to look for the current information.

4. Then you print, which creates the actual merged documents. The output can be printed copies of the letter or labels, or it can be a Writer file that’s got all the merged documents in it (so you can customize it before you print).

This is the only step that you have to do again each time you do a mail merge: the production of the actual output. You need to do it again to be sure that you get the latest information from the data source, because the data has probably changed since the last time you did the mail merge. Or you might want to print copies for only a few of the people or records in the data source.

Note: This article doesn’t address using a native Base database for both your data and your “middle-man” file. There are many ways to create a native Base database from scratch and via importing. See this URL to create a Base database by importing: http://openoffice.blogs.com/openoffice/2007/12/starting-a-new.html and this one for creating from scratch: http://www.tech-recipes.com/rx/2142/creating_databases_the_quick_easy_way_with_openoffice_org#creatingnativedatabase
If you have a native Base database, that is the same as steps 1 and 2.

How to Create or Double-Check Your Data

You probably already have data around that you’ll want to use. You need to be sure that it’s set up correctly.

You have to have a unique label, a name, for each column of data. You’re going to want to put a specific field, like the First Name field or the Zip Code field, in your mail merge document, so you need to be able to refer to it by name. Every name needs to be different; you can’t have Name for the first names and Name again for the last names.

Also, the names need to be at the top of the file, not along the left side or on the second row. The first row of the file, whether it’s a spreadsheet or text file, needs to be the field name.

Note that the text file doesn’t look even. That’s OK – you just need to have one (and only one) tab between each column. You could also separate the columns with commas or semicolons.

To make sure that the data can be used in as many ways as possible, make the names lowercase and without spaces. “First Name” is bad, “Firstname” is better and “firstname” or “first_name” are ideal.

If you’re using an address book or a database, it’s probably set up correctly in function, and you won’t be able to change the names of the fields.

Here’s something else to note. In a spreadsheet, the sheet name is going to be the table name: the table is what the data is in. You might want to name the sheet something that will clarify what the content of the data. The same goes for text files and the file name: the file name will be the table name.

You can have multiple tables (sheets or files) in the same database. This is fine and probably more convenient for you. This spreadsheet above, for instance, has two sheets, customer_mailinglist and employee_mailinglist. There’ll be two tables in the same database, corresponding to those sheets. You just can’t use two tables in the same mail merge. For text files, all the text files in the same directory will be tables in the database. If you have C:\data\customer_mailinglist.txt and C:\data\employee_mailinglist.txt, each file will be a table in the same database.

If you’re using text files for your data, put all the text files that you want in the same database, in the same directory. Make sure that those files are the only things in that directory, since all usable data in that directory will be brought into the database.

Once you’ve got the data set up correctly, you’re done with the data and you don’t need to do anything else except add new data when you need to. The new data goes into the data files or your database and your mail merge documents will be able to find it automatically.

You’re ready for step 2.

Creating the Middle-Man .odb Database File That Reads the Data

OpenOffice.org and StarOffice are set up with a middle-man file in the mail merge process to make merging work easily. You’ll create a small file, one for each spreadsheet or other data source. Once you create it, you don’t have to do it again.

1. Choose File > New > Database. You’ll see this window:

2. Select the type of data: spreadsheet data, text file data, your particular type of address book, or the type of database you’re using like Access or mySQL. It’s very important to select the right type.

3. Click Next.

4. What you do here depends on what you chose as the type of data you’re working with.

Spreadsheet

You’ll see this window. Click the Browse button and find the spreadsheet containing your data. Then click Next and continue to step 5.

Text File

You’ll see this window. Click the Browse button and find the directory containing your text files.

Fill out the rest of the information:

Specify the type of files you want to access: Specify whether the file name ends in .txt or .csv.

Row format: In the Field Separator list, specify what character separates each column: a tab, a comma, etc. Tab and comma are common. In the other lists, if you don’t know the characters used to indicate each type of information, just leave the defaults as is.

Then click Next and continue to step 5.

Access

You’ll see this window. Click the Browse button and find the .mdb Access file containing your data. Then click Next and continue to step 5.

Address book

You don’t have to specify anything if you choose to get your data from your email address book; the system automatically finds it. Continue to step 5.

Databases like mySQL requiring drivers

You’ll be asked different questions depending on what type of database you chose. For instance, if you chose mySQL, you’ll first be asked whether you want to use ODBC or JDBC to connect to the database. Click Next.

Then just specify where the ODBC or JDBC driver files are. The information will vary depending on your database and how you connect to it.

Click Next and continue to step 5.

5. In this window, just be sure to keep the option for registering selected. Unmark the selection to open the database for editing unless you want to see the database editing window. (You don’t need to unless you want to make a query or other database-related item, which we haven’t talked about yet.)

Click Finish.

6. You’ll be prompted to save the database file. Name it something very descriptive; this is the name you’ll be looking for when you’re adding database files to your mail merge documents. The name can be the same as the data source that you’re basing it on, or entirely different. The file will end in .odb and be stored in whatever directory you choose. It doesn’t have to be in the same directory as the data.

7. You can see your databases by pressing F4 when you’re in a Writer or Calc document. Click the + icon next to the database you created to see the items named Queries and Tables. Click the + next to Tables to see the tables you created; select a table to see the data in it.

When you’re done viewing your databases, press F4 again to hide the pane.

You’re done creating the database file. You only need to do this once for every spreadsheet, database, or directory of text files.

Creating the Mail Merge Document

In this step, you create the Writer document that contains the fields from the database like firstname, lastname, and so on. If it’s a letter, then you also put in the text of the letter, any graphics or letterhead, and so on.

Follow the steps for the type of mail merge document you want to create: a letter, labels, or an envelope.

Create a Mail Merge Document: Letter

1. Open a template, if you have one you want to use, or create a new Writer document.

2. Save the document with the appropriate name, like mailmerge_openenrollment.ods or mailmerge_parents.odt.

Warning: Don’t save it in Word format. You must save it in OpenOffice.org Writer format or the mail merge won’t work.

3. Write out the text that will be going to everyone, and plan where you want the fields. For instance, you might know that you are going to have an address block at the top of the letter, so you’ll leave a few blank lines for that. Then you’d write something like this, know that you’d add the fields firstname and years_of_service later:

Dear ,

Remember that next month is open enrollment for benefits. Employees with over five years of experience are also eligible for sabbatical; you have been with us for years so please get your application in early if you plan to apply.

Regards,

Human Resources

If you’re doing anything complex and this is your first mail merge letter, write out the letter completely as you want it to read including sample data. Use all the text, including samples for firstname, lastname, etc. This will help you determine which fields you need to use, where you need spaces before and after fields, etc.

In the following example, for instance, if you want to communicate this, you’ll need to use fields from the database for title, lastname, childs_name, study_area, and test_score.

Dear Ms. Smithson,

Your child Jenny is such a joy to have in the classroom. Her score on her Social Studies test score last Friday was A-.

Regards,

Mr. Thomson

4. Once you have the letter written out and you know what fields you need, you can delete the specific data like “Ms. Smithson” and insert the fields from the database instead.

Press F4. Click the + next to the database you want, then click the + next to Tables and the + next to the table you want.

5. Now just drag the field you want into the letter, to the place you want it.

a. Click and hold down on the name of the first field you want. Don’t click on the data, like Smithson; click on the name of the field, like lastname.

b. Drag the field name into the letter to the place where you want it. The mouse will have a + atached to it as shown.

c. Release the mouse. The field will appear. You won’t see the data yet; you’ll see the field name.

If you didn’t get it exactly where you want it, just select it, and cut and paste it to the place where you want it. The easiest way to select a field is to click on the right side of it, hold down the Shift key, and press the left keyboard key once. Then it’s selected and you can cut or copy.

Drag in all the other fields you want, the same way. If you want a field in the letter twice, you can either drag it twice, or drag it in once, then copy and paste it to the other location.

The fields should display with a gray background, as shown. It won’t print; it just helps you see which text is from the database and which text you typed.

Format the document any way you want it. The fields respond to formatting the same way that normal text does.

You’re done creating the mail merge document. You only need to do this once. The next step is to either print the letter to a printer, or “print” to a Writer file so you can see all the data merged, and so you can customize, before printing on paper. Continue to the printing step.

Create a Mail Merge Document: Labels

1. In Writer, choose File > New > Labels.

2. In the window that appears, select the appropriate Brand and Type. You must select the right choice in the Brand list, usually Avery Letter or Avery A4, or the type of label you want won’t appear in the Type list.

3. Select the Database and Table you want to work with.

4. Select the first field you want to use, such as firstname, in the Database Field list. Click the arrow button next to it to insert it in the Label Text field. It will appear as shown, with the databasename, then the table name, then the field name.

5. You’ll need to type any spaces, carriage returns, and any other extras you want in the labels yourself. So typically you would want to type a space after the field, in the Label Text area, then select the next field, lastname, and click the arrow button to insert that too.

Then press Return, insert the Address field, and so on until you’re done.

6. Click the Options tab. You’re almost done, but it’s a good idea to add a feature that will let you customize the labels after you’ve created the mail merge document itself. Select the Synchronize Contents checkbox. You’ll see how to use it in a moment.

7. Click New Document.

8. You’ll see the mail merge label document, with fields displayed instead of data. (You’ll be able to see the data in the next major step, printing.)

Notice that there’s a small floating Synchronize button. You might not need to use it. But consider the following: you might not like the text formatting, or you might realize you forgot to type a comma after the city field. It would take a lot of time to make those corrections in 30 or more labels.

With the Synchronize button, you make the corrections just once, in the upper left label, then click Synchronize and the changes are made in all the labels.

9. To make a formatting change, do it in the upper left corner as shown:

…then click Synchronize Labels and the changes are made in all the labels.

10. Optional: Here are some other changes you can make. You can add a graphic such as a logo. Click in the first label and choose Insert > Picture > From File. Resize it so it fits in the label, then right-click on it and choose Wrap > Page Wrap so that the text will flow around it. Make the text font size smaller if necessary so that it will all fit in along with the logo. Then click Synchronize Labels to put the logo on every label.

Here’s something else, totally optional, that you might want to try in order to make sure your labels don’t get cut off as you print on slightly different printers. Select the text in the first label and drag the small bottom triangle in the illustration to the right to give it a little room on the left margin.

Then click to the left of the first line of the label, press Enter, and click in the blank line created above the field names. Click in the Font Size field and type something small like 4 or 6, then press Enter again. You’ll get a small space above the label.

Then click Synchronize Contents to make the changes in every label.

11. Save the mail merge label document a recognizable name like labels_openenrollment.odt.

Warning: Don’t save it in Word format. You must save it in OpenOffice.org Writer format or the mail merge won’t work.

You’re done creating the mail merge label document. You only need to do this once. The next step is to either print the letter to a printer, or “print” to a Writer file so you can see all the data merged, and so you can customize, before printing on labels. Continue to the printing step.

Create a Mail Merge Document: Envelopes

Envelopes are very similar to labels, you’ll just need to specify what type of envelope you’re using, instead of the type of label. Refer back to the Labels directions for more detail on using envelopes.

Note: You can also create envelopes the same way you created letters: just set up the page in envelope size under Format > Page, Page tab. It needs to be Envelope 10 page size, landscape, with very small borders. You’ll also need to indent the text in the To area to get it in the right spot.

1. In Writer, choose Insert > Envelope.

2. If you want to type return address information, do so in the From area.

3. Select the Database and Table you want to use.

4. Select the first field you want to use, such as firstname, in the Database Field list. Click the arrow button next to it to insert it in the Label Text field. It will appear as shown, with the databasename, then the table name, then the field name.

5. You’ll need to type any spaces, carriage returns, and any other extras you want in the labels yourself. So typically you would want to type a space after the field, in the Label Text area, then select the next field, lastname, and click the arrow button to insert that too.

Then press Return, insert the Address field, and so on until you’re done.

6. The next step is the most important part of creating envelopes. Click the Format tab and in the Size list, select a specific envelope format from the list. I recommend Envelope 10. This selection MUST be the same that your printer is set up to expect when you print.

Next, you need to be sure your printer is also set up for Envelope 10, or the same choice you made in the Format tab. Click the Printer tab and click Setup as shown.

Now select your printer and click Properties.

In the window that appears, which will vary depending on your printer, you need to find the field where the size of the paper is specified. Select what you specified in the Format tab, typically Envelope 10. Click OK, and OK again to get back to the envelope wizard.

7. Select the correct option from the envelope orientation, if you know it. If you don’t, leave it as the default and if necessary repeat these steps to try a different orientation.

8. Click New Doc., not Insert. It’s best to keep envelope documents separate from letter-size documents because of the need to have the printer set up specifically to expect envelope-sized documents.

9. Save the document something recognizable like ‘envelope_openenrollment.odt’.

Warning: Don’t save it in Word format. You must save it in OpenOffice.org Writer format or the mail merge won’t work.

You’re done creating the mail merge envelope document. You only need to do this once. The next step is to either print the letter to a printer, or “print” to a Writer file so you can see all the data merged, and so you can customize, before printing on envelopes. Continue to the printing step.

Printing Your Mail Merge Document to Create the Merged Versions

Note: If you’re printing on labels for the first time on this printer, you’ll need to experiment a few times before you arrive at the right way to insert the envelope. Be sure to just print one record at a time until you’re figured this out.

1. With your letter, label, or envelope document open, just choose File > Print.

2. You’ll see this message. Click Yes. (Always click Yes, or you’ll just end up with a single printed version with <firstname> and <lastname> instead of the data.

3. You’ll see the mail merge print window. Here you can choose how many records to print, and how to print them: to the printer right now or to a file so you can view and edit them before you commit to paper.

4. Choose what range of records to print. The easiest is to just leavel All selected.

Printing Specific Records

You can specify exactly the ones you want with Ctrl – select. Just click on the gray box to the left of the first record you want to print, then hold down Ctrl and select the next record, and so on. In that case you want Selected Records to be marked, as shown.

Printing a Consecutive Range of Records

You can also specify a range of records such as 1 to 5, by making the appropriate selections in the From fields.

Sorting Records

Optional: You might want to specifically print a letter, labels, or envelopes only for people who meet certain criteria, like only people with five or more years of experience. To do that in this case, click on the field name for years_of_experience, as shown. Then find the Sort icon at the top. It’s easiest to sort Descending in this case, with the people with the most years of experience first.

When you click the Sort Descending icon, you get the results sorted in that order. And since you can see that the last person with five or more years of experience is Record 11, you can choose to print from 1 to 11.

5. Now choose where you want to print.

Printing to the Printer

If you’re ready to print straight to the printer, leave Printer selected as it is by default and click OK. You’ll see the normal print window. Just leave the defaults as is, with 1 copy of each document, and click OK.

If you’re printing labels, be sure to feed the labels into the printer. If you’re printing envelopes, click Properties and be sure that the printer is still set up to expect Envelope 10. Then click OK and feed in envelopes.

Printing to a File for Previewing or Customizing Before Printing on Paper

To print to a file – that is, to just generate a Writer document with the merged results – select File and Save as Single Document. Then click OK.

You’ll be asked to give a file name. This is the name of the merged document. Name it and click Save.

You’ll see the print monitor counting through the records.

Now choose File > Open and open that file you just named.

Note: When you open the output from merged labels, don’t click yes when you see this message. Click No. If you click Yes accidentally, just close it without saving, and reopening it, clicking No this time.

You’ll see the merged results.

Here’s one example of a merged letter:

And merged labels:

At this point you can view the document and proof it, make any changes you want, and save it. Then to print it, just print it like a normal document, since it’s already merged with the data from the database.

Making Changes

If you need to make changes to the document, such as adding spaces around a field, adding fields, or adding text, just go back to the mail merge document itself and make those changes. Then repeat the steps to print in this Printing section.

by Solveig Haugland of http://www.openoffice.blogs.com/

Visit Worldlabel.com to check out our Avery® Labels Cross Reference Chart for all popular size labels