How To Mail Merge From Collect! To Word

Easily print mailing labels in MS Word using data from your Collect! database.

This document will explain how this is done:

• Print the sample report from the Demonstration database
• View the results in MS Word
• Customize the report and template to suit your needs.

The Redirect To function is available when you select OTHER as the Destination for printing a report. We will use this to demonstrate how to print a mailing label for each one of the letters you are about to send to your debtors. The easiest way to try this is to print the sample report set up already in the Demonstration database. It is named Mailing Labels Redirected To Template.

This example uses a template for MS Word created for Avery Label #5960. This template is shipped with Collect! in the STYLES folder and it is named labeltemplate.dot.

Print The Sample Report

The Mailing Labels Redirected To Template report looks for In Progress Letter contacts due today or earlier. It also checks for Address OK on each debtor. When you print the report, a mailmerge.csv file is created with headings that match a pre-designed labeltemplate.dot file in Collect!'s .\styles folder. MS Word opens automatically and displays the labels ready to print.

To try this out, sign into the Demonstration database in Collect! and stop at the Main Menu.

1. Select Print from the top menu bar and then select Reports and Letters from the drop-down choices. This will display the list of reports in the Demonstration database.

2. Scroll down the list until to see the report named Mailing Labels Redirected To Template.

3. Select the report to begin the printing process. The Print Report form will display where you can set your printing options. The settings displayed there by default for this report have been set in the Report Options form for the Mailing Labels Redirected To Template report. Please leave all the settings as they are and select PRINT.

Collect! will quickly scan through your contacts and print the appropriate data to the mailmerge.csv file in the .\styles folder. Then this information will be redirected to the pre-designed MS Word labeltemplate.dot that is also in the styles folder. The results will be displayed in MS Word automatically in a few minutes.

View The Results

When MS Word displays the results, you may want someone on hand who is familiar with that program. Label Templates have two modes - one shows the layout and the other shows the actual data. If you are looking at the layout mode, you may be confused. Your office MS Word whiz can help you out! There are buttons at the top of the screen to change the view. Also, you may need to fix the layout or page margins a bit before you print the labels.

That's all there is to it. If you are satisfied using the template we have already set up, you can copy the report to your Masterdb database. All the settings in the Report Options form will carry over and can remain as they are, as long as you continue to use the .\styles folder for your mailmerge.csv file and your labeltemplate.dot template file.

Customize The Report

If you require other fields in your labels, you may need to modify the report codes or create a new MS Word label template. Please examine the Report Body of the Mailing Labels Redirected to Template to see how this report was designed. Remember that the headings must match the template.dot that you use. Modifications require someone who is familiar with creating templates in MS Word and who understands how the headings in the Report Body will relate to creating the template.

In the report itself, we have included many extra fields so that you may be able to easily create a new template in MS Word without having to modify the report in Collect!. All the data fields that are printed to the mailmerge.csv file are available for you to use in designing your new template.dot.

You must ensure two things:

• The mailmerge.csv and the MS Word labeltemplate.dot must be in the same folder.
• The correct folder path for these two files must be entered into the Mailing Labels Redirected To Template report's Report Options. (This is accessed through the OPTIONS button in the report's Report Definition form.)

A sample mail merge report for Client mailing labels is also available. Please request details if the report named Client Labels Redirected To Template is not in your Demonstration database.

Using DDE For Letters

Two DDE samples are included with the Collect! Demodb. These demonstrate using DDE (Dynamic Data Exchange) to query Collect! directly to fill a report template created in a third party word processor.

To use DDE templates:

1. The SQL Server must be up and running.

2. Collect! must be started with the command line startup option /z to enable DDE.

3. The DDE Template must be properly set up.

Creating these templates is very easy. Please see the next section, Creating DDE Letter Templates.

Windows Updates may disable DDE. You will need to enable it. Please refer to Microsoft documentation for more information: https://technet.microsoft.com/en-us/library/security/4053440.aspx

Creating DDE Letter Templates

Using MS Word as an example, the following tips will help you create your DDE letter template.

This requires that you are familiar with the report writer in Collect! and with the list of printable field codes. Each field has a UNIQUE identifier. (Such as, de.na, which refers to the Name field on the Debtor form.)

To view a complete list of UNIQUE IDENTIFIERS: Printable Field List

Adding DDE commands

In your template, you can simply type whatever information you want to print. Wherever you need to reference field data from Collect!, use the following steps.

1. In MS Word, with your document open, press CTRL+F9 to insert the { } symbol where you want to put the field data from Collect. This symbol is necessary for MS Word to recognize your DDE command.

You must use CTRL+F9 to insert these brackets. If you use the brackets on your keyboard, MS Word will not recognize these as the DDE symbol.

2. Type, in between the brackets, the command (DDEAUTO) to query Collect! (collect query) to read the appropriate field.

Example:

{DDEAUTO collect query @de.na}
This command places the Debtor Name at this location in the report.

You can format the field as well, using fonts and underlining or bold - whatever your word processor provides.

3. Continue to place fields from Collect! into your template, using CTRL+F9 to insert the DDE symbol and then typing {DDEAUTO collect query and the correct {field code} in between the brackets.

4. Save the document as a DOC file in your LETTERS folder.

Automatically Updating Fields

You may need to switch ON 'Update automatic links on Open' in the MS Word Tools, Options, General tab. This will automatically refresh the DDE template each time you open it using the Merge Documents option.

4. In Collect!, open the Debtor and select Print, Reports and Letters from the top menu bar. Then select Merged Documents from the list of reports and select the DDE *.doc that you want to print.

MS Word should open with your template populated with Collect! data and ready for printing.

If you advance in Collect! to another debtor, some word processors will refresh the template with new data. Others require that you shut the word processing program before you select the next record for printing.

Summary

Using Collect!'s Redirect To feature, it is easy to merge address data from Collect! to a pre-designed MS Word Label template. Try this out by printing the sample report shipped in the Demodb database.

When you print the report, the mailmerge.csv will be created in Collect!'s styles folder where the sample labeltemplate.dot resides by default.

If you are satisfied that the Mailing Labels Redirected To Template report works for you, copy it to your Masterdb database.

You may customize the report sample and the template.dot to suit your needs, but this requires someone who knows how to create templates in MS Word.

The same process may be used for your favorite MS Word Letter Templates if you want to print letters out of MS Word merging data from Collect!.