Okay, now let's look at a mail-merge operation. For this example, we'll use the customer table from the Mugs sample database. We'll create a short letter and address it to all the customers in the state of California. We'll create both the document and the mail-merge data source from scratch, right from within dBASE.
Educational Tip: There will be those who feel that the salutation of this letter, using the customer's first name, is a bit too casual for a business letter. I agree. I hope this brings up the point that a preferred greeting (Mr. Ms. Dr., etc.) is very important. Remember this next time you design a table.
// the dsName is the path & file name of our merge data source
// you'll need to change this if you don't have a C:\My Documents folder.
#define dsName "C:\My Documents\MergeSrc.doc"
// next, define the fields used in the merge. this is a simple string where
// the individual fields are separated using the character specified in the
// control panel "Regional Settings" as a "List Separator". In most cases it
// is a comma.
#define dsFields "FName,LName,Address1,Address2,City,State,PostalCode"
// this will be the body of the letter.
cBody = "Acme Widgets is happy to announce a special promotion, available " + ;
"only within the state of California. For a limited time only, our " + ;
"California clients may purchase the new SuperWidget, with California " + ;
"emission controls, for only $42,386,941.22. We hope you will take " + ;
"advantage of this special sale right away."
try
// begin by trying to get our data for the merge source
d = new database()
d.databaseName := 'mugs'
d.active := true
q = new query()
q.database := d
q.sql := 'select * from customer where customer."State Id"="CA"'
q.active := true
q.rowset.first()
// if we found any records to process
if not q.rowset.endOfSet
// get a shortened reference to our fields array
f = q.rowset.fields
// try to start MS Word
oWord = new oleautoclient('word.application')
// create a new document
oDoc=oWord.documents.add()
// save a reference to this doc so we can come back to it later
n=oWord.activedocument.name
// make it a form letter
oWord.activeDocument.MailMerge.MainDocumentType = 0
// get a reference to the MailMerge object
oMerge=oDoc.mailmerge
// next, we create the datasource using the values #define'd above
oMerge.CreateDataSource( dsName, "", "", dsFields )
// now, we'll open the datasource so we can insert data
oMerge.editDataSource()
// get a reference to the document
ds = oWord.activedocument
// the datasource will be a document with a table containing the field
// names in the first row and an empty second row
// start by getting a reference to the table
t=ds.tables(1)
// now just loop through the data and put it in the table
do while not q.rowset.endOfSet
// the last row of the table is empty, so we can use the count property
// of the rows collection of the table to select the row
r=t.rows(t.rows.count)
c=r.cells(1)
c.range.insertAfter( trim( f['First Name'].value ) )
c=r.cells(2)
c.range.insertAfter( trim( f['Last Name'].value ) )
c=r.cells(3)
c.range.insertAfter( trim( f['Street1'].value ) )
c=r.cells(4)
c.range.insertAfter( trim( f['Street2'].value ) )
c=r.cells(5)
c.range.insertAfter( trim( f['City'].value ) )
c=r.cells(6)
c.range.insertAfter( trim( f['State ID'].value ) )
c=r.cells(7)
c.range.insertAfter( trim( f['Postal'].value ) )
// get the next record
q.rowset.next()
// if we're not done yet, add another row to the table
if not q.rowset.endOfSet
t.rows.add()
endif
enddo
// now activate the mail merge document
oWord.documents(n).activate()
// get a little shorter reference to save some typing
oDoc=oWord.activeDocument
// center the date at the top of the page
oWord.selection.ParagraphFormat.Alignment := 1
oWord.selection.InsertDateTime("MMMM d, yyyy",true)
oWord.selection.TypeParagraph()
// set alignment back to "left aligned"
oWord.selection.ParagraphFormat.Alignment := 0
for i = 1 to 3 // insert three blank lines
oWord.selection.TypeParagraph()
endfor
// now we insert our merge fields
oDoc.mailMerge.Fields.add(oWord.selection.range, "FName")
oWord.selection.TypeText(" ") // space between first and last name
oDoc.mailMerge.Fields.add(oWord.selection.range, "LName")
oWord.selection.TypeParagraph() // new line
oDoc.mailMerge.Fields.add(oWord.selection.range, "Address1")
oWord.selection.TypeParagraph() // new line
oDoc.mailMerge.Fields.add(oWord.selection.range, "Address2")
oWord.selection.TypeParagraph() // new line
oDoc.mailMerge.Fields.add(oWord.selection.range, "City")
oWord.selection.TypeText(", ")
oDoc.mailMerge.Fields.add(oWord.selection.range, "State")
oWord.selection.TypeText(" ")
oDoc.mailMerge.Fields.add(oWord.selection.range, "PostalCode")
oWord.selection.TypeParagraph() // new line
oWord.selection.TypeParagraph() // new line
oWord.selection.TypeText("Dear ")
oDoc.mailMerge.Fields.add(oWord.selection.range, "FName")
oWord.selection.TypeText(",")
oWord.selection.TypeParagraph() // new line
oWord.selection.TypeParagraph() // new line
oWord.selection.TypeText( cBody ) // insert the body of the letter
oWord.selection.TypeParagraph() // new line
oWord.selection.TypeParagraph() // new line
oWord.selection.TypeText("Sincerely,") // letter closing
for i = 1 to 4 // insert four blank lines
oWord.selection.TypeParagraph()
endfor
oWord.selection.TypeText("John Smith")
// now set the destination per the following:
// 0 = new document
// 1 = printer
// 2 = e-mail
// 3 = fax
oMerge.destination := 0
// now perform the merge
oMerge.execute(false)
// show the result
oWord.visible = true
endif
catch (exception e)
clear
? "Error number",e.code
? e.message
? "occurred on line",e.lineNo
finally
q.active = false
d.active = false
release object q
release object d
release q, d
endtry
Now, if you run the above code, you'll find it to be everything but fast. For just a few records it would be perfectly suitable, but for a merge of any volume, you probably won't be happy with it. While it may be possible to speed this up, I've not had a lot of success in doing so. However, there is a way to move data into Excel that is very fast and Word is perfectly happy with an Excel spreadsheet as a data source for a merge.
[MS Word Mail-Merge.how] [Data Source Excel] [Data Source dBASE Table]
Last modified: November 9, 1999