Converting WP merges that use macros to mailmerge/VBA

[David Evans]

I read that post back in September 2005 on word and WP merges, but I still
haven't found what I'm looking for...

I have poked around the various newsgroups and web sites for a few hours and
still haven't found an answer to whether my current conception of how things
are done in WP can be replicated in Word, or whether I have to rethink
things entirely.

This will be a longish post.

Part of my problem is that Word doesn't seem to have the equivalent of WP's
merge language, which is a simplified version of WP's macro language. The
different way the programs work is also a big problem for me. As an example,
I can stick an {IF}{Condition A} code directly in the document, all by
itself, and let the text run for whatever I need, then stick an {ELSE} code
in for the alternative, ending with an {ENDIF} code. I find the Excel-like
IF construct very constraining and difficult to work with, by comparison.
The two WP languages can also seamlessly pass variables back and forth
between them, as well. My merges start off with a bunch of {ASSIGN}
commands, assigning field information to variables. Something like
{ASSIGN(vFileNo)(Field1)}. That variable for vFileNo can then be fed to a
macro (to pre-populate fields) for user input and then passed back to the
merge. Of course, macros are separate little programs that do not need to be
tied to a document at all.

Also, we work from a big database that IT looks after, and I just deal with
whatever comes out of that process. The database program is FoxPro, and when
it's done there's a .dbf file created with the necessary fields in it. We do
not do mass mailings; instead, individual letters/notices are generated
after a particular record is selected and the dbf is created. The letters
usually go to two parties, since we work in a tribunal, and go either to the
parties or their counsel if they're represented. (Before I redid all the
forms, they used to have at least four different versions of each letter to
cover off the four possibilities.) So the "Dear" line is almost always in a
form like "Dear Mr. Jones and Ms. Smith:"

Process: User selects record and then from inside the FoxPro front end
selects the letter she wants.
FoxPro creates the .dbf file and then initiates the process with a macro to
WP. [You see: no document is even created yet; we start with the macro.] The
macro then defines a bunch of variables for the merge, including the text
that will be used in the letter (variable vText, which points to another
file containing just the text part of the letter).

The merge then starts. Note: I have *one* master letter.frm, into which the
information is pulled, so at the appropriate spots in the letter, the merge
file contains the command, say {VARIABLE(vText)}, and that variable
instructs the merge which file is to be inserted into the letter.

In some letters, after the merge file "reads" the dbf file to assign a
number of variables, there is a user prompt for the user to see if any of
that information needs changing. The prompt is in the macro language (like
VBA). For instance, sometimes different counsel show up than are listed in
the .dbf, so the user can change the information for, say, the variable
vApp[licant]Rep[resentative], and then the final version of vAppRep is
called into the merge file.

I've looked at http://word.mvps.org/FAQs/Userforms/index.htm, and even
created a UserForm as per the instructions,
[http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm] but I don't feel
much further ahead. For instance, in my case, the information to be inserted
has to be pre-populated from information from the .dbf file (like vAppRep,
as discussed). I just find that whole bookmark thing so complicated versus
just being able to insert a variable when and where I want to. (Also, then
having to do Ctrl-a and F9 once you're done to see the information where
you've referenced bookmarks...)

BTW, re that CreateAUserForm page, item 10, I've reread it a number of times
and still don't understand this: "Similarly, you can insert the same
information into a second bookmark by inserting a line referring to the
range of that bookmark."

Note: the merge files (the main file I guess you call it) are kept in a
universally accessible folder; the individual .dbf files are created and
inserted by FoxPro into a user's own folder, based on their network name. I
don't know how that would work with Word's templates...

I guess worst case scenario we go back to the super-ignorant merges they
used to have before I got involved. The only programming commands were just
the field names. Otherwise, everything got repeated for each version of each
letter, including the letterhead. This got pretty unwieldy, especially for
the letters with minimal user input, where the clerk would just generate the
letter and print it out, because they want everything (all copies to all
parties and their envelopes) in one file. Thus a typical merge, with a
letter addressed to counsel for both sides, ends up like this:

[copy of] letter [for applicant] [with COPY at top of letter]
envelope to applicant
letter
envelope to applicant's rep
[copy of] letter [for insurer]
envelope to insurer
letter
envelope to insurer's rep
file copy

At least I can put in an insert text file for the letterhead. That was one
of the first things I did. I remember somebody spending hours going through
all the letters (and we have a lot of letters) just to change the fax
number. Now, I just have one file that gets called into all the merges, so I
just have to change the letterhead once to effect a change across the board.

Anyway, if you have any ideas on how to start to translate this to something
in Word, or how I should conceptualize it, or if anybody has a similar
system in Word, I'd appreciate it. Again, I have no control and only a
little input into what comes out of FoxPro. Occasionally, IT has added an
additional field where needed. Otherwise, it's up to the end users to figure
out how to create their merges; word processing setup is not part of IT's
purview where we work.

David Evans

Simplified version of some of the code I've talked about:
{IFBLANK(AppRep)}{Variable(vApp)}
{ELSE}{Variable(vAppRep)}
{ENDIF}

So if the field for the applicant's rep is blank, insert the variable with
the applicant's name address etc., else stick in the representative's name.

Other merge commands include, say {GO} to jump to a {LABEL} and {RETURN} or
{QUIT} etc. Of course, there are things similar to the Fill-In or Ask fields
in Word, but usually I got information through a macro if it was anthing
complicated.

 

[Doug Robbins - Word MVP]

I have no doubt that what you want to do can be done with Word. Probably
not with the use of Mailmerge however, which is really intended for mass
mailings rather than individual letters as you indicate is your requirement.
More likely with a Visual Basic application that makes use of the Word
Object Model. I would expect that a userform will figure pretty prominently
in the picture with a number of the controls on that form being populated
from the .dbf file and the user being given the opportunity to add other
information as required. There would be a template for each of the various
letters that the user might want to create and there would be a list box on
the userform which would list the possibilities and the user could choose
which one (or ones if more than one is to be created) would be used. Like
wise with the variable text, sufficient information about each variant could
appear as items in a list box and then based on which item was chosed, the
correponding text would be inserted.

To do all this, you are going to stop trying to find equivalents to what you
do in Word Perfect, and develop a good understanding of the Word Object
Model and how things are done in Word with VBA.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

I read that post back in September 2005 on word and WP merges, but I still
haven't found what I'm looking for...

I have poked around the various newsgroups and web sites for a few hours
and still haven't found an answer to whether my current conception of how
things are done in WP can be replicated in Word, or whether I have to
rethink things entirely.

This will be a longish post.

Part of my problem is that Word doesn't seem to have the equivalent of
WP's merge language, which is a simplified version of WP's macro language.
The different way the programs work is also a big problem for me. As an
example, I can stick an {IF}{Condition A} code directly in the document,
all by itself, and let the text run for whatever I need, then stick an
{ELSE} code in for the alternative, ending with an {ENDIF} code. I find
the Excel-like IF construct very constraining and difficult to work with,
by comparison. The two WP languages can also seamlessly pass variables
back and forth between them, as well. My merges start off with a bunch of
{ASSIGN} commands, assigning field information to variables. Something
like {ASSIGN(vFileNo)(Field1)}. That variable for vFileNo can then be fed
to a macro (to pre-populate fields) for user input and then passed back to
the merge. Of course, macros are separate little programs that do not need
to be tied to a document at all.

Also, we work from a big database that IT looks after, and I just deal
with whatever comes out of that process. The database program is FoxPro,
and when it's done there's a .dbf file created with the necessary fields
in it. We do not do mass mailings; instead, individual letters/notices are
generated after a particular record is selected and the dbf is created.
The letters usually go to two parties, since we work in a tribunal, and go
either to the parties or their counsel if they're represented. (Before I
redid all the forms, they used to have at least four different versions of
each letter to cover off the four possibilities.) So the "Dear" line is
almost always in a form like "Dear Mr. Jones and Ms. Smith:"

Process: User selects record and then from inside the FoxPro front end
selects the letter she wants.
FoxPro creates the .dbf file and then initiates the process with a macro
to WP. [You see: no document is even created yet; we start with the
macro.] The macro then defines a bunch of variables for the merge,
including the text that will be used in the letter (variable vText, which
points to another file containing just the text part of the letter).

The merge then starts. Note: I have *one* master letter.frm, into which
the information is pulled, so at the appropriate spots in the letter, the
merge file contains the command, say {VARIABLE(vText)}, and that variable
instructs the merge which file is to be inserted into the letter.

In some letters, after the merge file "reads" the dbf file to assign a
number of variables, there is a user prompt for the user to see if any of
that information needs changing. The prompt is in the macro language (like
VBA). For instance, sometimes different counsel show up than are listed in
the .dbf, so the user can change the information for, say, the variable
vApp[licant]Rep[resentative], and then the final version of vAppRep is
called into the merge file.

I've looked at http://word.mvps.org/FAQs/Userforms/index.htm, and even
created a UserForm as per the instructions,
[http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm] but I don't feel
much further ahead. For instance, in my case, the information to be
inserted has to be pre-populated from information from the .dbf file (like
vAppRep, as discussed). I just find that whole bookmark thing so
complicated versus just being able to insert a variable when and where I
want to. (Also, then having to do Ctrl-a and F9 once you're done to see
the information where you've referenced bookmarks...)

BTW, re that CreateAUserForm page, item 10, I've reread it a number of
times and still don't understand this: "Similarly, you can insert the same
information into a second bookmark by inserting a line referring to the
range of that bookmark."

Note: the merge files (the main file I guess you call it) are kept in a
universally accessible folder; the individual .dbf files are created and
inserted by FoxPro into a user's own folder, based on their network name.
I don't know how that would work with Word's templates...

I guess worst case scenario we go back to the super-ignorant merges they
used to have before I got involved. The only programming commands were
just the field names. Otherwise, everything got repeated for each version
of each letter, including the letterhead. This got pretty unwieldy,
especially for the letters with minimal user input, where the clerk would
just generate the letter and print it out, because they want everything
(all copies to all parties and their envelopes) in one file. Thus a
typical merge, with a letter addressed to counsel for both sides, ends up
like this:

[copy of] letter [for applicant] [with COPY at top of letter]
envelope to applicant
letter
envelope to applicant's rep
[copy of] letter [for insurer]
envelope to insurer
letter
envelope to insurer's rep
file copy

At least I can put in an insert text file for the letterhead. That was one
of the first things I did. I remember somebody spending hours going
through all the letters (and we have a lot of letters) just to change the
fax number. Now, I just have one file that gets called into all the
merges, so I just have to change the letterhead once to effect a change
across the board.

Anyway, if you have any ideas on how to start to translate this to
something in Word, or how I should conceptualize it, or if anybody has a
similar system in Word, I'd appreciate it. Again, I have no control and
only a little input into what comes out of FoxPro. Occasionally, IT has
added an additional field where needed. Otherwise, it's up to the end
users to figure out how to create their merges; word processing setup is
not part of IT's purview where we work.

David Evans

Simplified version of some of the code I've talked about:
{IFBLANK(AppRep)}{Variable(vApp)}
{ELSE}{Variable(vAppRep)}
{ENDIF}

So if the field for the applicant's rep is blank, insert the variable with
the applicant's name address etc., else stick in the representative's
name.

Other merge commands include, say {GO} to jump to a {LABEL} and {RETURN}
or {QUIT} etc. Of course, there are things similar to the Fill-In or Ask
fields in Word, but usually I got information through a macro if it was
anthing complicated.