Automating email of a mailmerge document

[Murray Muspratt-Rouse]

I have just been given another opportunity - automating the emailing o
a Word document resulting from a mailmerge, the mailmerge data comin
from Access. I would be grateful for advice on the basic steps t
follow following the completion of the mailmerge into a new document.

Murra

 

[Doug Robbins - Word MVP on news.microsoft.com]

Probably no automation required. A mail merge can be executed to email.

--
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, originally posted via msnews.microsoft.com

 

[Murray Muspratt-Rouse]

Doug, thank you for your help. I hate to disagree with you about th
need for automation or rather a bit of VBA programming - I must explai
in more detail, now that you have pointed me at the .Destinatio
parameter. The merge table from Access can contain up to 3 rows, o
which 2 might be for snail mail and the printer, and 1 for email. Wha
I now have to do is work out how to change the VBA code to set th
destination according to the content of the row: -

With objApp.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

I have tried wdSendToEmail without success. I finished up with the bas
document and no DDE connection to the Access table. I think the bes
thing will be for me to modify the code above so that it outputs on
letter at a time - if that is possible. The user can then decid
whether to print a letter or send it by email.

Just another opportunity!

 

[Doug Robbins - Word MVP on news.microsoft.com]

Well that is a significantly different situation from that mentioned in your
original post.

To automate the execution of mail merge to email destinations, it is
necessary to supply the MailAddressFieldName

With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.MailAddressFieldName =
[fieldfromdatasourcethatcontainstheemailaddresses]

etc.

Using the above type of construction, I don't think that it will be possible
to use code to intercept each record to determine to what destination it
should be merged.

For information on creating individual documents as part of the mail merge
process, see the "Individual Merge Letters" item on fellow MVP Graham Mayor's
website at:

http://www.gmayor.com/individual_merge_letters.htm

If you are using Word XP or later, the "Add-in to Merge Letters to Separate
Files" that I have written and that can be downloaded from that site will
allow you to create each letter as a separate file with a filename taken
from a field in the data source with a minimum of fuss.

I believe that it should be possible to modify the code in that Add-in so
that it can determine from each record the destination to which the "merge"
should be executed and thus automate the whole process, Printing those that
are to be printed, email those that are to be emailed and creating
individual documents for those for which the destination is to be a new
document.




--
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, originally posted via msnews.microsoft.com

 

[Murray Muspratt-Rouse]

Thank you again Doug for your help. I spent quite a while trying to fin
a definition of the VBA code required for merging to email and failed t
find anything.

It is not my intention to intercept individual letters to determin
their destination but to merge each one to a new document. The use
will be presented with a separate form letter for each recipient an
can then decide whether to print it for snail mail or send it vi
email. This situation arises because a number of the recipients ar
likely not to be in the UK.

I have a problem with Graham Mayor's solution because of the securit
setup on the database, which does not allow Word to connect back t
Access, even when given a valid user name and password. In fact
believe I am going to have to build a new version of the database fro
the old, just in case the problem is a hangover from when I built it
1/2 years ago on aneralier version of Access.

Before it carries on looping the code does produce a letter. I must tr
to find a solution for the EOF problem!

Well that is a significantly different situation from that mentioned i
your
original post.

To automate the execution of mail merge to email destinations, it is
necessary to supply the MailAddressFieldName

With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.MailAddressFieldName =
[fieldfromdatasourcethatcontainstheemailaddresses]

etc.

Using the above type of construction, I don't think that it will b
possible
to use code to intercept each record to determine to what destinatio
it
should be merged.

For information on creating individual documents as part of the mai
merge
process, see the "Individual Merge Letters" item on fellow MVP Graha
Mayor's
website at:

http://www.gmayor.com/individual_merge_letters.htm

If you are using Word XP or later, the "Add-in to Merge Letters t
Separate
Files" that I have written and that can be downloaded from that sit
will
allow you to create each letter as a separate file with a filenam
taken
from a field in the data source with a minimum of fuss.

I believe that it should be possible to modify the code in that Add-i
so
that it can determine from each record the destination to which th
"merge"
should be executed and thus automate the whole process, Printing thos
that
are to be printed, email those that are to be emailed and creating
individual documents for those for which the destination is to be a ne

document.




--
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, originally posted via msnews.microsoft.co

 

[Murray Muspratt-Rouse]

Doug, I have managed to adapt Graham Mayor's splitter code to produc
separate letters. Thank you again for pointing me in the righ
direction!

I believe the code also contains the solution to my Access VBA proble
- if only I can find out how to apply it!

Well that is a significantly different situation from that mentioned i
your
original post.

To automate the execution of mail merge to email destinations, it is
necessary to supply the MailAddressFieldName

With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.MailAddressFieldName =
[fieldfromdatasourcethatcontainstheemailaddresses]

etc.

Using the above type of construction, I don't think that it will b
possible
to use code to intercept each record to determine to what destinatio
it
should be merged.

For information on creating individual documents as part of the mai
merge
process, see the "Individual Merge Letters" item on fellow MVP Graha
Mayor's
website at:

http://www.gmayor.com/individual_merge_letters.htm

If you are using Word XP or later, the "Add-in to Merge Letters t
Separate
Files" that I have written and that can be downloaded from that sit
will
allow you to create each letter as a separate file with a filenam
taken
from a field in the data source with a minimum of fuss.

I believe that it should be possible to modify the code in that Add-i
so
that it can determine from each record the destination to which th
"merge"
should be executed and thus automate the whole process, Printing thos
that
are to be printed, email those that are to be emailed and creating
individual documents for those for which the destination is to be a ne

document.




--
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, originally posted via msnews.microsoft.co

 

[Graham Mayor]

In the interest of fairness, I should point out that the code from my web
page http://www.gmayor.com/individual_merge_letters.htm is 99% Doug's.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Doug, I have managed to adapt Graham Mayor's splitter code to produce
separate letters. Thank you again for pointing me in the right
direction!

I believe the code also contains the solution to my Access VBA problem
- if only I can find out how to apply it!

Well that is a significantly different situation from that mentioned
in your
original post.

To automate the execution of mail merge to email destinations, it is
necessary to supply the MailAddressFieldName

With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.MailAddressFieldName =
[fieldfromdatasourcethatcontainstheemailaddresses]

etc.

Using the above type of construction, I don't think that it will be
possible
to use code to intercept each record to determine to what destination
it
should be merged.

For information on creating individual documents as part of the mail
merge
process, see the "Individual Merge Letters" item on fellow MVP Graham
Mayor's
website at:

http://www.gmayor.com/individual_merge_letters.htm

If you are using Word XP or later, the "Add-in to Merge Letters to
Separate
Files" that I have written and that can be downloaded from that site
will
allow you to create each letter as a separate file with a filename
taken
from a field in the data source with a minimum of fuss.

I believe that it should be possible to modify the code in that
Add-in so
that it can determine from each record the destination to which the
"merge"
should be executed and thus automate the whole process, Printing
those that
are to be printed, email those that are to be emailed and creating
individual documents for those for which the destination is to be a
new

document.




--
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, originally posted via msnews.microsoft.com

 

[Murray Muspratt-Rouse]

I am pleased to report that someone has given the tip for finding ou
how many rows are in a table in VBA. As a result the system no
produces as many form letters as there are rows in the merge dat
table. Here is the relkevant code: -

strTableName = "MergeTable"
strowcount = GetRecordCount(strTableName)

Public Function GetRecordCount(strForm) As Long
GetRecordCount = Nz(DCount("*", "MergeTable"))
End Function

With objApp.ActiveDocument.MailMerge
strownum = 0
Do While strownum < strowcount
strownum = strownum + 1
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
'.FirstRecord = wdDefaultFirstRecord
'.LastRecord = wdDefaultLastRecord
.FirstRecord = strownum
.LastRecord = strownum
End With
.Execute Pause:=False
Loop
End With

The crucial line should be Do While strownum 'less than' strowcount...

I will sleep better tonight!

Murray