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 database. It is named Debtor 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 DebtorLabesl.docx.

Print The Sample Report

The Debtor 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 debtormerge.csv file is created with headings that match a pre-designed DebtorLabels.docx file in Collect!'s .\styles folder. MS Word opens automatically and displays the labels ready to print.

To try this out, sign into 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 database.
2. Scroll down the list until to see the report named Debtor 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 Debtor 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 debtormerge.csv file in the .\styles folder. Then this information will be redirected to the pre-designed MS Word DebtorLabels.docx that is also in the styles folder. The results will be displayed in MS Word automatically in a few minutes.

First Time Running

The first time you run the report, you will receive an error about not finding the debtormerge.csv file. This is because your network will be different. If multiple users need to run this report, we recommend that you run it from a workstation to properly setup the path.

After Word opens, navigate to the data sources. Select update to choose a new file. Navigate to the Collect\bin\styles folder on your network and select the debtormerge.csv file.

As long as your network path doesn't change, this should open fine from now on.

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 debtormerge.csv file and your DebtorLabels.docx 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 Debtor Mailing Labels Redirected to Template to see how this report was designed. Remember that the headings must match the DebtorLabels.docx 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 debtormerge.csv file are available for you to use in designing your new DebtorLabels.docx.

You must ensure two things:

• The debtormerge.csv and the MS Word DebtorLabels.docx must be in the same folder.
• The correct folder path for these two files must be entered into the Debtor Mailing Labels Redirected To Template report's Report Options.

A sample mail merge report for Client Mailing Labels Redirected to Template is also available. It is identical to the Debtor version, but with Client Contacts.

Using DDE For Letters

To use DDE:

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.

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.