How to Use Customer and Outside Lab Lists in a MS Word Mail Merge

Summary

The information in this article applies to LIMS 2.x, Excel 97 or newer, and Word 97 or newer.

Since Microsoft Word can use an Excel file as a mail merge data source and LIMS can export report data to Excel, Word mail merges can be performed with data maintained in the LIMS. This article describes the steps necessary to perform mail merges using LIMS customer and outside laboratory lists.

More Information

Using Microsoft Word you can easily create form letters, envelopes, and mailing labels by merging a main document with a suitable data source. The main document contains the text that remains the same and the data source contains the information such as names and addresses that varies for each version of the form letter, envelope, or mailing label. By exporting the data behind the LIMS’ customers or outside laboratories reports to Excel, the saved Excel workbook can be used as the data source in a Word mail merge.

Create the Mail Merge Data Source

To follow the instructions below you will need a recent version of file LIMS Export Template.xlt, which you can download here. This is a minimal export template that contains only the LIMSData worksheet and empty BeforeTransferFromLIMS and AfterTransferFromLIMS macros. NOTE: If you use an older version of the template that contains additional worksheets, simply delete the extra sheets so only the LIMSData sheet remains before saving your Excel data source. To create the Excel data source:

1. Start LIMS and open the Workstation Configuration screen on the Admin menu. Verify that the Export field under the MS Excel Template Folders includes a valid folder name and use Windows Explorer to make sure file LIMS Export Template.xlt exists in this folder. If the Export field is blank use the [Browse] button to select your folder [C:\Program Files\…\LIMS\Example Files], which should contain the template file.
2. Select either the Customers or Laboratories report on the LIMS Setup menu to preview the report.
3. Click the “Export to MS Excel Template” toolbar button, select the LIMS Export Template and click the [OK] button to export the report’s data to Excel.
4. In Excel use File | Save then select a folder and enter a workbook name such as Customer List.xls or Laboratory List.xls.
5. Exit Excel.

Create the Mail Merge Main Document

Start Microsoft Word and open or create your form letter, envelope, or mailing label. Now use Tools | Mail Merge to open the mail merge helper. Using Step One of the mail merge helper screen, click Create then select the document type and select Active Window to use the current document. For Step Two, click Get Data | Open Data Source and select the Excel workbook you created above for your data source. Select the default Entire Spreadsheet option at the prompt for a named or cell range. Click the Edit Main Document button to return to your main document.

In your main document, use the Insert Merge Field dropdown list on the toolbar to add LIMS Customer or Laboratory fields to your document. For example, insert the following Customer fields into a form letter to display the billing address:

«Contact»
«Name»
«BillingAddressLine1»
«BillingAddressLine2»
«BillingCity», «BillingState»  «BillingZipCode»
«BillingCountry»

Merge the Main Document and the Data Source

Save your main document then click Tools | Mail Merge. Using Step Three of the mail merge helper screen, click the Merge button to merge with all data source records or use the Query Options button to specify filtering and sorting options for the data source records.

For example, if you use the Inactive check box in LIMS’ Customer setup screen to inactivate old customers, you can exclude inactives in the merge by building an “Inactive Equals False” query option on the Filter Records tab of the Query Options screen. To query all customers with a Name beginning with “A” use “Name greater than A AND Name less than B”. Use the Sort Records tab of the Query Options screen to sort the records.

To ensure that your mail merges use current LIMS data, prior to performing the merge you should export the customer or laboratory list report to the LIMS Export Template then save the resulting Excel workbook overwriting the previous file (see “Create the Mail Merge Data Source” above). You can name the workbook anything you like and save it in any folder. The trick here is to always save the file with the same name and in the same folder, since Word saves the name of the data source file with the main document. Open the form letter, envelope, or mailing label document, and simply initiate the merge, and Word knows where to find the customer or laboratory data.

Add Custom Fields to Your Data Source

In the Excel workbook saved under “Create the Mail Merge Data Source” above, each field in the LIMS database’s Customer or Laboratory table results in one labeled column in the workbook’s LIMSData sheet. As we saw above, any of these fields can be used as a mail merge field in the main document and the fields can also be used to filter or sort data source records. If you are using LIMS version 2.3.0 or newer, you can use the Notes field in the Customer and Laboratory setup screens to define your own mail merge fields using the syntax Token=Value; where Token is the name of your field and Value is the field’s data.

In the following examples, the Salutation field is added to improve the appearance of form letters and the MailFrequency field will be used to filter data source records.

Salutation=Dear Ms. Smith;
MailFrequency=Quarterly;

Salutation=To Whom This May Concern;
MailFrequency=Annually;

Note that each Token and Value pair must be terminated by a semicolon (;) and should be placed at the beginning of the Notes field. The examples above place each pair on separate lines but they may appear on a single line. When other notes exist or will be added to the Notes field, they should follow the Token and Value pairs. For example:

Salutation=Dear Ms. Smith;
MailFrequency=Quarterly;

These are the customer's notes.

To add the Salutation and MailFrequency fields in our example to the mail merge data source, LIMS must recognize the Tokens and parse the Notes field accordingly. This is accomplished by updating the Structured Query Language (SQL) statement used to retrieve the data for the Customers or Laboratories report. Use the Options tab on LIMS’ System Configuration screen and add a new record to configure the report. Using our example above, the following record will configure the Customers report (rptCustomers) to recognize our Salutation and MailFrequency fields.

Option:

rptCustomers.RecordSource

Data:

SELECT *, 
GetTokenValue([Notes] & "", "Salutation") AS Salutation, 
GetTokenValue([Notes] & "", "MailFrequency") AS MailFrequency 
FROM Customer;

The following System Configuration screen Options record will configure the Laboratories report in the same manner.

Option:

rptLaboratories.RecordSource

Data:

SELECT *, 
GetTokenValue([Notes] & "", "Salutation") AS Salutation,
GetTokenValue([Notes] & "", "MailFrequency") AS MailFrequency 
FROM Laboratory;

Leave the screen’s Value field blank and use the Inactive field to disable the configuration record and restore the report’s default data source. Note that your field (i.e. Token) names must not conflict with existing Customer or Laboratory table field names.

With the new Salutation field our earlier example of a form letter main document can now be updated with the new merge field:

«Contact»
«Name»
«BillingAddressLine1»
«BillingAddressLine2»
«BillingCity», «BillingState»  «BillingZipCode»
«BillingCountry»



«Salutation»:

When the record filtering options available on Word’s Query Options screen are inadequate to properly filter the records for your criteria, an SQL Where clause can be added to the SQL statements above to temporarily filter the records exported to Excel.