Mail Merge

The basic idea with mail merge is to use special fields and replace these with data from an external source, such as the Address Book, a file or a database. Typically this is done by adding placeholders to a document and then running a utility which inserts and substitutes the placeholders with the records form the data source. Typically this is used to insert a customers name and address on a common letter sent to a mailing list.

Mail Merge with Open Office

First you need to configure your database. If you want to use the embedded H2 database you will need and Address Book Server release from after 15th December 2009 as previous releases do not support remote connection to H2. You can however choose any of the other supported database such as Microsoft SQL Server, MySQL or PostgreSQL instead of H2, all of which offer remote connections via JDBC and ODBC. I am going to cover using JDBC as this is generic across all platforms supported by Open Office, including Windows, Linux and OS X.

If you only need to support Mac OS X in your environment you can also use the Address Book DataSource.  http://neowiki.neooffice.org/index.php/Accessing_the_Mac_OS_X_Address_Book

Enable remote access to your H2 database. This is done by adding / enabling the following line to your setenv.sh or setenv.bat file in the bin folder

JAVA_OPTS="$JAVA_OPTS -Dcom.addressbookserver.ENABLE_H2_REMOTE=true"

In this case you should also configure access credential for your database. The default credentials are set in the conf/server.xml file and are used during the initial creation of your database. If you already have a database, please delete it's files or reconfigure secure access. The default installation does not require password authentication as only local connection are allowed to the database, however since you have enabled remote access you should secure access by a password and possibly a firewall as well.

<Resource name="jdbc/AddressBookDB" auth="Container" type="javax.sql.DataSource"
             maxActive="25" maxIdle="5" maxWait="-1"
             username="sa" password="PASSWORD" driverClassName="org.h2.Driver"
             url="jdbc:h2:tcp://localhost:48081/../db/AddressBookDB;LOCK_TIMEOUT=60000"/>
          <Realm className="org.apache.catalina.realm.JDBCRealm"
            driverName="org.h2.Driver"
            connectionURL="jdbc:h2:tcp://localhost:48081/../db/AddressBookDB;LOCK_TIMEOUT=60000"
            connectionName="sa" connectionPassword="PASSWORD"
            userTable="WEBUSERS" userNameCol="USERID" userCredCol="PASSWORD"                                                                                                                             
            userRoleTable="WEBROLES" roleNameCol="ACCESSROLE"/>

Now you can start your Address Book Server. During the startup / login process a new database will be created and is now accessible via the following JDBC URL:

jdbc:h2:tcp://[YOUR SERVERS IP ADDRESS OR HOSTNAME]:48081/../db/AddressBookDB

Configure Open Office with JDBC Driver

You need to configure Open Office to use the proper !JDBC driver to be able to access your database. In case of H2 the driver can be added to the Open Office class path in Options -> Java.

Select a JavaVM and then add the JDBC driver jar to your classpath via the Add Archive button
Example path for H2 JDBC Driver on Windows

C:\addressBookServerEnterpriseEdition\apache-tomcat-6.0.24\lib\h2*.jar

Open Office is now enabled to access your database, however it still needs to be configured. The following steps will guide you along this process.

Step 1 : Create Database

Create a new database

Step 2 : Select JDBC

Select JDBC as to connect to your existing database

Step 3 : JDBC Connection

Configure your JDBC connection using your the address from earlier and your credentials as defined in server.xml

Step 4 : JDBC Driver Test

Test that your JDBC Driver is available. If you driver cannot be loaded, perform the JDBC configuration again, and restart Open Office.

Step 5 : JDBC Username

Set your JDBC username to the username as set in server.xml previously.

Step 6 : JDBC Password

Set your JDBC password to the password as set in server.xml previously.

Step 7 : Test JDBC Connection

Test to ensure Open Office is able to connect via JDBC to your database.

Step 8 : Register database

Register the database and press Finish to complete the process.

Step 9 : Save

Save your database giving it a suitable name.

Step 10 : Create Address View

The default schema stored contacts and their addresses in separate tables. For mail merge however we need a consolidated view across both tables. Fortunately Open Office supports creating views based on SQL statements. Create a new view using the following SQL statement.

Step 11 : Create Address View

Ensure you select the Native SQL mode button in the tools menu and then cut & paste the following SQL statement into the text area.

SQL Statement

SELECT CONTACTS.TITLE, CONTACTS.FIRSTNAME, CONTACTS.LASTNAME, CONTACTS.COMPANYNAME, STREETADDRESSES.TYPE, 
STREETADDRESSES.STREET,STREETADDRESSES.CITY,STREETADDRESSES.STATE,STREETADDRESSES.COUNTRY,STREETADDRESSES.POSTALCODE 
FROM STREETADDRESSES 
JOIN CONTACTS ON CONTACTS.IDENTIFIER=STREETADDRESSES.CONTACTIDENTIFIER
ORDER BY CONTACTS.FIRSTNAME, CONTACTS.LASTNAME

You can verify and execute the statement via the little green check box icon in the tool bar. You should now see the consolidated data from both Contacts and Address table.

Step 12 : Save Query

Save the query under a suitable description

Step 13 : Create document

Create a new Open Office Write document and insert some mail merge fields. Use the following menu option:

Step 14 : Insert fields

Select Mail Merge as type, scroll to your view, past all the tables listed and select the fields for which you would like to insert place holders into your document

Step 15 : Mail Merge Wizard

Once all placeholders have been placed inside your document you can run the Mail Merge Wizard via the following menu option.

Step 16 : Document Selection

Select the current document, or an existing file which contain the appropriate placeholders

Step 17 : Document Type

Select Letter or email depending on the type of document you would like to produce.

Step 18 : Address Block

If required you can insert an Address Block.

Step 19 : Map Address Block fields

If you have selected for an Address Block to be inserted, you need to map the address fields to your data columns.

Step 20 : Set salutation

If you like you can create a salutation.

Step 21 : Positioning

Position your Address Block and Salutation within your document

Step 22 : Mail Merge in Process

Step 23 : Save Result

You can either save each merged document individually, or combine them into a single file.

Step 24 : Merge Result

Mail Merge with Pages

Pages integrates directly with the OS X Address Book. Please have a look at the help pages for Pages for detailed instructions

Mail Merge with Micosoft Office Word

The instructions for Microsoft Office are technically very similar to Open Office. You need the ODBC driver for your choice of databases. Unlike Open Office JDBC connection are not supported, which means you can't use the default H2 database engine. However you can use Microsoft SQL Server, MySQL or PostgreSQL all of which are supported by Address Book Server and offer ODBC access. Once you have the driver installed you need to configure a data source and then use Microsoft Office Word to use this data source in the mail merge process. If you require more detailed instruction please refer to the Microsoft Office Help documentation or the Address Book Server forum