Creating the data source in Word

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


   // begin by trying to get our data for the merge source
   d = new database()
   d.databaseName := 'mugs' := true
   q = new query()
   q.database := d
   q.sql := 'select * from customer where customer."State Id"="CA"' := true

   // 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
      // save a reference to this doc so we can come back to it later
      // make it a form letter
      oWord.activeDocument.MailMerge.MainDocumentType = 0
      // get a reference to the MailMerge object
      // 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
      // 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

      // 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
         c.range.insertAfter( trim( f['First Name'].value ) )
         c.range.insertAfter( trim( f['Last Name'].value ) )
         c.range.insertAfter( trim( f['Street1'].value ) )
         c.range.insertAfter( trim( f['Street2'].value ) )
         c.range.insertAfter( trim( f['City'].value ) )
         c.range.insertAfter( trim( f['State ID'].value ) )
         c.range.insertAfter( trim( f['Postal'].value ) )

         // get the next record
         // if we're not done yet, add another row to the table
         if not q.rowset.endOfSet

      // now activate the mail merge document
      // get a little shorter reference to save some typing
      // center the date at the top of the page
      oWord.selection.ParagraphFormat.Alignment := 1
      oWord.selection.InsertDateTime("MMMM d, yyyy",true)

      // set alignment back to "left aligned"
      oWord.selection.ParagraphFormat.Alignment := 0
      for i = 1 to 3   // insert three blank lines

      // 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.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.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
      // show the result
      oWord.visible = true


catch (exception e)

   ? "Error number",e.code
   ? e.message
   ? "occurred on line",e.lineNo

finally = false = false
   release object q
   release object d
   release q, d


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] [Data Source Excel] [Data Source dBASE Table]

Last modified: November 9, 1999