OpenOffice.org Training, Tips, and Ideas

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.)

A good old-fashioned envelope template for OpenOffice.org Writer

You can use the Insert > Envelope feature, but this is simpler.

Just download this file to your computer. Then every time you want an envelope, just double-click it, and type in the information you want.

Then print.

Here are the two things you need to do.

One: Be sure that there are envelopes ready to feed into your printer.

Two: When you've typed in the information, make sure that your printer is set up correctly to print to Envelope 10. So choose File > Print, select the printer to print to, click the Properties button next to it, and you'll see the setup window for that printer. In a tab called Paper or something like it, you'll find a field where you pick the paper size. (It varies between printer brands.) Instead of Letter, be sure you select Envelope 10 instead. This is for normal fullsized envelopes.

Click OK, then click OK to print.

----------------
(If it doesn't work, insert the envelope different ways until it lines up correctly.)

The most straightforward wizardless way to create an envelope in OpenOffice.org Writer (and then reuse it as a template)

Envelopes can be problematic. Yes. This is primarily because envelopes are not Letter or Legal. Each printer has its own approach to printing, and you'll need to do a little experimenting.

But you can do it. The Envelopes category and the Mail Merge catogory of my blog each has various approaches.

But with this new entry I've tried to clarify what I think is the best and most straightforward approach to making envelopes. The thing is, creating the first doc is a little work, and figuring out how to print is a little work. But once you do it, you don't have to create the envelope document again. You don't have to figure out which way the printers go in. You know. So the second time and all other times, you just open the old doc, whether you made it into a template or just saved it in

C:\myreusabledocs\envelope.odt

and retype the info. Then you print, and you now know how to print.

Just do the hard work once. Then re-use.

THE PART YOU ONLY HAVE TO DO ONCE: CREATING THE ENVELOPE THE FIRST TIME AND FIGURING OUT HOW TO PRINT

A) Make the envelope document once

B) Print

C) Save it as a template so you never have to make it again

THEN ONCE YOU'RE SET UP PRINTING ADDITIONAL ENVELOPES IS MUCH SIMPLER

D) Open the template, retype or paste, and print

Here we go.

---

THE PART YOU ONLY HAVE TO DO ONCE: CREATING THE ENVELOPE THE FIRST TIME AND FIGURING OUT HOW TO PRINT

---

A) Make the envelope document once

1. Create a new empty document. This will be your envelope.

2. Now make it look like an envelope. Choose Format > Page, Page tab. Make your window match the settings in this window, then click OK.

 

 3. This is what it should look like.

 

 
4. Type the return address if you want (skip it if you've got that already on your envelopes.)

After typing, you want to take the cursor, the entry place where you type, down and over. Click after the return address and press Enter a few times til the cursor is about as high as you'd want it to be for the regular address.

 

 5. See the top item circled on the ruler? Click and hold down on the bottom triangle and drag it about halfway over.

6. And now the cursor is where you need it to be to type the address.

 

7. Type the To address.

 

8. And now you can format it of course any way you want.

 

9. If you don't like where the To text is, click above the text and press Enter a few times to lower it or press Backspace a few times to raise it. Just the same way you'd do it in a normal doc.

 

10. And to move it left or right, select at least part of the To text and drag that bottom triangle left or right. Same way you did it before.

  

---

B) Print

Take out some cheap envelopes. You're going to need to experiment a little potentially.

1. Choose File > Print. In the Print window, click Options and in the window (this varies by printer), find the paper size and choose Envelope 10. This is the most important part of printing. You must do this or it won't work.

 

Click OK in the settings window, then specify your print options and click OK to print.

Now, it's up to you to fiddle with which way to insert the envelope, up or down or one end first or the other. That's allllll your printer.

If you end up getting the address printed 90 degrees off, then make this change to the print setup in your print window too. Print it landscape. How it looks in your printer will vary.

Click OK in the settings window, then click OK to print.

At this point it should  work.

Once you've got it working, write down all the settings you did that work.

Note: You can also choose File > Printer Settings in your document and make the same paper size and landscape/vertical settings there. And theoretically those settings are saved so you don't have to do it again each time you print. If it works for you, great. However, I've found this slightly less reliable.

---

C) Save it as a template so you never have to make it again!

Had enough formatting? Then just save it as a template so you never have to create it again. You can make it into a template by choosing File > Templates > Save, selecting the My Templates category, naming it, and clicking OK.

 

Then use the template. When you want another envelope, choose File > New > Templates and Documents, click the Templates icon in the window that appears, and open the My Templates category.

 

 Inside the category, find your envelope. Double-click it. And you'll get a new untitled envelope document just like the one you made.

 

Then just type or paste new info.

 

---

THEN ONCE YOU'RE SET UP PRINTING ADDITIONAL ENVELOPES IS MUCH SIMPLER

---

D) Open the template, retype or paste, and print

This is all you have to do once you've done all this stuff.

• When you want to print an envelope, open up that template. (Or skip the whole template-making thing and just open the old document, wherever you saved it.)
• Type or paste in new information.
• Print, being sure that the printer is set for Envelope 10 and, if necessary, Landscape.

That's all.

In OpenOffice mail merge documents, you see the field names like Name onscreen, not the data like Bob. The data appears when you print.

I just wanted to clarify something since I hate to see people thinking they're doing something wrong when they're doing it right.

Here's an example using labels.� This is how it's supposed to look. You won't see the data onscreen, you'll see the field names like first name. When you print, the correct data will appear.

If you don't know want to print all the data, you can pick the ones you want in this� window. Just choose File > Print, click yes that you want to print a form letter, and then select the records you want in this window. Select the first record, hold down Ctrl and select the next one, and so on.

Or specify a range.

Then just click OK.

Making life with labels a little easier

Labels don't always match up perfectly, especially those little return address labels.

What can you do, besides prayer and fasting, and wasting labels?

I try to just give the labels some room to make mistakes without screwing up my text, by putting some space around the label text. It's nothing all that complicated, but I hate to think of dealing with labels without it.

See also this entry, another way of tweaking labels.

First, you make your labels. Do whatever you need to do, but on the Options tab, select Synchronize contents. Then click New Document to create the label sheet.

You'll get something like this, the red arrows particularly with very small labels where the default font size, 12, doesn't fit.

Next, select the upper left label and make the font as small as you need to to fit the content on. You might need to just select a line or two at a time or select All. Apply a 6-point font or less if necessary, and I like to use a sans serif font like Nimbus Sans L or Helvetica. Now you can see all the content.

With the first label selected, find the little hourglass shaped icon on the ruler, marking the left margin.

And drag that icon to the right to give the text a little breathing room on the left.

Now that you've given it room on the left, give it room on the top. Click to the left of the first line of text in the first label and press Return.

Now with your cursor in the new blank line, type in the font size box, perhaps a 4, and press Return.

And now there's a nice scosh of blank text above the content, too.

The label is how you want, so just click that Synchronize button to make all the labels like the first one.

---

---

Printing Envelopes in OpenOffice.org Writer (Repost)

I ran into Christian Einfeldt at Linuxworld, who mentioned that he had some questions about printing  envelopes. It's a good perennial topic, so I'm rereposting.

(Originally posted December 2, 2005)

I get a lot of questions about envelopes. A lot. I wrote an article for TechTarget.com about how to do envelopes in OpenOffice.org 2.0.

I included some templates there; here are the same envelope templates for OpenOffice.org 2.0.

• Envelope template, not hooked up to a data source, using frames
• Envelope template, not hooked up to a data source, not using frames

The article is long and detailed. Here are the key points.

• You need to get to know your printer and let it know to expect envelope shapes, not letter or A4 shapes.

• You also need to fiddle around for a while and figure out where--left, right, perpendicular, parallel--in the tray your printer expects envelopes. Buy a box of cheap envelopes and expect to waste a few while you experiment.

• Then make sure that your envelope document is set up to print to the envelope size your printer is expecting. Envelope 10 is good.

• You can type in your data for the envelope or use a data source. My article goes over both.
• The simplest way is to just choose File > New > Envelope, then Format > Page Size and instead of Letter or A4, choose Envelope 10 and change the orientation to landscape.  Then either with standard margin formatting or with frames, put the text where you want.
• As another way to create the envelope document, you can also choose Insert > Envelope, and use the three-tabbed window that appears.
• Print the envelopes separately from your letter or other document that goes with the envelope. It complicates things too much with the paper size that the printer expects.

Then just print the envelope. (If you're using data sources, click Yes in the dialog box that appears asking if you want to print a form letter.)

The next time you want envelopes, just use that same document you already created, and change the addresses. Either save the document in myimportantdocuments\envelopes, or if you're a template kind of person, make it a template. (File > Templates > Save, select a category and name the template, then choose File > New > Templates and Documents and pick your envelope template.

Note: Doing it in 2.0 is quite similar to how to do it in OpenOffice.org 1.1. Here are my posts how to do that; they're excerpted from my OpenOffice.org workbooks.

• OpenOffice.org 1.x Mail Merges, Envelopes, and Labels
• OpenOffice.org 1.x Plain Labels and Envelopes

Tip on Printer Setup
Some of the pain of envelopes is the printer setup. Here's a GREAT tip from Miriam:
"I just read your envelope printing tip. Instead of constantly changing and checking the printer settings, I add another instance of the printer, configure it for envelopes and name it "envelope." When I want to print an envelope I choose this printer instead of the default one. That way my settings are always the same."

Envelope Mantra
Here's the other main point I want to make sure everyone understands.

Envelopes aren't too bad once you figure out how to do it the first time. Honest.

---

---

Printing Exactly the Records You Want to Print for a Mail Merge in OpenOffice Labels, Envelopes, or other Documents

Used to be, it was hard to just print the records you wanted to print in a mail merge, in OpenOffice.org mail merges.

Bob  Jones   121 Ludlow
Marie Hanson  1688 Oak
Kathy Bates  88 Pearl

What if you're mad at Marie, though, as well as Jean, John, and Xavier,  and just want to print a holiday letter to Bob and to Kathy and the other 119 people in your list who you're still speaking to?

You had to either go through the HUGE and complex mail merge wizard, or just print a letter each for Marie, Jean, John, and Xavier, and throw them away.

However, in 2.something of OpenOffice.org, you can just Ctrl - Select the records to print.

1. Make your mail merge document: labels, letter, envelopes, whatever.

2. Choose File > Print and say Yes, you want to print a form letter. NEVER mark the checkbox.

3. In the window that appears, you can scroll down to view the database and table you're using at the left side. This isn't necessary but it might help you feel more organized.

4. Click on the light gray box next to the first record you want to print.  Hold down Ctrl and click on the light gray box next to the next record you want to print. And so on.

You'll see that at the left side,  Selected Records is now marked.

5. Now just select Printer or File to print as you normally would, and click OK. The normal printing window will appear if you select Printer.

---