OpenOffice.org Training, Tips, and Ideas

How to get the "Do you want to print a form letter" checkbox back in OpenOffice.org Writer

Click here!

How to generate an email address from firstname and lastname

Let's say that you work at one of those companies that has a nice leftover-from-the-8-character-file-name-limit-days email format. Solveig Haugland becomes shauglan, Bob Nelson becomes bnelson, and so on.

And let's also say, as long as we're dreaming up situations, that you've got a spreadsheet full of first and last names but you don't have their emails.

You could type in all the email addresses but even an intern who's paid well by the hour is going to balk at that.

The faster way is to use CONCATENATE and LEFT.

You've got this.

Firstname	Lastname	Email
Shelly	Nelson
Bill	Mizrahi
Steve	Santos

You want this.

Firstname	Lastname	Email
Shelly	Nelson	snelson@company.com
Bill	Mizrahi	bmizrahi@company.com
Steve	Santos	ssantos@company.com

Here's how to get it.

Syntax

=CONCATENATE(LEFT(cell_with_firstname;1);LEFT(cell_with_lastname;7);"@yourdomain")

Example

Doing an email mail merge without most of the overhead of the OpenOffice Mail Merge wizard

I'm afraid the Tools > Mail Merge Wizard has never been one of my favorites. Too complicated.

I always train people to just "roll their own" when making a mail merge document.

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

Everything you can do in the mail merge wizard, you can pretty much do in the roll-your-own approach, especially since you can now print all documents to a single file and then open that and customize it as you like, before printing on paper.

Printing

and viewing/editing the file output. (Click to see the bigger version; you'll see different values in the two letters.)

BUT

One thing you can't do in the roll-your-own approach is do an email mail merge.

Do you really want to go through the complexity and muscle aches of using the Mail Merge Wizard? No. And you don't have to. You're going to mix and match.

Step 1. Create your email the way you want it with the roll-your-own approach.

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

Save it. Keep it open.

Step 2: Set up email configuration. In Writer, choose Tools > Options > OpenOffice.org Writer > Email. This setup worked for me. Key settings are smtp.comcast.net and port 587. You just need to do this once. If you have security on your email, like requiring a password to send, you'll have to click the Server Authentication button and enter additional information.

Step 3: Choose Tools > Mail Merge Wizard. Choose Current Document, or else browse to your document, and click Next.

Choose Email and click Next.

Select the database you're using and the table. Click OK and click Next.

Keep clicking next til you're here. Fill it in by selecting the field from the database that has the emails in it, and anything else you want.  Click Send Documents.

You'll see the progress window (in this test I only sent two emails).

 

And you're done!

The received email looks like this. Note that I sent it in email format, but with the extra carriage returns I put between the lines in Writer, it looks a little spacey here. You'll want to experiment with and adjust how you format the original Writer documents and what format you send in.

Cell formats, and using NOW() and other functions in OpenOffice Calc spreadsheets that you use in databases and mail merges

Mail merges work fine with spreadsheets as the data source. You create a spreadsheet of data, then choose File > New > Database, specify connecting to an existing database, Spreadsheet as the type, then click Next and point to the database itself.

Here are a couple things about formats, though.

Formats don't come through. So if you want your Salary field to have dollar signs, decimals, etc. you need to set the format. Likewise with date or time.

One way to do this is just set the formats when you view them. Press F4 and expand to view the query or table you want. Then just right-click on the column heading and choose Column Format.

Then in the window that appears you can select a category (Currency, Date, Number) and the the specific format you want. Click OK.

You can do the same thing in the database file itself. Open the .odb file, click the Tables or the Queries icon at the left, then double-click on the particular table (the sheet or query).

In the window that appears, right-click on the column heading, choose Column Format as before...

and in the window that appears, as before, make the choice you want. Click OK.

Now, here's a related topic. Can you use the =NOW() function in a spreadsheet and have it interpreted correctly in the database and in mail merges? (Thanks to a Colorado Springs LUG member for this idea.) The answer is Yes.

Here's a spreadsheet with =NOW() showing the absolutely current time and date.

Here's what it looks like, with nothing done to it, in the database view. It needs some tender loving formatting since it's just showing the internal numeric value. Right-click on the column heading and choose Column Format.

 

Set the format you want. The NOW() function can let you use a date or time format since it contains both.

 

And now it looks fine in the database view.

 

When I use this field in a mail merge....

Here's the output when I format the field as a date:

 

and when I format it as a time.

Suppressing empty fields (and the lines they're on) in OpenOffice.org labels, or any mail merge document

Note: This is a repost but useful. It's important to follow the steps exactly. Everything is case sensitive. Also when you type the two "" quotes, don't put a space between them. If you have spaces in your database, this won't work. Either change the database field names, or create a query based on the table, and change the field names in the query. Then base the labels on the query.

Here it is -- suppressing a blank Address2 field in your mail merges. It's not extremely simple, but it's reasonably straightforward and it works.

Here's the situation we're addressing. Sometimes your addresses have two lines for the address part, sometimes they don't.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave
Broomfield, CO 82211

But you have to put in the <Address2> field for everybody, since it's a mail merge. The setup has to be the same.

<Firstname> <Lastname>
<Address1>
<Address2>
<City>, <State>, <Zip>

But with this approach, your addresses look like this.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave

Broomfield, CO 82211

Ick. How do you suppress that second Address2 line and the corresponding carriage return if there's no content for a particular record, for that Addres2 field?

Select the Address2 field in your mail merge document, choose Insert > Section, and create a conditionally hidden section with this formula.

databasename.tablename.fieldname EQ ""

Here are the details, using an example of labels.

1. Create the labels for mail merge as usual. File > New > Labels, select your database and tables, insert the fields, etc.

2. Choose the Synchronize Contents checkbox.

3. Click New Document.

4. Here are the labels.

5. Turn on nonprinting characters if they're not on already.

6. Select the first soft return, shown selected.

7. Press Return or Enter to replace it with a hard return.

8. Repeat, to make them all hard returns.

9. Click Synchronize to update the other labels to be the same.

10. Select the Address2 field.

11. Choose Insert > Section.

12. Name the section Suppress. Select the Hide checkbox and type the following condition. The screen shot shows the syntax.

Syntax
databasename.tablename.fieldname EQ "" � �(the last part is two double quotes together)

Example
databasewithtwoaddresslines.Table1.Address2 EQ ""

NOTE: if you are using the Thunderbird address book as a data source, you need to use square brackets if the field name includes a space (i. e.: [Address 2]=="") to hide the second line of the address if it the Address 2 field is blank.)� I would suggest in general avoiding field, table, or database names with spaces.

http://www.oooforum.org/forum/viewtopic.phtml?t=43528&highlight=

Click the screen shot to see it bigger. It shows the syntax, not an actual example.

13. Click Insert.

14. Click Synchronize.

15. Now preview the data or print the data and you'll see that it prints correctly.

15. If you need to change the section, select it in the first address and choose Format > Section. Select the one named Suppress for the master label and make changes, then click OK. Click Synchronize again in the labels.

---

---

Two ways to preview your data in an OpenOffice Writer mail merge

There isn't a preview button per se but there are two ways to see the actual data, rather than the mail merge fields, in a mail merge.

Here's a PDF from my workbooks with two approaches. One is just printing several or all of the records to one OpenOffice Writer file, then opening that up to look. The other is to click a button in the database view area, which lets you see just one page of records (one, for a mail merge letter or an envelope, or a whole label sheet full for labels).

The PDF starts out with standard mail merge printing instructions, and the two preview techniques are at the end.

A useful little hack for mail merges and addresses, in OpenOffice.org

When you set up labels, you set them up like this.

Firstname   Lastname
Address1
Address2
City, State  PostalCode

All of those are mail merge fields.....EXCEPT the comma.

So when you print out 17 labels on your 30-label sheet, you get a comma on EVERY label in the sheet. This is annoying.

To get around this, add a field in your data source, like your spreadsheet, that's just a comma. Make a new column anywhere, call it Comma, and type a comma in every row or record.

Then instead of typing the comma, just insert the Comma field.

If you use Linux, you MUST try gLabels

Download it now, use it now.

http://glabels.sourceforge.net/

This is a beautiful program, a well-balanced combination of power, simplicity, good design, and ease of use. Thanks to Keith for pointing it out to me.

There's no Windows or Mac version, sadly.

Among the things you can do are:
- automatically (no effort on your party) suppress empty address lines
- do bar codes
- point straight to a CSV or similar format file to bring in records
- deselect records you don't want to print
- add graphics and drawing shapes
- apply formatting
- easily preview the whole sheet

Here's a screen shot with a summary of what you do. I'll do more detailed instructions later but here's the quick info. I love it.