run a macro for each mailmerge datasource record

Sharon Ray

unread,

Jan 20, 2003, 5:58:24 PM1/20/03

to

Is there any way to run a macro to build a table for each
record in the document's Mail Merge datasource? I tried
creating a form field and using a SET command to set the
bookmark value of the form field in order to activate
the "On Enrty" Macro, but it doesn't seem to work unless
the user actually touches that field for every letter
being merged. We send out hundreds of letters at a time
using mail merge, so visiting the form field manually on
each letter to make it run the macro is not an option.

Please let me know if there is a way to do this.

Thanks in advance for any help on this problem!
Sharon

Peter Jamieson

unread,

Jan 21, 2003, 5:51:37 AM1/21/03

to

If you are using Word XP, it is possible to do this using VBA and the
MailMerge events.

If you are using earlier versions of Word and
a. you want to build a table for /each/ record in the datasource, that may
be possible if all the data is in the record - if you can find the nearby
conversation I'm having with Jeff Rasnick in this group you may get some
ideas.
b. what you really want to do is process several records in the source for
each "output document", then there are ways using IF and NEXT fields, and/or
DATABASE fields that may help, although they have their limitations.

There are other possibilities but it might help if you can spell out exactly
what inputs you have and what outputs you are trying to produce.

--
Peter Jamieson
MS Word MVP

"Sharon Ray" <sr...@lashgroup.com> wrote in message
news:072601c2c0d7$6ffe8cd0$8ef82ecf@TK2MSFTNGXA04...

Sharon Ray

unread,

Jan 21, 2003, 10:07:07 AM1/21/03

to

Thanks so much for your willingness to help. I looked at
your conversation with Jeff and found that the information
there might be helpful in some other cases. For the
particular case I'm working with, I have found a possible
solution. However, I'd love to find a better one. I am
using Word XP and I'm curious about the MailMerge events
you mentioned. I'm currently using the Document_Open
event to execute the merge and then looping through the
datasource records and calling the macro for each record,
passing it 3 things: a unique id for each letter/record,
the active merge document and an ADO connection to a SQL
server database. This allows me to obtain records that
are 1:many for the addressee of the letter and build the
table by inserting additional rows to the table when
needed. The connect string for the ADO connection is
passed as a mail merge field in the datasource text file.

I'd prefer to call the macro during the
Document.MailMerge.Execute event vs. the open of the
document, but I'm not seeing an event for that. It may be
due to the fact that the word document was written prior
to our upgrade to XP. Or perhaps I need to set a
reference. Please let me know if you can help me to
divert my macro execution from the Document_Open to the
actual execution of the MailMerge.

Thanks,
Sharon

>.
>

Peter Jamieson

unread,

Jan 21, 2003, 12:26:05 PM1/21/03

to

Hi Sharon,

The events are all described in the Microsoft Word Visual Basic Reference
that comes with Word XP. If you look at "What's new for Microsoft Word 2002
Developers"|New Events you will see a number of events such as
MailMergeBeforeRecordMerge Event, which is probably the one you need. They
are Application Events rather than Document Events which may be why you
haven't been able to find them yet.

I find that following the example in the Help file and adapting the example
in the article mentioned in there - "Using Events with the Application
Object" gets me started. The main thing to bear in mind is that the document
object being passed to the event handler (in this particular case) is the
/Mail Merge Main Document/, not the output document.

I suspect you are rather more familiar with VBA than I am but if you need
more help than that, by all means ask again. It sounds as if these events
are just what you need if you are inserting from a secondary source via ADO.

--
Peter Jamieson
MS Word MVP

"Sharon Ray" <sr...@lashgroup.com> wrote in message

news:040501c2c15e$c3c947b0$8df82ecf@TK2MSFTNGXA02...

Sharon Ray

unread,

Jan 21, 2003, 1:38:27 PM1/21/03

to

Thanks for your help, Peter. I'm well on my way now.
After reading your earlier reply, I did some research and
found the events you're referring to. I'm still having
some problems, but I think your hint about the document
object returned being the main document vs. the merge
document may answer the issues I'm currently having. If
it doesn't, I may be posting for your help again so please
keep me in the back of your mind.

Thanks again,
Sharon

>.
>