by Joe Goldthwaite, Bar-S Foods
Updated in 2013 & 2014 by Peter Schellenbach, Zumasys, Inc.
Updated in 2013 & 2014 by Peter Schellenbach, Zumasys, Inc.
In this article, we’re going to explore ways to use Word’s OLE Document architecture and AccuTerm’s scripting language to automatically merge Pick data with a Word document. Microsoft Word is a very powerful word processing system. With the addition of OLE Automation, it gains much more flexibility. Almost any feature in Word can be accessed from your Pick based system.
A mail merge operation in Word is a fairly simple process. You first create a source document and link it to a data source. From there, you can insert special codes into the source document that tell Word where you want the data fields to go. Word can access a variety of data formats; Excel, Access, SQL or even text files. The important thing to keep in mind is that Word needs to be able to match up field names in the data source with the mail merge fields in the source document. For our purposes, the perfect format is the tab delimited text file that reserves the first line for the field names. This is a simple file format that you can create automatically with AccuTerm’s file transfer utility – FTD, or the FTEXPORT or FTEXPORTDA subroutines. It is also an easy format to work with. You can edit and change the file using Notepad, Word itself, Excel or any number of other Windows programs. Alternatively, an Excel worksheet can easily be used as the data source.
Our sample application consists of three parts. First the text file mentioned above. The name we’re going to use is “c:\Mailmerge\MergeData.txt”. Second, we’re going to create a Word document that will be linked to the c:\Mailmerge\MergeData.txt file and will act as the source document. We’re going to call it “c:\Mailmerge\MergeSample.doc”. Third, we’re going to create a script that will be downloaded from AccuTerm and will instruct Word to do the actual mail merge.
Most of the work involved in doing a mail merge is handled by our source document–MergeSample.doc. If you have some experience with Word mail merges, this will be much simpler. If not, read on. Creating a source document is easy. The only difficulty is remembering the field names. Word can help us here but only if we first connect the document to our data before we start inserting the merge fields. Here’s the step by step.
Download a sample data file using FTD (or FTEXPORT or FTEXPORTDA) so we have a data source to attach to the new source document. This will allow you to choose field names to insert into the new source document. You can use a small sample to get started, then use the full data set when performing the actual merge function. To download the data from Pick, we’ll use the FTD command. We created a Pick file called MERGEDATA. It has dictionary items for “Name”, “Address”, “City”, “State”, “Zip”. The names are case sensitive. Here’s the session that created the c:\Mailmerge\MergeData.txt file (see code to the right). The items in bold are the ones we typed.
AccuTerm Data Transfer Utility
(S)end, (R)eceive, (C)onfigure, (O)ptions, (H)elp or (E)xit ? S
File transfer protocol: (A)SCII or (K)ERMIT ? K
Enter source (PICK) file name: MERGEDATA
Enter source (PICK) item list: *
Enter target (DOS) file name (d:\directory\file.ext): C:\Mailmerge\MergeData.txt
Attributes to transfer: Name Address City State Zip
Generate Header Record (<Y>/N): Y
Explode MultiValue Fields (Y/<N>): N
Transfer status: Successful transfer.
Transferred 9 items, 391 bytes.
Start Word and create a new document.
Select Tools->MailMerge from the Word menu (or click the Mailings tab then click the Start Mail Merge button and select Mail Merge Wizard). This will display a dialog box with a series of buttons (or show the wizard in the right-hand side bar). Select Create and choose Form Letter from the drop down menu. You are then given the choice of using the Active Window or creating a new main document. We want to use the Active Window.
The Edit and Get Data buttons should now be enabled. The next step is to connect this document to our data file. Click on the Get Data button and select Open Data Source. You should get an open file dialog box. Go ahead and open the c:\Mailmerge\MergeData.txt file.
Now we can start actually typing our document. Any place you want to use the data from the MergeData.txt file, click on the Insert Merge Field button and select the desired field name from the drop down list.
After the document looks the way you want it to, save it to “c:\Mailmerge\MergeSample.doc”
We now have a word document that contains our form letter and is connected to the MergeData.txt file as a data source. We’re now ready for the next step. Getting our Pick data and performing the mail merge from AccuTerm’s scripting language. Repeat step 1 with the entire dataset that you want to use with your form letter. Once the data source (text file) has been downloaded, all we need to do is start up Word, load our source document and tell it to do the mail merge. It already knows the file with the data source and the field names. To do this, we’ll execute this script:
‘First create our object variables
Dim WordApp As Object
Dim WordDoc As Object
Const wdSendToPrinter = 1
‘Make sure errors don’t mess us up
On Error Resume Next
‘Start up word
Set WordApp = CreateObject(“Word.Application”)
‘Open our source document
Set WordDoc = WordApp.Documents.Open(FileName:= “C:\Mailmerge\MergeSample.doc”, ReadOnly:=True)
WordApp.ActiveWindow.View = wdPrintView ‘Workaround for Word 2013 mailmerge bug
‘Merge the document with our data file
.OpenDataSource Name:= “c:\Mailmerge\MergeData.txt”
.Destination = wdSendToPrinter
.SuppressBlankLines = True
‘That should do it. The only thing left is clean up. This is very important
‘If we don’t close both the document and the application, it will stay
‘in memory. If you try to run the macro twice, it will fail because
‘the document is already open in another session. It also
‘uses up memory
Set WordDoc = Nothing
Set WordApp = Nothing
That’s it! This simple script. It opens Word, and tells it to merge the text file with MergeSample.doc and send it to the printer. In order to use this script from your host machine, it’s necessary to automatically download and run it using AccuTerm’s script command. This is simpler than it sounds. AccuTerm has a command that allows you to download a script. Script lines are separated by the ASCII EM character (CHAR(25)). Then we send a special escape sequence to AccuTerm followed by our script. AccuTerm will then run it. To simplify things and to provide an example, this PICK subroutine handles all of the above.
* THIS SUBROUTINE EXECUTES A PRESET WORD MERGE WITH A DOCUMENT
* Created 19 March 2001 JEG
* Updated 17 March 2005 PJS
* Updated 14 April 2013 PJS
* Updated 14 March 2014 PJS
* Pass path to template document in DOCUMENT, path to merge data
* file in MERGEDATA and destination (new document or printer)
* in the DESTINATION argument.
EQU ESC TO CHAR(27), STX TO CHAR(2), CR TO CHAR(13), EM TO CHAR(25)
* Define the wdMailMergeDestination constants
EQU wdSendToNewDocument TO 0
EQU wdSendToPrinter TO 1
EQU wdSendToEmail TO 2
* If the data source document is an Excel workbook, we
* need to supply a SQL Select statement to skip prompt
EXT = OCONV(FIELD(MERGEDATA,’.’,DCOUNT(MERGEDATA,’.’)),’MCU’)
DATA.IS.XL = (EXT[1,3] = ‘XLS’)
* Build AccuTerm VBA script to execute the mailmerge
* First create our object variables
SCR = ‘Dim WordApp As Object’
SCR = SCR:EM:’Dim WordDoc As Object’
* Make sure errors dont mess us up
SCR = SCR:EM:’On Error Resume Next’
* Start the Word application
SCR = SCR:EM:’Set WordApp = CreateObject(“Word.Application”)’
* Make the Word application visible (remove this if you want to remain Word hidden)
SCR = SCR:EM:’WordApp.Visible = True’
* Open our source document
SCR = SCR:EM:’Set WordDoc = WordApp.Documents.Open(FileName:=”‘:DOCUMENT:'”, ReadOnly:=True)’
* Merge the document with our data file
SCR = SCR:EM:’WordApp.ActiveWindow.View = 3′ ;* workaround for Word 2013 bug (wdPrintView=3)
SCR = SCR:EM:’With WordDoc.MailMerge’
SCR = SCR:EM:’ .OpenDataSource Name:=”‘:MERGEDATA:'”‘
IF DATA.IS.XL THEN SCR = SCR:’,SQLStatement:=”SELECT * FROM [Sheet1$]”‘
SCR = SCR:EM:’ .Destination = ‘:DESTINATION
* If destination is wdSendToEmail, we need to assign the MailAddressFieldName and MailSubject properties
SCR = SCR:EM:’ .SuppressBlankLines = True’
SCR = SCR:EM:’ .Execute Pause:=False’
SCR = SCR:EM:’End With’
* That should do it. The only thing left is clean up. This is very important
* If we don’t close both the document and the application, it will stay
* in memory. If you try to run the macro twice, it will fail because
* the document is already open in another session. It also
* uses up memory
SCR = SCR:EM:’WordDoc.Close SaveChanges:=False’
SCR = SCR:EM:’Set WordDoc = Nothing’
IF DESTINATION <> wdSendToNewDocument THEN SCR = SCR:EM:’WordApp.Quit’
SCR = SCR:EM:’Set WordApp = Nothing’
There you have it. A simple automatic mail merge subroutine that is executed from your Pick host.
One enhancement you may want to make to this routine is call FTEXPORT or FTEXPORTDA to perform the file transfer of the merge data. Also, with a little more work it is possible to specify the data file using scripting.
Another enhancement to consider is emailing the results of the merge automatically. To do this, you need to include the recipient email address in your data document, and assign the field name of the recipient’s address to the MailAddressFieldName property (see comment in code sample above). Also, assign a subject to the MailSubject property.
Please direct any comments or questions to Peter Schellenbach at [email protected].
Return to Technical Articles