How to Automate a Word Mail Merge with Applescript

• Microsoft FoxPro for Macintosh, version 2.6a

SUMMARY

This article shows by example how to automate a Microsoft Word for Macintosh mail merge that relies on FoxPro for the data. An AppleScript program does a FoxPro SQL select and calls Word to perform the merge using an existing form letter. The Script is run by double-clicking its icon if it is saved as a runtime script.

MORE INFORMATION

Example

Use the Customer database, and make the selection criteria a state. The form letter is an invitation to a demo and uses the fields contact, company, address, city, state, and zip as follows:

   Microsoft Macintosh Group
   Jan 2,1996

   <COMPANY>
   <ADDRESS>
   <CITY>,    <STATE>  <ZIP>

   Dear <CONTACT>,

   Please come to see us at our new show room. On Mondays, Wednesdays, and
   Fridays, we will be doing demos on the AppleScript Advantage.

   Yours truly,

   John Smith

Step-by-Step Procedure

Create a form letter using Word and save it as a mail_merge_doc in the Macintosh HD:Microsoft FoxPro 2.6:Tutorial folder. Use the saved name in the following script:

1. On the Tools menu, select Mail Merge. (For instructions on how to set up a mail merge, see Word Help.)

2. In the Mail Merge dialog box, click Open Data Source to open a table. If tables do not display when FoxPro/dBase is selected under List Files of Type, choose All Files, and select a table. Once a data source is selected, two new buttons appear in Word. Use the Insert Merge Field button to place fields in the document. (See the note at the end of this article before closing the form letter or Word.)

3. Using the Script Editor, create the following AppleScript program:

   NOTE: In the following code, the semicolon is used as a line continuation character. This is not the line continuation character in AppleScript which you create by pressing the option key and the return key. The & is the concatenation symbol. (* *) and -- indicate comments. The "do Script" command executes FoxPro or Word commands from AppleScript.

   AppleScript Program -------------------

   set state to text returned of ;

       (display dialog "What state would you like to print" default answer "";
            buttons {"OK"})
   
      (* When the dialog window displays, enter a state abbreviation. The
         state variable will contain the results *)
   
      tell application "Microsoft FoxPro"
      Activate
      Do Script "SET SAFETY OFF"
      Do Script "SET DEFAULT TO 'Macintosh HD:Microsoft FoxPro 2.6:Tutorial'"
        Do Script ;
            "SELECT contact,company,address,city,state,zip FROM customer
                  WHERE state='" & state & "' INTO TABLE
                 ' Macintosh HD:Microsoft FoxPro 2.6:Tutorial:Mailinglist'"
   
      (* When writing this code in AppleScript, all three of the previous
         lines must be on a single line. Continuation characters cannot be
         placed in the middle of a command string following a do script. *)
   
        Do Script "USE"          --close mailinglist.dbf so Word can open it.
        Do Script "SELECT customer"
        Do Script "USE"
      end tell
   
      tell application "Microsoft Word"
        Activate
        open file "Macintosh HD:Microsoft FoxPro 2.6:Tutorial:mail_merge_doc"
   
      (* The previous line opens the Word mail merge document *)
   
        do script "MailMergeToDoc"       --issue Word's mail merge command
        activate
      end tell
   
   

NOTE: The code does not close the Word document. The table produced by the FoxPro select statement is Mailinglist.dbf. When prompted to save the .dbf document in Word, choose no. Choosing yes creates a Word document that can no longer be opened in FoxPro. For more information about this, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q142795
   TITLE     : PRB: FoxPro DBF Opened/Saved by MacWord Alters File Type