Great new way to Mail merge one-to-many records fails at final hurdle...

Dear experts,

I have created a mail merge template that merges with a recordsource. In the template, I have created an event procedure for the "MailMergeAfterRecordMerge" event, that fires for each record when merged. This is a new feature since Word 2002. But it seems to go by largely undiscovered, as Google still only gives 41 hits on this event's name! Anyway, in this event procedure one could open an Access database to fetch the 'many' records from a couple of 'one-to-many' tables where the 'one' records are the mailmerge recordsource. This lets one create personalized invoices with separate purchases neatly in rows.

I want to create such a document and automate the procedure, so the Word template is instructed to start merging right away when a new document is based upon it.

But now comes the final hurdle that keeps throwing me off balance:

If I use the above event procedure to insert a many-side table into each merged record, only one record gets merged.
If I use the above event procedure to add anything else from the many-side to each record, all the records get merged.
If I manually click the "merge to new document" button in the toolbar, I can insert tables in each merged record and all records get merged (so I know it actually does work!).

My 500 point question is logical: who can help me automate this procedure, so I can mail merge all records and insert many-side tables in each one-side record.

For those of you who are still with me (hang in there!), here are the steps to reproduce my problem:

1) Create a Word template in Word 2002 (or higher) and call it MailMerge.dot
2) Create a mailmerge recordsource file with two or so records and call it MailMerge.txt
3) Put both files in the same directory
4) Tell MailMerge.dot that it's supposed to merge with MailMerge.txt
5) Put some mailmerge fields anywhere you want
6) Put the following text somewhere: Put_Table_Here (the code looks for that string to insert data when merging)
7) Open the VBA window (Alt-F11)
8) Select the "ThisDocument" thingie (what are those called anyway?) on the left hand side.
9) Paste the code below on the right hand side.
10) Save and close MailMerge.dot
11) From the File Explorer (or however you go about this yourself) right click mailmerge.dot and choose New
12) All records ought to merge automatically now
13) Close everything, and re-open MailMerge.dot for editing
14) Open the VBA window and unblock (or un-comment or whatever it's called) the line where it says: Selection.Tables.Add (I think it's the very last line)
15) Save and close and repeat step 11
16) Only one record merges
17) Close (and don't save) the new document containing the one merged record
18) You will have a "Document1" (or something like that) still open.
19) Click the "Mailmerge to new document" button yourself
20) All records merge
21) Have some coffee and come up with an elegant solution
22) Earn 500 house points.

I hope you can help me with this one!

Kind regards,
Keimpe


[code]Private WithEvents oApp As Word.Application

Private Sub Document_New()
  If oApp Is Nothing Then Set oApp = ThisDocument.Application
    ActiveDocument.MailMerge.OpenDataSource ThisDocument.Path & "\MailMerge.txt"
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub


Private Sub Document_Open()
    If oApp Is Nothing Then Set oApp = ThisDocument.Application
End Sub

Private Sub oApp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)
    MsgBox "Before record merge"
End Sub


Private Sub oApp_MailMergeAfterRecordMerge(ByVal Doc As Document)
   
    MsgBox "After record merge"
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "Put_Table_Here"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    Selection.TypeText "You can put anything here..."
    'Selection.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:= _
    2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
    wdAutoFitWindow

End Sub[/code]
LVL 1
keimpeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joanne M. OrzechManager, Document Services CenterCommented:
I started trying to follow your instructions but wonder by you are using a template with a mail merge and why you are saving your data source as a text file.
keimpeAuthor Commented:
I'm not a Word (but an Access) specialist so I use any method that actually works for me. Feel free to alter my example in any way you please. And the reason I'm saving my data source as a text file is that it's the fastest method (I know of) to merge with an Access database (Access exports the merge file and Word uses it to merge).
Joanne M. OrzechManager, Document Services CenterCommented:
Oh - so you're getting the data from Access?
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

keimpeAuthor Commented:
Yes, but it's irrelevant to this question (I think)
Joanne M. OrzechManager, Document Services CenterCommented:
Ok - well - I'll bow out.  I understand how Word merges work.  Good luck with your project keimpe.

keimpeAuthor Commented:
Hey, I'm always interested in learning new and better ways for things - I was just worried we were getting off topic. But you're the moderator, so if you have any suggestions on performing "the ultimate mail merge", I'll be all ears!
GrahamSkanRetiredCommented:
I think I understand your objective. In fact it addresses a problem that frequently appears in questions to this forum. Previously I have recommended concatenating the report list into a single string with records separated by line-feeds.

I am unclear about what you are trying to automate and why you are doing particular things. You should understand, for instance, that a mailmerge operation is usually done from a document, known as the Main document, rather than from a template. The main document remains unchanged after a mailmerge, so there is usually no need to create a new one.

Also it is not clear why you want to automate the insertion of the table. Why not just add it to the design of the main document?

As I understand it, the bit you need to automate is the filling of the table, which should look a bit like this (untested):

Sub oApp_MailMergeAfterRecordMerge(ByVal Doc As Document)
    Dim rs   As New ADODB.Recordset
    Dim cn   As New ADODB.Connection
    Dim tbl  As Table
    Dim r As Integer
   
    cn.Open '...
    rs.Open "Select * from MyTable where Field1 = " & Doc.MailMerge.DataSource.DataFields(1).Value, cn, adOpenStatic, adLockPessimistic, adCmdText
    Set tbl = Doc.Sections.Last.Tables(1) ' each record creates a new section.
    r = 2 ' leaving one row for a header
    Do Until rs.EOF
        If r > tbl.Rows.Count Then
            tbl.Rows.Add
        End If
        tbl.Cell(r, 1).Range.Text = rs.Fields("Field2").Value
        tbl.Cell(r, 2).Range.Text = rs.Fields("Field3").Value
        '...
        rs.MoveNext
        r = r + 1
    Loop
    rs.Close
    cn.Close
End Sub


keimpeAuthor Commented:
Hi Graham,

thank you for your interest in this topic and your kind answer. It's very instructive!

I'll try to answer some of your questions (I can also say that I solved the problem myself - I will write a separate topic on that one).

- "it is not clear why you want to automate the insertion of the table. Why not just add it to the design of the main document?"

This is because I don't know beforehand how many tables each sub record will hold! You see, I am trying to automate the creation of specsheets for products. "One" product can have "Many" specs. The products are the main records and the spec lines are the subrecord. Now, some specs like "Manufacturer" have only one column: "Name" and other specs like "Frequency Range" have two columns: "Min" and "Max". Still other specs have seven columns. "Insertion loss" for example, has "Port", "Range", "Min", "Typical", "Max", "Remarks" and "Margin". Now, I want to create a different table for each different kind of spec containing the exact amount of columns required. But some products have all of these specs, others only one or two. So, I never know beforehand how many tables are needed.

Thanks for explaining about main document versus template use. JOrzech said the same thing, so that makes it two to one, so I'll use that method from now on ;-)



keimpeAuthor Commented:
Dear Experts,

I solved the problem myself (after a long struggle). For those interested, the last procedure should be as follows:

Private Sub oApp_MailMergeAfterRecordMerge(ByVal Doc As Document)
   
   oApp.Selection.Find.ClearFormatting
    With oApp.Selection.Find
        .Text = "Put_Table_Here"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    oApp.Selection.Find.Execute
    oApp.Selection.Tables.Add Range:=oApp.Selection.Range, NumRows:=2, NumColumns:= _
    2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
    wdAutoFitWindow

End Sub
keimpeAuthor Commented:
J0rzech, what should I do now? Can I award Graham 250 points for teaching me how to create rows and columns on the fly? (and if so, how do I do that? Because I'm unable to say "split points" and then award 250 points to one answer only)
keimpeAuthor Commented:
Well, Graham didn't solve the problem, but he did give me some fresh insights from which I've learned a lot. So on the one hand, I would like to share some points because I'm grateful, but on the other hand, if I do so, there will be "Accepted answer" besides his comment and future Experts Exchange users might mistake his post for "the" solution, which it's not. Don't you have some secret button somewhere that can upgrade Graham's account with 250 points? And then you can either mark this question as abandoned or make my post the "Accepted answer" (if that is at all possible). O deary, what did I get myself into.....
GrahamSkanRetiredCommented:
I am not sure that I understand exactly what the problem is.  Growing the table seemed to be the only base that you didn't have covered.
keimpeAuthor Commented:
No, adding extra tables on the fly was the uncovered base. Your "growing the table" code was a real gem, but not the base.
GrahamSkanRetiredCommented:
Still puzzled. The code that you posted in the question looks virtually identical to your solution to that part of the problem.

However, you could post a points-free question in the Community support area, requesting that the question be closed with a points refund.
keimpeAuthor Commented:
Dear Graham,

yes the code is almost (but not quite) identical. So I was already close in the first place ;-)
I will try to follow-up on your advice. Thanks for the "rows-on-the-fly" example, and I'm sure we'll meet on this site again (when I can finally credit you with loads of points).
LunchyCommented:
Closed, 500 points refunded.
Lunchy
Friendly Neighbourhood Community Support Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.