| |
// 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."
try
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')
endif
// begin by trying to get
our data for the merge source
q = new query()
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)
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
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
endif
// 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
if type('q') == 'O'
q.active = false
release object q
endif
release q
endtry |