Mail Merge OpenOffice Labels, OpenOffice.org 2.0 (Repost)

Here's how to do labels.

First, get your data in a spreadsheet, text file, address book, and create a database. You can do this by choosing File > New > Database. 

This post contains instructions for spreadsheets.

This post contains instructions in step 2 for spreadsheets or text files.

Then, once you've got the database set up in OpenOffice.org, you're ready to go.

1. Choose File > New > Labels. (To do Envelopes, open an OpenOffice.org Writer document, and choose Insert > Envelope.)

2. In the Labels tab of the Labels window, select the database  you created in the Database dropdown list. You're looking for the name of the database you created by choosing File > New > Database, not the spreadheet, addressbook, or text file containing the data.

(Click the picture to see a larger image.)

3. Select a table from the Table list. This will be Sheet1 or whatever the sheet name is, if you are using a spreadsheet to hold your data.

4. Select the first field you want to use from the Database Field list.

5. Click the arrow next to it to insert it in the Label Text field.

Type a space after the field and you can add the next field, such as LastName.

6. Use the Database Field list to insert any other fields you need. If you want fields on the second line, click after the last character of the last field you inserted, in the big text box, and just press Enter. If you need to change the arrangement later in the created label document, you can.

7. In the Brand dropdown list, select Avery Letter Size if you're not using A4.

8. In the Type dropdown list, scroll through the billions of labels. Select the type of label you're using, 8160 Inkjet Address is a good one but just use whatever is on the envelope of labels.

9. This step and step 10 , and step 15, are optional but recommended. Click the Options tab.

10. Select the Synchronize Contents option IF you want to apply formatting, like a different font or colors or adding graphics, and make those changes apply to all of your labels.

11.Click the Setup button next to the printer display.

12. Select the printer you want to print to.

13. In the printer options (this will vary according to your operating system) specify the appropriate paper feed or tray. For now, select Manual Feed.

14. Click the New Document button at the bottom of the window. The labels will appear. This is how it's supposed to look. You won't see the data, you'll see the field names like first name. When you print, the correct data will appear.

15. OPTIONAL: IF YOU SELECTED THE SYNCHRONIZE CONTENTS CHECKBOX

If you need to make changes, like adding spaces, rearranging fields, or changing formatting, do so in the upper left label. Make the text an interesting font, or make it the size you need. You can also right-click on the border of the upper right label, choose Frame, go to the Border tab, and give it a background color.

Then click Synchronize to apply those changes to all labels.

16. Choose File > Print. A message will ask if you want to print a form letter. Click Yes.

17. If you want to print labels for only certain records, you can select them in the scrolling list of records. Select one, press Ctrl, select the next, and so on. Or you can select a range of records like 1-20.

18. If you want to just print all the labels, choose All.

19. When you're ready, just click OK. You'll be prompted again to choose your printer. Print normally.

Note: If you have any problems printing, check your printer setup using your operating system setup tools.

Note: You can save the label document and just go back to it again when you need to use it again.

---

---

Comments

[Robert Jung]

ok, I now get how to do labels. And I think I understand the link to the database. But, what I don't get is the how to sync the data, which is on a spreadsheet, into the database. I can initially create that link. But let's say, I need to modify the spreadsheet, keeping all of the fields. Just the data that I'm merging has changed. How do I tell the data source to sync up to the latest version of the spreadsheet. It seems that it should be automatic, given the connection, but I don't see it updating.

thanks,
Rob

[ben dalton]

Rob,

I created a database (from File>New...) from a spread sheet I have already filled with info.

Like you say, I didn't think it was updating when I made changes. But when I closed the database, and then re-opened it, and double clicked on one of the tables (e.g. "Sheet1"), the changes had gone through.

If this isn't working for you, try right clicking on a table in your database (e.g. "Sheet1") and choose Database > Connection Type. Then 'Spreadsheet' from the drop down menu. Then select your spread sheet, and click 'Test Connection'.

[Steve Kinney]

Great start - it all works as advertised. Only one little problem here: It seems impossible to get Open Office to populate more than one page (sheet of labels) at a time. For instance, if you are printing 300 addresses on labels that come 6 per page, it seems necessary to re-populate the ONE page 50 times by hand to do it.

I must be missing something. My co-worker in the cubible next door, who originally found the problem, must be missing the same thing. Any clues? (Open Office 2.1 on WinXP.)

[Dr Phil]

Been struggling with the "only one sheet" problem my self. THe trick is in this bit:

>>Choose File > Print.
>>A message will ask if you want to print a form letter. Click Yes.

I don't think it will play if you just hit the "print it now" button...

[deedhoet]

One problem:
It's not vertically centered. How? How to delete the second row too? It's useless!

And I think it will be much better if we can see the record value than seeing

,
As if we click the "Data to Fields" button from Data Source View. It's disabled if I dont do the 15th step. Is it bug or something else?

I'm using OOo v 2.0 for Windows.

[deedhoet]

Another question: how to show the "Mail Merge" window? Mine isn't looking like that, there is no "Record" and "Output" dialog.

Thx in advance.

[Ravan Asteris]

I figured this part out already, BUT
A) How do you get it to do other than default fonts without having to twiddle every label? and
B) How do you get it to put the Post Office bar code on the label. If you're bulk mailing, this is importanr!

[Adam]

This step in the process does not occur for me:

>>Choose File > Print.

>>A message will ask if you want to print a form letter. Click Yes.

Not that it does not work, I never get the message. I choose "print" from the file menu. The "print" dialog box appears, I click print, and it prints (and not the information I desire either).

I am running OO 2.0, have even reinstalled it, and nothing. I have spent hours upon hours trying to get it to print 700+ labels from a DB. I cannot find anything related to a "form letter" when dealing with labels in Writer.

My DB is fine, it is connected to the open document in Writer and all the other steps go smoothly other than:

>>A message will ask if you want to print a form letter. Click Yes.

Which as said before, does not happen.

Any help would be greatly appreciated, as I am going crazy trying to get this to work, and have no other program to print labels :(

[Solveig]

Hi Adam,

See this blog entry.
http://openoffice.blogs.com/openoffice/2006/10/how_to_get_the_.html

I couldn't find the file but it seems to work for everyone else.

Solveig

[adam]

Thanks SOOOO much Solveig! You saved me :) That blog entry covered my problem. The config for the message pop-up was set to "false". Changed it to "true", saved the file and was in business. Thanks again!!

[Solveig]

Glad it worked! That's one of the settings that I think should be under Tools > Options, at least, or off of the Print Options button, not buried deep in the bowels of the XML.

Solveig

[Arthur]

All my address dbs are as tables in Word (having installed Open Office only recently when I upgraded to Vista). Is there a way I can get a link to them to produce labels -or do I have the chore of transferring all the information to Open Office? CsZ4u2

[Solveig]

Hi Arthur.

Open the .doc files in Writer. Copy the table contents, create a new spreadsheet, and paste. Then create a database based on that new spreadsheet, and you can connect to that database to create labels.

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

Solveig

[Arthur (again!)]

Solveig - many thanks. I created the spreadsheet datablase with ease, but trying to register it produces the message that the file does not exist. What am I doing wrong?

[Solveig]

Hi Arthur,

Just be sure the spreadsheet is the right format (spreadsheet not word), that the data is headings in the first row and data in the 2nd and subsequent rows, and be sure the file is closed.

By trying to register, you mean saving in the final window of the File > New > Database wizard?

solveig

[Arthur (yet again!)]

Solveig - The spreadsheet database is filed in OpenOffice.org Calc and formatted the way you describe. I used 'HELP Registering and Deleting a Database' to register the file so that I could access its contents for producing labels (but I can't).

Another conundrum: If I enter an Avery Label type (8160) should it automatically format?

Thanks again

[Suse]

Solveigh, thanks for your helpful page!

Now I've got another problem: I want to print labels using 436 records, so I check all records in the dialog box, but OO decides to leave out records (always the same) and I end up with only 156 labels (13 sheets with 12 labels each). I also tried 1 to 436 records in the dialog box - the result is the same.

Any idea? Writing all the adress labels for the shipping of the christmas parcels wouldn't be so funny *sheepish smile*

[Suse]

Found the solution: the field "next record" was inserted more than once!

[Solveig]

HOW many times have I done that? Yep, that's a stickler. I have modified my approach so that I make the font for that tiny tiny gray box nice and big, so it's easier to see doubles.

Glad you found the solution!

Solveig

[Arthur (yet again!)]

Solveig - any suggestions for Arthur (yet again!) message of November 14. Like Suse, I do not relish the prospect of writing out 100s of Christmas address labels.

[Solveig]

Hi Arthur,

I'm not sure what you mean by automatically format, I'm afraid.

I think there must be something wrong with the spreadsheet. Copy and paste the content into a news spreadsheet and save it in .ods, then create a new database, be sure to specify that it's of type spreadsheet, and leave the checkbox in the last window, saying you want to register the database, marked. That should work.

Solveig

[Arthur (yet again!)]

S - I appreciate - and admire - your patience! I have again done as you said, but in 'Labels' I still cannot get Sheet 1 to show in the Table window, nor the address fields in Database. In 'Database' after creating the neww database where do I specify it is a spreadhseet, as I cannot see such an option in the the Database Wizard?

Arthur

[Ian Davidson]

Done all that, labels printing OK from databse file.
Is there a quick and easy way to get it not to print empty fields?
thanks,
ian

[Ian Davidson]

Done all that, labels printing OK from databse file.
Is there a quick and easy way to get it not to print empty fields?
thanks,
ian

[Solveig]

Hi Ian,

Yep!

http://openoffice.blogs.com/openoffice/2007/05/suppressing_bla.html

Solveig