OpenOffice.org Training, Tips, and Ideas

How to do a holiday letter using mail merge, and print labels for the envelopes

I've done a lot of mail merge postings, but it's A) a powerful tool that B) isn't that obvious and C) is particularly useful at this time of year. (Me, I don't actually do holiday letters, not yet anyway, but I enjoy the ones I receive from friends.) So this is another one for this year. I've tried to make it as complete as possible without actually rising up out of the browser and knocking you over the head with painful details.

Here's how to create physical, printed-on-paper, snailmailable letters and labels, soup to nuts. 

If you want to try doing it via email, try this link instead.

http://openoffice.blogs.com/openoffice/2008/10/doing-an-email-mail-merge-without-the-overhead-of-the-openoffice-mail-merge-wizard.html

Step 1: Get the data in order.

Take a look at the data. Make sure you have the fields you need. Are you planning to address the letter to the <Lastname> family? You're probably fine then but if you want to address it to two people who live at the same address with different first and last names, you won't be able to do that without some data juggling. Consider in that case just sending two letters. Also if you want to use Mr., Ms., Miss, etc. in the letter or labels, make sure that info is in alllllll the address records. Otherwise you'll have an empty space in your label or salutation. Not the worst thing but a little off. In general, simplest is best.

If you have to assemble the address data from various bits and drabs of info on your computer, I suggest putting it in a spreadsheet. You must create one column for each separate piece of information, and you must (should, but I'm saying must) have a heading for each column, as shown.

Step 2: What format is the data in?

If the data is in your mail client address book, you can use that. If it's in a CSV text file, you can use that. Spreadsheets work too. So do Access databases. mySQL, Oracle, pretty much anything. Just make sure you know that the data is in an acceptable format, and you know what format it is. 

Step 3: Create the little database file that lets you suck the address data into a letter.

Follow the steps for the type of data you have.

YOU ONLY NEED TO DO THIS ONCE FOR EVERY SET OF DATA YOU HAVE. Once it's set up, it just sits there, ready for you to use in any mail merge document you want. (Letter, labels, another letter, invitations to a holiday party, birthday cards for everyone in your list with a January birthday, and so on.)

Text File Instructions

If your data is in text files, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Text as the format.

3. Click Next.

4. Specify the DIRECTORY where the text files are. Each text file in that directory will be a table in your database. Then select the item separating fields, i.e. a tab or comma or something else.

5. When all the settings look correct, click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.

You're done.

Make the Data Source: Spreadsheet Instructions

If your data is in a spreadsheet, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Spreadsheet as the format.

 

3. Click Next.

4. Specify the spreadsheet file. Each SHEET in that spreadsheet will be a table in your database.

 

5. Click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

 

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.
 

Address Book Instructions

Start off the same way as either of the other two, but specify the type of address book such as Thunderbird address book. Then click Next; the program will find it. All you have to do then is, as instructed in the other two approaches, save the database and name it as something you'll remember.

Step 4: Create the letter you want to write.

This has absolutely nothing to do with mail merge. Write the letter, put in graphics, go nuts with formatting. DON'T SAVE IT IN .DOC FORMAT; IT NEEDS TO BE IN NORMAL OPENOFFICE FORMAT. BE SURE THE FILE EXTENSION IS .ODT.   

Write in the name and address of a real person to help you figure out what fields you want.

Step 5. Put in the fields from the data.

Choose View > Data Sources. Everything you've created will be displayed. Click the + sign by the data source you want to use, then click + by Tables til you see the data you want to use.

Find one of the pieces of data you wrote, like "Bob" which is a first name. You're going to delete that, then drag in the FirstName field to the place where it was.

Click on the NAME OF THE FIELD, not the piece of data, that you want in the mail merge.

Drag it into the document and release. The field name will appear. If it goes to the wrong place, just cut and paste it to where you want it.

 

Continue with any other fields that need to replace any other pieces of individual data.

Step 6. Print the letter

I suggest printing to a file ("Individual" file), opening the file, checking it, then printing that file.

The following steps show how to do that. If you want more control, here's how to filter, sort, restrict, etc.

http://openoffice.blogs.com/openoffice/2009/08/queries-in-openoficeorg-mail-merge-how-to-print-only-documents-for-the-records-you-want-in-the-order.html

Choose File > Print. Click Yes when it asks if you want to do a form letter.

In the window, be sure that the correct database is selected, then select File and Individual.

 

Click OK. Name the file. This is the file that will contain a copy of the holiday letter, one for each person in your address book. It will say Dear Bob, Dear Tina, etc. It will be a normal Writer document, no fields in it or anything special.

 

Name it and click Save.

The mail merge process will run.

Now open that file, the merged output file, and check to be sure it's been created the way you want. If it isn't, go back to the mail merge letter (the one you dragged fields into) and make adjustments. Then print again, creating another output file.

Once the output file is how you want, just print it normally.

Step 7: Create the labels

Now that you've got the database set up, creating the labels is easy. Create the labels for whatever label type you're using, following these instructions.

Then print the same way you printed the letter; just be sure that when you're ready to really print on label paper, that you insert the label sheets in the way that works for your printer.

http://openoffice.blogs.com/openoffice/2006/07/mail_merge_labe.html

Step 8: Laboriously fold, stuff, label, and stamp the envelopes.

Step 9: Consider doing it with email next year.

http://openoffice.blogs.com/openoffice/2008/10/doing-an-email-mail-merge-without-the-overhead-of-the-openoffice-mail-merge-wizard.html

How to make the print window for mail merge bigger in OpenOffice

It's not wildly obvious, but you can make that window bigger.

Here's it's natural state. Not that big if you want to scroll through all the records or fields.

Just drag it to the right to make it wider, and up to make it higher. I'm not positive how it works on all OSes but it seems like it should.

Queries in OpenOfice.org Mail Merge: how to print only documents for the records you want, in the order you want them

I hate it when I blithely skip by fantastic built-in features.

No more skipping by.

So you've got 2000 employees, or members, or whatever. You need to send out a letter reminding people to, oh, sign up for benefits, or renew their membership, or maybe it's just a congratulatory letter for some people who have five years of service now and are going to get that sterling silver ice bucket they've always wanted. Or let's say you need to print labels for everyone but you need to do it in order by zip code or the people at the post office are going to be very annoyed.

You don't need to set up a query ahead of time in Base or in Calc. You don't need to go into Base or Calc and sort the information. You could, it would be fine, but it's more work than you need to do.

Just specify what you want, when you print.

You know how to print a mail merge letter, envelope document, or label document. You choose File > Print, click Yes in that little window that asks you about a form letter, and you get the print window.

Check out the top row of icons.

 

Sorting

Here's what you get when you click the Sort icon. Just choose to sort by a field, up or down, then others if you need to. Just like the sort in Calc.

 

Or if you just want a quick sort-by-one-field thing, click on  column of your choice, then click the A>Z or Z>A icon to sort ascending or descending.

Filtering

To use the quick autofilter, click on a particular cell in a row. One single value, like the city where one person is from. Click the AutoFilter icon; you'll get all the records that match that value.

 

Results:

 

If you want a little more control over the filter, click the Standard Filter icon. It's just like the standard one in Calc. NOTE: The AND/OR fields in the 2nd and 3rd rows aren't enabled until you select something to filter by in those rows. It looks like they're not available, but they are.

 

And results.

 

To turn off the standard filter, click the Apply Filter icon; to get the filter back on, click it again.

You can also remove any filter or sort with this icon.

When you're done, just print as usual; I recommend selecting File, and Save as Single Document. Click OK and name the output file.

This creates a perfectly ordinary Writer document with all the fields merged, one copy of your original mail merge document for each record you chose to print by. 

Then you open that Writer document, check it over a bit to be sure it's what you want, then print that as you would any other Writer document. (The illustration cuts out the bottom half of each page for space reasons; you would have an entire page for each person.)

Printing a group of certificates using mail merge, Draw, and Writer

You've had a contest, or a class, or something where you want to give out certificates of recognition.

Normally you would do this in Draw.

But there are 435 people to give certificates to and darned if you're going to type in 435 names by hand when you already have the list of names in a spreadsheet. (Or some type of list.)

So here's what you do.
1. Create the certificate, maybe just download it off the Internet or else create or embellish your own certificate design in Draw and export it as a PNG graphic
2. Make the certificate graphic the background of a new landscape Writer document
3. Set up a mail merge with the spreadsheet of names as your data source
4. Drag in the Firstname and Lastname  from the data source into your Writer document and add any other text, like "Congratulations for Completing" and the date, course, or other information
5. And print

1. Create the certificate.

You can just google +certificate +image  or other parameters such as +free and find a lot. Try Google Images. You might not find something free but you might find something for a dollar that you can use again and again.

Download the image to your computer, then create a new Draw document and choose Insert > Picture > From File to bring the picture into the Draw document. Or just copy and paste it into a Draw document.

Make sure it's the right size, so choose Format > Page, Page tab, and select Landscape. Click OK. Then drag the corners of the document so that the picture fills the whole printable page space.

Make any changes you want to the graphic such as making it redder, making it gray scale, whatever. When you click on the graphic you get the options in the upper left corner of this drawing.

When it looks like you want it, select it and choose File  > Export. Select PNG and select Selection, and click Save.

When prompted for compression, choose 0.

2. Make the certificate graphic the background of a new landscape Writer document 

Create a new Writer document.

Choose Format > Page, Page tab. Make it Landscape, with the minimum page margins that your printer can handle. 

Then click the Background tab. Select Graphic instead of Color at the top, then browse to find the PNG file that you exported. Select Position. Click OK.

 

Make the document look the way you want and leave a space for the name.

 

Be sure to save the document.

3. Set up a mail merge with the spreadsheet of names as your data source
See this blog entry. You have the spreadsheet so create a database file that points to it.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

4. Drag the names onto the Writer document.

Continue the process in the blog by viewing your data sources (View > Data Sources or press F4) and select the data source you created, then expand it and select Table and the sheet name. Drag the field names (not the data but the headings of the fields) into the place on your Writer document where you want them.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

5. And print.
When you print, follow the printing instructions for mail merge. File > Print and when the message appears asking if you want to print a form letter, choose Yes.

Choose to print to the printer to print immediately, or (better) print to another Writer file first to see what it would look like. Then print that exported Writer file. To do this, choose File, then click OK.

When prompted for a file name, type something like printed_certificate_file  .

The files will print for a while. Then choose File > Open and open the the file you created, the printed_certificate__file. It will look something like this, with the real data in it.

Once you've done this once, you can use it over and over. Just put different data into the spreadsheet and the certificate document will draw that new data into the spreadsheet.