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

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

Every advance leads to benefits and to problems.

The 9-digit zip code is great for delivery accuracy. But they aren't required. So you have mixed 5-digits and 9-digits.

Now just try sorting your address info by zip code when there's mixed 5s and 9s. Here's what you get when you sort a standard address list by zip code.  It's like cattle and sheep, they don't mix.

What do you do?

There are three things you can do:
1 - Enforce a 9-digit zip. Everyone without four digits gets -0000 whether they want it or not.
2 - Put a ' in front of every zip code. It doesn't print but it forces the zip code to think of itself as text. (This also helps with not losing leading zeroes.)
3 - Split the column into two, so you have the zip in one column and the four-digit extension in the other. This is kind of like the forcing-9-digit solution.

The explanations follow but if you want to root around in an example spreadsheet, here's a spreadsheet with options 2 and 3.

Solution 1
Solution 1 is self-explanatory.

Solution 2
Just type a '   a regular apostrophe on the keyboard, to the left of the first character of every zip code. It doesn't show but it forces text format.

See? The ' is there in the entry field but it doesn't show in the spreadsheet cell.

Typing ' into thousands of cells takes a while. So you can search and replace. There might be a better way to do this but this at least doesn't suck.

Click in the Zip Code heading in your spreadsheet, then  choose Edit > Find and Replace. Click More Options and fill out the window as shown, searching for ^0, caret zero, and replacing with '0, apostrophe zero.

You'll need to do this once for 0, then for 1, then for 2, and so on. (I've tried to figure out a faster way, plus submit any suggestions.) 

Either replace one at a time if you're cautious, or go nuts and replace all. You might want to select the whole column of zip codes, too, and select the Current Selection Only checkbox.

When you're done, and when you sort that data, the zip codes sort correctly.

Solution 3

You can split your data into two cells with the LEFT and RIGHT functions. I'm throwing in IF too because sometimes you'll want the right-hand four digits (the extension) and sometimes you'll want 0000. (Or just leave it blank, whatever you want to do.)

This is what I want to achieve.

And this is how I get it. The formula for the first column, where I extract the first five digits, is simple.

The right-hand side is a little more complicated since you're dealing with variable-length zip codes. But basically you're saying if the zip code is just five digits, then create a new 4-digit extension, "-0000" (or just "0000" depending on how you want to deal with the dash). And then if it isn't just five digits, then you want to see the right-hand five digits of the zip code (including the dash) or the right-hand four digits (if you want to leave out the dash and put it in manually somehow).

Then you just drag down those formulas to all the zip code cells.

If you want to turn those columns into normal text, just copy them, choose Edit > Paste Special, choose to NOT paste formulas, and click OK.

The pasted results are nothing but numbers.

Now when you sort, you just need to be sure to do it by two levels, first by the main zip code, then by the extension. BE SURE that you set the Ascending or Descending the same for both.

And you get your results, sorted correctly.

Here's a spreadsheet with options 2 and 3.

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.

Never retype when you can do it quicker!

In OpenOffice.org Calc spreadsheets, as in Excel, you can click on a cell, find the little black handle in the lower right corner, and drag it up, left, right, or down, to get additional content.

If it's a number when you drag you'll increment by 1. If it's a value like the days of the week or names of months, it will increment as in January February March.

If it's a formula where you've just typed it normally without absolute  references, then drag it down through a column or row, it will repeat the formula as shown. Here's the first cell with the formula:

Drag down and you get similar formulas referencing the next-door cell, not the original cell.

If it's anything else it will just repeat.

If it's a number or day/week/month (not a formula) that usually increments, and you just want it to repeat, you can:

- Select the cell containing the number, as well as all the cells that you want it to repeat into, and choose Edit > Fill > down or right or whatever fits the cells you've selected

- Or you can hold down Ctrl when you drag and it'll do the same thing: repeat instead of incrementing. This works for incrementing months/days etc and numbers.

Note: for formulas, if you want them to not adjust as you drag, you use absolute references as in Excel. $A$4 instead of A4 for instance.

I wrote this article  for TechTarget about the fabulous Web Wizard and its uses for mass PDF conversion and quick web publishing of existing documents. This is a "classic" post but it's a great feature that bears re-posting about.

You have a lot of control over how OpenOffice is set up. One of them is automatic formatting. I recommend turning off most of it. Here are two things to do that will make OOo work the way you want.

Choose Tools > Autocorrect, Options tab, and unmark everything from the second line on down. This will prevent, among other things, the numbering formatting that  starts when you don't expect it.

Then choose Tools > Options > Writer > Print and unmark the circled option. It will take out that annoying extra page you get sometimes and you don't know why . (It has to do with right-left pagination for books.)

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.

http://tllts.org/

Starting in 9 minutes! (And, I imagine, listenable afterwards as well.)

Once I installed this extension, it took me about six seconds to create a presentation based on a directory of graphics, one slide for each graphic.

http://extensions.services.openoffice.org/node/419

Download the extension, don't unzip it, then choose Tools > Extension Manager as always to add it.

Then choose Tools > Addons > Create Photo Album.

Point to the directory where your graphics are. That's it. You get a presentation.

Only thing is, the randomly chosen slide transitions are a little annoying, so click the Slide Transitions item, select No Transition (or something you like), then click Apply to All Slides. And you can use that pane also to change the amount of time on each slide, or to switch it to manually going from one slide to another.

Views and queries let you cherrypick what fields, and what data, you want to see. You can also create calculated fields to add data, and change the field names. You can then base mail merges, among other things, on what you've created.

This is an article on creating views in OpenOffice.org 2.0. Queries are pretty much the same thing with a few differences.

See also part 1 and part 2 of an article about creating databases in the database tool in OpenOffice.org 2.0, and the forms article, part 1 and part 2.

When in doubt, right-click.

The right-click context menu gives a very nice list of many of the operations you can do with the selected item. Not everything, of course, but it's a good place to start. (Click to see a bigger version.)

The awards for the Best Open Source Software are out!

The article

http://www.infoworld.com/article/08/08/04/32TC-bossies-2008_1.html

Specific winners for databases, OSes, etc.

http://weblog.infoworld.com/tcdaily/archives/2008/08/best_of_open_so_4.html

Ubuntu won for operating system.

Specific winners for productivity apps.

http://weblog.infoworld.com/tcdaily/archives/2008/08/the_infoworld_b.html

OpenOffice.org, GIMP, and Firefox won their categories as usual.

Check out the lists for other ideas for new software to try!

I realized that I don't really have all the tips for compatibility between OpenOffice.org Writer and Microsoft Word in one spot. Here we go. These aren't all of the things you could ever try but they're my classics.

• Before converting the document, see what you can do about creating it well in a way that will convert well. If you create documents well, they'll convert well.
• If people are having problems with your document, find out if they really need to edit the document at all. If they just need to print it or have it on hand, then use File > Send > Document as PDF and all formatting problems are moot. Ditto if the problem is on your end -- if you don't need to edit the document you've received, then ask for a PDF version.
• Don't overlook the idea, with legacy documents, of A) keeping a couple licenses of Word around so you don't have to bother converting 0r B) contracting out the conversion work.
• Check the page formatting. Choose Format > Page and adjust the page size, margins, and page orientation.  Make the page margins smaller  if you're having trouble fitting everything onto the right number of pages.
• If text formatting is decidedly odd, select and choose Format > Default to remove formatting and start over.
• Change the font and/or font size. Select the text and choose a different font and font size from the dropdown lists at the top left of the toolbar.
• If graphics are behaving oddly, right-click on each and choose Anchor > To Page. Then reposition the graphic. You might also want to slightly shrink the graphic so it will fit better, or increase the size.
• For lists, select the list and click the numbering or list icon to turn off all numbering or bullets. Then reapply the list or bullets by clicking the same icon again.

• Adjust the default tabs. Choose Tools > Options > OpenOffice.org Writer > General. Set the tabs slightly smaller or slightly larger and check the effect.
• Adjust the default fonts. Choose Tools > Options > OpenOffice.org Writer > Basic Fonts (Western). Specify the fonts and font sizes that fit best in your documents.

• Check the fonts that are used with your printer and operating system. You might want to set up font substitution. Choose Tools > Options > General > Fonts, and use the online help to apply the replacement table. Replace fonts you can’t use with fonts you can.
• Choose Tools > Options > OpenOffice.org Writer > Compatibility. If the printer metrics option at the top isn’t on, select it. Try changing the other settings in the window and see if those items help.

If you find that you have a set of formatting changes that works well going to Word and back again, make that your default template. Then every time you create a new Writer document, it will have those attributes.

• Create a document with the correct formatting attributes.
• Choose File > Templates > Save.
• Save the template in My Templates and call it Conversion.
• Click OK.
• Choose File > Templates > Organizer.
• In the left-hand pane, expand My Templates and select the Conversion template.
• Right-click on that template and choose  Default Template.

• If you want to go back to the normal default template later, open the Organizer again, right click, and choose Reset Default Template > Text Document.

I occasionally mention, in class, that you can apply styles with the keyboard too. However, I'm a menu user by nature so I usually forget to emphasize it enough. Here are the steps for setting up styles so you can apply them with a keyboard shortcut.

1. Choose Tools > Customize.
2. Select the Keyboard tab.
3. Select the shortcut key to assign from the list of shortcut keys.
4. Select Style from the Category list.
   Scroll down to the Styles entry and click the + sign to show the style types of OOo.
5. Select the style to assign from the Function list.
6. Click OK.

Note: This is part of a three-part post on styles. See this post for the intro and links to the other posts.

Why should you use styles?

Lots and lots of reasons.

Styles Mean You Do the Formatting Once, Then Apply It Quickly Each Additional Time

Styles aren't so necessary for your short scathing memo to your intern, but anything longer and more complex will be a lot easier to format with styles.

Let's say you've got a 100-page white paper on how your company's flotsam server works. You've got these types of things in the document:

• Four levels of headings in various sizes and the same font (Arial)
• Regular body text in 12 point type and Times New Roman font
• Notes in bold, 10-point Arial, indented a half inch from the left
• Warnings bold and italic 10-point Arial, indented a half inch from the left
• Numbered lists with 1 at the top level, A at the 2nd level, and a special purple bullet at the third level

A cover page with no footer, introductory text that should be numbered in roman numerals, main body text with arabic numbers in the footer and the document title in the header, and two pages with big diagrams that need to be landscape (horizontal)

Do you really want to painfully do the formatting of every heading, every bulleted list, ever note and warning, every page, manually each time you have a new one?

You really don't.

With styles, you just do the formatting for each heading and each other formatted element of the document once, and then you just select the text and select the style each time you want to apply the formatting. Instead of selecting the text, clicking the Bold icon, clicking the Italic icon, selecting the font size, selecting the font, indenting the text....and so on for every freakin' individual chunk of differently formatted text.

Styles Mean You Can Update a Long Document's Formatting Easily

Let's say you wrote that 100-page white paper and formatted it manually. There's a layoff and you have a new manager who tells you that your paper needs to conform to the corporate marketing formatting standards. Which are completely different.

If you formatted it manually, you have days of work ahead of you.

If you formatted it with styles, you just need to either import a template from the corporate marketing group and be done with it. That's the best case scenario. If your marketing group isn't that organized, then you just need to update about 15 styles. That's all. When you update the styles, all the text says to itself “Oh, I'm Heading3, and Heading3 has changed. I'd better change too.”

Styles Are Required for a Lot of Essential Features in OpenOffice.org.

Do you want to create a table of contents? Do some fancy formatting in a table of contents or list? Do running headers? Do conditional formatting in a spreadsheet? You're going to need styles.

I blogged about this item recently.

http://openoffice.blogs.com/openoffice/2008/03/loss-aversion-a.html

It's about how people are more inclined to fear loss than to be motivated by gain. (And also about how a cheap placebo is less effective than an expensive placebo.)

That was pretty depressing because it seemed like people are hard-coded to not be interested in Openoffice.org (free, and gaining money in the budget to do other things with), when they could clearly benefit from switching from MS Office.

(Caveat. Of course, not everyone should switch from MS Office to OpenOffice.org, but pretty much everyone should consider it.)

However. I'm listening to NPR again and here's the flip side.

http://www.npr.org/templates/story/story.php?storyId=93872977

People are motivated by fear, by loss. Not just to buy a certain brand of deoderant but it just works. Firefighters who during training are shown  or told about the wrong decisions by previous firefighters, ended up performing better than firefighters who were just shown the right decision-making process. Mothers who were told that formula was bad for their babies were more likely to breastfeed than mothers who were told that breastfeeding was good for their babies.

Microsoft certainly does this but without as much emphasis on truth/the whole truth/and nothing but the truth as one might hope.

And when you think about it, it makes sense. Why bother to get up off the chair that's on fire if all you're told is that it's cooler over there on the other side of the room? "You're going to die" is the key information.

So that's one major thing. Emphasize the danger, the disadvantages, of the current choice.

The next major thing I took away from this NPR show is that it's all about "what is everyone else doing." Which is not surprising, but it's very effective. You know the sign you see in hotels, saying please leave your towel on the rack if you want to reuse it. The sign says we should save hot water, save the environment, etc.  Hotels in a study increased their towel reuse by guests significantly simply by changing the sign so that it says that 43% (or so) of hotel guests reuse their towels. People look to their peers for approval and guidance of what to do.

Here's a by no means complete but useful list of many implementations of OpenOffice.org. And let's not forget that Sun, Novell, and IBM all have heavy involvement with OpenOffice.org/StarOffice/Symphony.

http://wiki.services.openoffice.org/wiki/Major_OpenOffice.org_Deployments   Plus my home town library in Kalispell, Montana; the library uses Userful kiosks. Not a major deployment ;>  but it's another stat.

Once you've done the first two things, then of course you need reasons for switching to OpenOffice, or whatever you're trying to explain. And we have those in spades for OOo.

This PDF is from my Calc workbooks, and covers Data Pilots, Calc's version of pivot tables.

You'll also need this lab file.

See also this post.

This PDF is from my Calc workbooks, and covers Data Pilots, Calc's version of pivot tables.

You'll also need this lab file.

See also this post.

I'm back home, and ready to ship books!

See this URL.

http://openoffice.blogs.com/bookresources/2008/04/ordering-the-op.html

I'm back home, and ready to ship books!

See this URL.

http://openoffice.blogs.com/bookresources/2008/04/ordering-the-op.html