January 3, 2004

ODBC MySQL Mail Merge

I'm trying to figure out how I can use ODBC with MySQL so that database-driven Microsoft Office applications can work smoother (e.g., mail merge). I started by installing the latest production release of MySQL Connector/ODBC. I downloaded and executed the "Driver Installer" for Windows. I then followed along the FAQ "How do I configure Connector/ODBC DSN on Windows". I then opened up Microsoft Word, selected Tools -> Letters and Mailings -> Mail Merge Wizard. I selected Envelope. After selecting an Envelope size, use Browse to select the database. It works. Use "Connect to New Data Source" and then "ODBC DSN". Nice.

Here are more detailed instructions... starting from square one. Prerequisites are a net connection, a running MySQL server, and Microsoft Word.

  1. Download and install the MySQL Connector/ODBC driver
  2. With administrator priviledges, select Control Panel -> Adminstrative Tools -> Data Sources (ODBC)

  3. Click on Add and select "MySQL ODBC 3.51 Driver"

  4. Fill out the details on your MySQL server and then click OK

  5. Now, fire up Microsoft Word and select Tools -> Letters and Mailings -> Mail Merge Wizard
  6. Though aesthetically the correct choice is "Envelopes", the practical choice is "Labels", so choose "Labels" and click Next: Starting document
  7. Click on Label options and select the correct template (for this example I chose 5160) and go on to the next step

  8. Click Browse... to use an existing list. Use "Connect to New Data Source.odc"

  9. Select "ODBC DSN"

  10. You should see the data source created earlier, in my case, "ahcouk"

  11. Now select the desired table, e.g., the invitation labels would be stored under "guests", seating name tags under "rsvp"

  12. Finish up and you should see the contents of the selected table

  13. The Mail Merge Recipients window is very powerful, allowing you to sort and filter the records as needed by simply clicking the headers and header buttons, I suggest, however, restraining yourself and using instead the (Advanced..) option available from down arrow buttons in the header. In the example below I selected for my mail merge A-list guests in the United States with the address_1 field filled and no outstanding inquiries

  14. Fancy mail merging will be reserved for a different entry. Click on Next: Arrange your labels. The mail merge fields are available when you click More items...

  15. A label template like
    «address_2»{ IF {MERGEFIELD address_2}="" "" "¶"}«city», «state» «zip»
    should prove useful. Don't just cut and paste, the curly brackets are inserted using Ctrl-F9, and the fields using the More items... menu. Some additional logic will be needed to cope with international addresses. You can find more information on using mail merge fields here. To replicate the first label, click on Update all labels

Posted by torque at January 3, 2004 10:09 AM | TrackBack

Hello There,

I followed your example here and did not have much success.

I got Execl to talk to MySQL, but not Word (using the same odc file).

Word successfully matches Table Colums, but does not pull any data from the table.

Word says that "The connection to the recipient list has been lost. Check that the file has not been moved or deleted and that the network location is accessible."

I am using:
Word 2002
MySQL ODBC 3.51 Driver

What could be going wrong here?


Posted by: Jose at June 17, 2004 1:39 PM

Am I being naive? Why is this even a problem? What is the point of having people in a database if you can't communicate with them? I don't get why this isn't one click functionality in Word or MySQL.

Posted by: Nat at July 23, 2004 7:25 AM

I was looking for a solution just like this. This works great. Thanks!

Posted by: RickR at October 18, 2004 1:00 PM

Good example.

The only problem I had was that Word 2002 and WinXP SP1, would not accept my DSN.

In the "Data Link Properties" dialogue of the Mail Merge Wizard the "Test Connection" button failed with a "Test connection failed because of an error in initializing provier. Catastophic failure."

To resolve this matter click on the section 3, Enter the initial catalogue to use, drop down button. DO NOT SELECT ANTHING from the list just let the list appear. Click the drop down button again (or click outside of the area). This box should now be empty.

While this box was empty the "Test Connection" function would work and the OK would complete this stage.

FYI: In section 1, it automatically set for me, "Use Connection String" with the first item being "DATABASE=mydatabase;....". I'm not sure what the problem is, but the OBDC driver doesn't appear to like it if section 3 question is not blank.

Posted by: Darryl Miles at February 25, 2005 9:14 PM

I tried this out and it works perfectly. The only thing i forgot to do was allow external connections to my web server. This is going to save me a ton of time. Thx.

Posted by: David Kozarichuk at March 25, 2005 8:03 AM

I just discovered this post, and it's exactly what I needed to finally convince my boss that we should get away from spreadsheets and start using MySQL databases. Thanks so much for this solution, it's going to make my life a lot easier starting today! (Thanks also to Darryl Miles for the solution to the catastrophic failure issue, I was having that too.)

Posted by: Gator at July 23, 2005 6:52 AM

Anyone know how to create a mySQL ODBC connection using Microsoft Word on Mac OS X? It seems that the Mac version of Word only supports File Maker Pro.

Posted by: Bill Abel at July 25, 2005 1:12 PM

Problem: Values do not show up in the corresponding row on screen (13.) above.
Solution: Use empty string instead of Null, e.g. by applying the IFNULL function in your MySQL insert stmt.

Problem: MySQL Datetype Date cannot be sorted/filtered
Solution: Transform date into an 8 digit integer yyyymmdd.

Posted by: Severin at October 28, 2005 2:17 AM
Post a comment

Remember personal info?