How to Automate a Word Mail Merge with Applescript

ID: Q149633

2.60a MACINTOSH kbinterop kbhowto

The information in this article applies to:

• 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.)

   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.)

   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

Keywords          : FxotherGeneral 
Version           : 2.60a
Platform          : MACINTOSH

Last Reviewed: April 9, 1996