Automate multiple mail merges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mjkx - 17 Aug 2006 21:27 GMT

I have 40+ mail merge templates in Word 2003 which all share a single Excel
2003 spreadsheet as a data source. I would like to be able to automate the
merge of each of these documents from a single macro. For example, I'd like
to be able to open a single Word document, run a macro which opens each of
the mail merge templates in turn and completes the merge to email. Is this a
reasonable request and can anyone point me in the right direction?

Thanks in advance for any advice!

Reply to this Message

Doug Robbins - Word MVP - 18 Aug 2006 04:40 GMT

The following code has not been tested, but should do what you want if you
supply the correct name for the field containing the email addresses:

Dim MyPath As String
Dim MyName As String
Dim maindoc As Document

'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() <> -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path

If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'get files from the selected path
'and insert them into the doc
MyName = Dir$(MyPath & "*.*")
Do While MyName <> ""
Set maindoc = Documents.Open(MyName)
With maindoc.MailMerge
.MailAddressFieldName = "nameoffieldcontainingemailaddresses"
.MailSubject = "subjectofemailmessage"
.Destination = wdSendToEmail
.Execute
End With
maindoc.Close wdDoNotSaveChanges
MyName = Dir
Loop

Signature

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 have 40+ mail merge templates in Word 2003 which all share a single Excel
> 2003 spreadsheet as a data source. I would like to be able to automate
[quoted text clipped - 7 lines]
>
> Thanks in advance for any advice!

Reply to this Message

Matt - 05 Sep 2006 17:54 GMT

Fantastic! Thank you very much.

2 ongoing issues:
1) I'd like to be able to set the location of the data source using the
macro as well. Currently I get prompted with each template to point to the
spreadsheet.
2) Each of the templates filters the data in the data source so I get
prompted with each to allow the SQL Query to execute. I'd like to be able to
either programmatically set the SQL query for each template, or
programmatically allow the existing query to run without being prompted.

Any ideas? Thanks again.

> The following code has not been tested, but should do what you want if you
> supply the correct name for the field containing the email addresses:
[quoted text clipped - 43 lines]
> >
> > Thanks in advance for any advice!

Reply to this Message

Doug Robbins - Word MVP - 05 Sep 2006 19:38 GMT

If the documents were saved after the data source was attached to them you
should be be being asked for the data source unless its the following
message that you are getting when you start talking about SQL:

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document "

If that is the case, see:

http://support.microsoft.com?kbid=825765

Signature

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

> Fantastic! Thank you very much.
>
[quoted text clipped - 62 lines]
>> >
>> > Thanks in advance for any advice!

Reply to this Message

Matt - 06 Sep 2006 06:41 GMT

OK, so I'll resave the templates to the correct source location and consider
either using the reg hack indicated in the KB. This is great progress.

One thing I'm still encountering is an error if there are no records for a
particular template. I run these merges once a week and there may be zero to
many records for each of the merges. Is there a quick way within the loop to
programmatically determine the number of records returned by the filter
before executing each merge?

Thanks

> If the documents were saved after the data source was attached to them you
> should be be being asked for the data source unless its the following
[quoted text clipped - 73 lines]
> >> >
> >> > Thanks in advance for any advice!

Reply to this Message

mjkx - 06 Sep 2006 07:46 GMT

I've solved this issue with code based on the following KB article:

http://support.microsoft.com/default.aspx?scid=kb;ko;829121

Thanks

> OK, so I'll resave the templates to the correct source location and consider
> either using the reg hack indicated in the KB. This is great progress.
[quoted text clipped - 84 lines]
> > >> >
> > >> > Thanks in advance for any advice!

Reply to this Message

mjkx - 06 Sep 2006 07:06 GMT

One other thing...Is it possible to prevent the templates from being
displayed during the merge? Can this be done behind the scenes?

Reply to this Message

mjkx - 08 Sep 2006 16:01 GMT

OK, I'm making pretty good progress, but have another question. The machines
that will be used to run these merges are locked down and users wont be able
to make changes to the registry. I have adapted the code to programmatically
set the data source as well as the specific query string for each template.
This has resolved the problem of the prompt each time a template opens and
tries to apply the filter. I have been executing the merge to a new document
for testing and everything seems to be working beautifully.

The problem I'm encountering now is that when I merge to email rather than
to a document, Outlook prompts for permission to access the address book and
then asks for permission to send each email. The specific dialog is "A
program is trying to automatically send e-mail on your behalf. Do you want
to allow this?" These merges generate hundreds of emails. Is there a way to
overcome this prompt? I have signed the macro and it should be trusted, but
that does not stop the security warning.

Thanks in advance

Reply to this Message

Doug Robbins - Word MVP - 08 Sep 2006 21:00 GMT

See the bit about the Express Click Yes utility in the Preparations section
of the article "Mail Merge to E-mail with Attachments" at:

http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.htm

Signature

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

> OK, I'm making pretty good progress, but have another question. The
> machines
[quoted text clipped - 108 lines]
>> >> >
>> >> > Thanks in advance for any advice!

Reply to this Message