advanced mail merge of letters and reports

[MorningStarFan]

Hi All,

We have used automation to create documents via word mailmerge from our
access db. We can just press a button and as long as all our dbs and files
are in place, it works great! Except for our records that need to have
multiple recipients for one letter, multiple student awards for one recipient
award report, etc. Does anyone know how we could modify the code and the
documents or the data or a combination of some or all so that when multiple
students have been recipients for a single scholarship fund one award report
is produced? See our code that we are currently using to create the documents
below...We could have three donors to thank in a letter or one or two. We
could also have one, two, three or more students data to print on an award
report.


Function MergeIt_Awards()
Dim objWord As Word.Document
Set objWord =
GetObject("C:\Stewardship_automation\STUDENT_AWARDS_REPORT_3.doc",
"Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as db2.
objWord.MailMerge.OpenDataSource _
Name:="C:\Stewardship_automation\" & _
"db2.mdb", _
LinkToSource:=True, _
Connection:="TABLE QRY_FORMAL_FUNDNAME", _
SQLStatement:="SELECT * FROM [QRY_FORMAL_FUNDNAME]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

Thanks in advance!

 

[Peter Jamieson]

I think what you're asking for is a "multiple items per condition" type
merge,
which Word cannot really do out of the box - for approaches, see e.g.

http://homepage.swissonline.ch/cindymeister/mergfaq1.htm

http://support.microsoft.com/default.aspx?scid=kb;en-us;211303

You can also consider using Access reporting.

If you're only producing one letter/document at a time, things may be
slightly easier as long as you have a known maximum number of items on the
letter/document (because then you may be able simply to repeat

{ NEXT}{ MERGEFIELD field1 }{ MERGEFIELD field2 }

the maximum number of times they can possibly occur (and there are other
possible approaches).

Peter Jamieson

 

[MorningStarFan]

Thanks! I was wondering if this could even be done or not. I will let you
know if we get one of the workarounds to accomplish our goal here.
--
MorningStarFan

I think what you're asking for is a "multiple items per condition" type
merge,
which Word cannot really do out of the box - for approaches, see e.g.

http://homepage.swissonline.ch/cindymeister/mergfaq1.htm

http://support.microsoft.com/default.aspx?scid=kb;en-us;211303

You can also consider using Access reporting.

If you're only producing one letter/document at a time, things may be
slightly easier as long as you have a known maximum number of items on the
letter/document (because then you may be able simply to repeat

{ NEXT}{ MERGEFIELD field1 }{ MERGEFIELD field2 }

the maximum number of times they can possibly occur (and there are other
possible approaches).

Peter Jamieson

Hi All,

We have used automation to create documents via word mailmerge from our
access db. We can just press a button and as long as all our dbs and files
are in place, it works great! Except for our records that need to have
multiple recipients for one letter, multiple student awards for one
recipient
award report, etc. Does anyone know how we could modify the code and the
documents or the data or a combination of some or all so that when
multiple
students have been recipients for a single scholarship fund one award
report
is produced? See our code that we are currently using to create the
documents
below...We could have three donors to thank in a letter or one or two. We
could also have one, two, three or more students data to print on an award
report.


Function MergeIt_Awards()
Dim objWord As Word.Document
Set objWord =
GetObject("C:\Stewardship_automation\STUDENT_AWARDS_REPORT_3.doc",
"Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as db2.
objWord.MailMerge.OpenDataSource _
Name:="C:\Stewardship_automation\" & _
"db2.mdb", _
LinkToSource:=True, _
Connection:="TABLE QRY_FORMAL_FUNDNAME", _
SQLStatement:="SELECT * FROM [QRY_FORMAL_FUNDNAME]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

Thanks in advance!

 

[Kathy Webster]

I was wondering if you could help me with merging from Access in a similar
way. The difference is, I want the resulting merged text to be left on the
screen in Word, then disconnect or unlink from the data source once the
merge is complete.
Thanks!

 

[Peter Jamieson]

As long as you set

objWord.MailMerge.Destination = wdSendToNewDocument

then after you .Execute, the new document should be the ActiveDocument, and
the Mail Merge Main document can still be referenced by objWord, so you
can...

- close the Mail Merge Main Document:

objWord.Close SaveChanges:=False

- or disconnect the data source:

objWord.MailMerge.MainDocumentType = wdNotAMergeDocument

(or in Word 2003 you can probably use)

objWord.MailMerge.DataSource.Close

- or both.

Peter Jamieson

I was wondering if you could help me with merging from Access in a similar
way. The difference is, I want the resulting merged text to be left on the
screen in Word, then disconnect or unlink from the data source once the
merge is complete.
Thanks!

Hi All,

We have used automation to create documents via word mailmerge from our
access db. We can just press a button and as long as all our dbs and
files
are in place, it works great! Except for our records that need to have
multiple recipients for one letter, multiple student awards for one
recipient
award report, etc. Does anyone know how we could modify the code and the
documents or the data or a combination of some or all so that when
multiple
students have been recipients for a single scholarship fund one award
report
is produced? See our code that we are currently using to create the
documents
below...We could have three donors to thank in a letter or one or two. We
could also have one, two, three or more students data to print on an
award
report.


Function MergeIt_Awards()
Dim objWord As Word.Document
Set objWord =
GetObject("C:\Stewardship_automation\STUDENT_AWARDS_REPORT_3.doc",
"Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as db2.
objWord.MailMerge.OpenDataSource _
Name:="C:\Stewardship_automation\" & _
"db2.mdb", _
LinkToSource:=True, _
Connection:="TABLE QRY_FORMAL_FUNDNAME", _
SQLStatement:="SELECT * FROM [QRY_FORMAL_FUNDNAME]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

Thanks in advance!