Tutorial 2

Mailmerge from an Access Database

In this tutorial you’ll setup a merge project that will fill in the name and address of each customer listed in a database, insert a date into a mailing letter and gather all personalized letters in a single printable PDF document.

The result should look something like this:

Step 1: Setup an output document

Launch variaDoc, or create a new empty project. Add an output document and set the output method to Save to disk.

Set the base path to $[PROJECTPATH]\Out and set the file name to letters.pdf.

Make sure the Create if not exists and Overwrite existing files check boxes are both checked.

Step 2: Add a data source to repeat pages

We need to have a letter for each customer listed in the database. Lets insert a data source and add the page with the letter below it.

Select the output document

in the merge tree and select Data from the Insert toolbar.

You can now type in a name for the data source. Enter the name Customers, since it will contain a list of customers. It's good practice to give data sources descriptive names regardless of project.

Select the data source type Access database.

In this tutorial we'll be using a the Northwind database, which is created with Microsoft Access. This is a sample database from a fictional company named Northwind Trading.

As soon as you select your data source type a new panel appears.

Click

to browse.

Select the Northwind database file (Nwind.mdb), located in the samples/Tutorial 2 folder.

The screen above shows the path of the database file as $[PROJECTPATH]\nwind.mdb. The PROJECTPATH parameter is an internal merge parameter you can use to make paths relative to your merge project. This helps you deploy merge projects to other computers.

Next you'll have to select a query method.

A query is used to select specific data from a data source. All non-XML data sources (Access, Excel, OLE DB etc.) use SQL statements. XML data sources use XPath to select data. Refer to SQL quick reference and XML / XPath quick reference for more information on SQL and XPath respectively.

For this data source we need to select all customers. Use the option Select a table to do this, since all customers are in a table in the database.

Select the option Select a table.

Select the table Customers from the Database tables list.

Step 3: Add the letter

Lets add the PDF Form with the newsletter to the project and fill the fields with data.

Click Pages > Template Page(s) on the Insert toolbar and use the Add Page(s) Wizard to select the mailing letter template from Samples/Tutorial 2/mailing.pdf.

Step 4: Fill the form fields

The template document contains one page with three fields. The first field, Name_Address, is a multiline text field that will hold the company name and address. The second field, Head, is the letter head containing a city and a date (e.g. “New York, July 20, 2004”). The final field, Opening is the letter opening with a personalized sentence, for example “Dear Chris Sharp,”.

Select the field named Name_Address.

Enter the following expression:

$[Customers:CompanyName]

c.o. $[Customers:ContactName]

$[Customers:Address]

$[Customers:PostalCode] $[Customers:City]

$[Customers:Country]

You can use type assist to quickly type in this expressions. Type $ and select Customers, this is the data source we have set up earlier. After the data source name type a colon (:) this will display a list of columns available in the records of the data source. You can use the preview function

to see what the result of the expressions will be.

Select the field named Head.

Enter the following expression:

Venlo, { DateTime.Now.ToLongDateString() }

This expression uses VB.NET code to generate a string that represents the current date.

Select the field named Opening.

Enter the following expression:

Dear $[Customers:ContactName],

Step 5: Run the project

Now, run the project.

Double-click on the Document sample2.pdf created message to open the document.