[Murray Muspratt-Rouse]

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

Murray

[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" wrote
in message ...

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

Murray




--
Murray Muspratt-Rouse

[Murray Muspratt-Rouse]

Doug, thank you for your help. I hate to disagree with you about the need for automation or rather a bit of VBA programming - I must explain in more detail, now that you have pointed me at the .Destination parameter. The merge table from Access can contain up to 3 rows, of which 2 might be for snail mail and the printer, and 1 for email. What I now have to do is work out how to change the VBA code to set the 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 base document and no DDE connection to the Access table. I think the best thing will be for me to modify the code above so that it outputs one letter at a time - if that is possible. The user can then decide whether to print a letter or send it by email.

Just another opportunity!

Murray
[quote=Doug Robbins - Word MVP on news.microsoft.com;401232]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" wrote
in message ...

[Murray Muspratt-Rouse]

I have tried to program a loop to output 1 letter at a time, following pp 238/9 in the Access 2003 VBA Programmer's Reference. Here is the code, with all but the vital bits cut out: -

Sub OpenWordDoc(strDocName As String, strLetterDescription As String, strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim db As Database
Dim letterdata As DAO.Recordset
Dim rownum As Integer
rownum = 0
strCurrentFileName = CurrentDb.Name
Set letterdata = db.OpenRecordset("MergeTable", dbOpenDynaset)

Set objApp = CreateObject("Word.Application")
objApp.Visible = False
'objApp.Activate
'Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

objApp.Documents.Open FileName:=strDocName, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", XMLTransform:=""

Do While Not letterdata.EOF
rownum = rownum + 1
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\All Users\Documents\Age Concern Databases\empty.odc", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=""pixie 7826"";" & _
"User ID=Murray M-R;Data Source=" & strCurrentFileName & ";" & _
"Mode=Read;Jet OLEDB:System database=\\10.0.0.20\SharedDocs\Age Concern Databases\Secured.mdw;", _
SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = rownum
.LastRecord = rownum
End With
.Execute Pause:=False
End With
letterdata.MoveNext
Loop


Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges

objApp.Visible = True
objApp.Activate

End Sub

The EOF function does not work - without a breakpoint set the system goes into a tight loop, as you would expect!. Using Help I have found EOF, but it has to be associated with a file number, not a recordset name. Am I missing a reference? I have Visual Basic for Applications, MS Access 11.0 Object Library, OLE Automation, MS DAO 3.6 Object Library, MS Office 11.0 Object Library, MS Word 11.0 Object Library, and MS Office Runtime 1.0 Type Library.

Murray

[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" wrote
in message ...

Doug, thank you for your help. I hate to disagree with you about the
need for automation or rather a bit of VBA programming - I must explain
in more detail, now that you have pointed me at the .Destination
parameter. The merge table from Access can contain up to 3 rows, of
which 2 might be for snail mail and the printer, and 1 for email. What
I now have to do is work out how to change the VBA code to set the
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 base
document and no DDE connection to the Access table. I think the best
thing will be for me to modify the code above so that it outputs one
letter at a time - if that is possible. The user can then decide
whether to print a letter or send it by email.

Just another opportunity!

Murray
Doug Robbins - Word MVP on news.microsoft.com;401232 Wrote:
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"
wrote
in message ...




--
Murray Muspratt-Rouse

[Murray Muspratt-Rouse]

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

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

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

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

Murray
[quote=Doug Robbins - Word MVP on news.microsoft.com;401427]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]

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!

Murray
[quote=Doug Robbins - Word MVP on news.microsoft.com;401427]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

[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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Murray Muspratt-Rouse wrote:
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!

Murray
Doug Robbins - Word MVP on news.microsoft.com;401427 Wrote:
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 out how many rows are in a table in VBA. As a result the system now produces as many form letters as there are rows in the merge data 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

[quote=Graham Mayor;401772]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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org