Creating the Data Source in Word
by Gary White, dBVIPS

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

   set database to
   if not file('customer.dbf')
      create table 'customer.dbf'(;
         customer.'First Name' char(15),;
         customer.'Last Name' char(15),;
         customer.'Street1' char(25),;
         customer.'Street2' char(25),;
         customer.'City' char(20),;
         customer.'State ID' char(2),;
         customer.'Postal' char(10))
      insert into customer values (;
         'Sarah', 'Connor', '4567 Juniper', '', 'Hertown', 'CA', '99999')
      insert into customer values (;
         'Frank', 'Brown', '4321 Maple', '', 'Yourtown', 'NY', '11111')
      insert into customer values (;
         'John', 'Smith', '1234 Main St', '', 'Mytown', 'KS', '66666')
      insert into customer values (;
         'Bill', 'Williams', '5555 Walnut', '', 'Histown', 'CA', '99999')
   // begin by trying to get our data for the merge source
   q = new query()
   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
      oDoc = oWord.documents.add()
      // save a reference to this doc so we can come back to it later
      n =
      // 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
      // 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)

      if q.rowset.first()
         // 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
            // 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
      oDoc = oWord.activeDocument
      // 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


   if type('q') == 'O' = false
      release object q
   release q



Now, if you run the above code, you'll find it to be everything but fast. 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.