Imitating Mail Merge on server

[Diane Yocom]

I have an ASP intranet application that is required to produce Word
documents merged with data from a database. I originally looked at
automating Word's mail merge capabilities, but nixed that idea since I
learned it's not a good idea to try to automate Word on the server and I'd
rather not have to worry about whether or not the user has Word on their
machines.

I, instead, decided to have the Word templates saved as RTF files and to
write a parser/merger myself. This was going fine, although made me a
little nervous since Word produces such complicated RTF code, until we
decided that we were going to combine multiple templates into one final Word
document with, potentiall, different header and footers for each
section/template doc.

I'm having a problem figuring out how to get sections inserted into the
combined RTF doc, so I'm thinking perhaps I should rethink my solution...Can
anyone offer suggestions on how to produce database merged documents from
templates created by users on a Web server? We may have some budget, so
third-party tools would be welcome, as well.

Any suggestions would be greatly appreciated,
Diane Y.

 

[William Morris]

We do something similar in our app - it's based on the premise that at any
given moment we know which data (in this case, customers) is being worked
with. I'll give you a simple example:

--- letter ---

Dear #firstname#,

Thanks for your recent purchase. Sincerely,

#salespersonname#
Your Salesperson

--- end letter ---

--- table: LetterTokens --
tokenName varchar(255)
tokenSQL varchar(7000)

#firstname# SELECT cusFirstname FROM customers where cusNum = #cusnum#
#salespersonname# SELECT empFirstname FROM employees e INNER JOIN
customers c on e.employeeid = c.employeeid
where cusNum = #cusnum#

--- end table ---


The general process works like this:

First : open the tokens table into an array, adding an empty element on the
end for the eventual values, and concantenate each query together.
Second: run that query, using rs.open cxn, sqlString
Third: loop the array, plug in the current recordset value. set rs =
rs.nextRecordset, loop the array, and repeat until each recordset value has
been plugged in.
Fourth: open the letter, loop the array again, this time plugging the values
in with simple REPLACE statements.
Fifth: create a new file with the substitutions made and feed it to the
browser.

We have close to fifty tokens that we use in the application correspondence,
and there's no noticable lagtime. The only time there's a problem is if
someone puts in a token/query where the query hasn't been properly tested,
so the whole process fails. We've also recently added a token ~remover~
that replaces any unused sql tokens with a negative number so the query will
run, even if it doesn't return a value.

HTH,

- Wm