[DWilliams]

Alright so I have a pretty big mail merge goal here. At my work, we have a
MySQL database that stores information for use with our software system. The
software has a mail merge feature that integrates with Word. It takes
pre-created mail merge templates and exports its own values into those
templates and proceeds with printing. This part works fine, but the problem
here is that the fields it allows to be exported are somewhat limited in some
cases.

I'll use a real example to make this easier. We have one letter that needs
to be addressed to the client of a customer. The built-in mail merge only
exports the customer's address, but the client's address IS stored in the
database. The only client information exported is the name. I've googled
around and so far found a MySQL ODBC driver. I installed it and successfully
set up an ODBC connection to our database and can see our client table in
Word.

This is where I get a little bit fuzzy, though. I don't have any idea how to
have two data sources co-exist, and how to query one based on the other.

What I need to do is this:
Have the MySQL connection "built-in" to the document so that when the letter
is selected to be mail merged from the software (and therefor given ANOTHER
data source from the program), it can calculate what fields it needs from the
database and plug them into additional fields (particularly the address). So,
when the mail merge button is hit, ideally the mail merge template will know
to run a query similar to "SELECT address FROM clients WHERE
clientname={mailmerged_clientname".

How can I accomplish this? If I'm on the wrong track and there's an easier
way to solve this problem then please don't hesitate to tell me...

[Peter Jamieson]

What is even feasible depends a great deal on
a. what the existing software is actually doing and at what points in
the process you have the opportunity to intervene in some way
b. the version(s) or Word you are using and/or whether or not the
additional information you want to insert can always be surrounded by
paragraph marks (the reason for this will become clearer later)

I suppose before doing anything else I would want to be sure that I had
exhausted all the options for modifying the existing software (e.g. do
you actually have the code), or modifying the field list (e.g. is there
any kind of config file that lets you choose which fields to export, or
even something such as renaming fields/using Views to get the data
through in otherwise unused fieldnames.

The existing software could either
a. be doing a mailmerge (e.g. doing some preparation such as exporting
to a text file, then connecting to that file and using Word's built-in
MailMerge facility) or
b. be using its own merge process, e.g. where it looks through the
document for markers (which might be bookmarks or even { MERGEFIELD }
fields, and inserts the values from the data source. Some people use a
process where they populate Word Document Variables in code then update
{ DOCVARIABLE } fields to insert the values, for example.

From your description I would guess it is (a) and, starting with that
assumption, if you cannot alter the existing code in any way, your
options are probably
c. insert your own stuff /before/ the existing merge is performed. My
guess is that that would be unworkable in your case, but I mention it
just in case it isn't. (I think you'd always have to be merging letters
with only one client ID at a time, or some such)
d. if you are using Word 2000 and just possibly Word 2002, you may be
able to use a DATABASE field to insert the information you need. The
DATABASE field normally inserts a table created from a piece of SQL
within the DATABASE field. However, if it only returns one row and
column and specify that you do not want headers, then you actually get
plain text rather than a single-cell table. Unfortunately, in later
versions and revisions of Word, you get at least one paragraph mark as
well as the text, so you can only insert stuff where that does not
matter (e.g. perhaps an address is OK). Also unfortunately, you may not
be able to get the { DATABASE } field to work with ODBC data sources in
later versions of Word. There used to be an OLE DB provider for MySQL
that might help with that, but I suspect it is not maintained. For some
clues on how to use { DATABASE } fields to do this kind of thing, see

http://tips.pjmsn.me.uk/t0004.htm

but right now I only have time to suggest the following:
- the key parameters in the DAtABASE field are
\d (database pathname)
\c (connection string)
\s (query)

- for \d, make a file DSN with a .dsn extension for your database and
specify its pathname. If that's c:\mydsns\mysql.dsn, use
\d "c:\\mydsns\\mysql.dsn"
(i.e. you need to double up backslashes in these parameters)

- for \c, specify something like
\c "FILEDSN=c:\\mydsns\\mysql.dsn;PWD=your mySQL
password;"

You might also need the UID= parameter (mySQL user ID) if it isn't in there.

- for \s, the type of code you suggested is what you need, e.g.
\s "SELECT address FROM clients WHERE clientname= '{ MERGEFIELD
clientname }'"

- make sure you do not have a \h parameter.

e. if you are using Word 2002 or later, you may be able to achieve
what you need using Word's MailMerge events and some VBA code of your
own. However
- I don't have time to deal with that right now. I'm not sure it's
feasible in the scenario you describe.
- if the existing code is already using those events, this is
probably not an option (I doubt if it is though)
- you would have to find some way to ensure that the instance of
Word that the third party code uses will enable the mailmerge events
before the merge is initiated, and disable them afterwards.
- within these events you'd inspect the value of the clientname
using the Doc.MailMerge.DataSource object, and look up whatever else you
needed using (say) ADO. Then you'd probably assign the value to a
Document Variable and it would be inserted using a DOCVARIABLE field in
the document.

f. As long as you can get the existing code to output to a new
document rather than directly to the printer, there's always the option
of putting some kind of placeholder in the mail merge main document and
replacing it (e.g. using VBA and find/replace) post-merge, then printing
the document.

g. then there's the option of throwing away what you have and doing it
another way...


Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

DWilliams wrote:
Alright so I have a pretty big mail merge goal here. At my work, we have a
MySQL database that stores information for use with our software system. The
software has a mail merge feature that integrates with Word. It takes
pre-created mail merge templates and exports its own values into those
templates and proceeds with printing. This part works fine, but the problem
here is that the fields it allows to be exported are somewhat limited in some
cases.

I'll use a real example to make this easier. We have one letter that needs
to be addressed to the client of a customer. The built-in mail merge only
exports the customer's address, but the client's address IS stored in the
database. The only client information exported is the name. I've googled
around and so far found a MySQL ODBC driver. I installed it and successfully
set up an ODBC connection to our database and can see our client table in
Word.

This is where I get a little bit fuzzy, though. I don't have any idea how to
have two data sources co-exist, and how to query one based on the other.

What I need to do is this:
Have the MySQL connection "built-in" to the document so that when the letter
is selected to be mail merged from the software (and therefor given ANOTHER
data source from the program), it can calculate what fields it needs from the
database and plug them into additional fields (particularly the address). So,
when the mail merge button is hit, ideally the mail merge template will know
to run a query similar to "SELECT address FROM clients WHERE
clientname={mailmerged_clientname".

How can I accomplish this? If I'm on the wrong track and there's an easier
way to solve this problem then please don't hesitate to tell me...