Mail Merge 2

[pluto1415]

Thanks to help from here, I have a Mail Merge from Access that works fairly well, but I'd like to refine it even further.

Right now I have a contract in MS Word that needs to get data from Access. It needs to merge signature lines (or signature blocks or even half pages of text) for 3 different categories of signers. Insureds, Owners and Beneficiaries. Unfortunately, there could be different numbers of each category. For example, there could be 1 insured, 2 owners and 4 beneficiaries. For this reason, I'm having issues with 1 big query pulling all the data (you can imagine the results I get).

How can I mail merge one document, but have different sections repeat different amounts of times? If I keep it to 2 owners for example, I can just put an IF statement in the Word doc, ie. IF Owner2 is not null then...and have it repeat the whole signature block. I need it to not be hard coded to only 2 as that number can change depending on the case. Also, if the information must come from multiple queries, how do I merge those all into 1 document?

 

[SkipVought]

loops thru each bookmark...

dim bk as bookmark

for each bk in thisdocument.bookmarks
  msgbox bk.name
  msgbox bk.start
  msgbox bk.end
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pluto1415]

Maybe I'm being dense, but I'm just not getting it.

I've got a big bookmark "SellerSig" that is about 1/2 a page long and contains another bookmark "SellerName". What I want to do is loop through my recordset and for each instance of Owner_Name, create a new "SellerSig" bookmark inserting the correct Owner_Name at the "SellerName" bookmark. Here's what I've got

With rst
    .MoveFirst
    Do While Not rst.EOF
    sOwner = rst.Fields(Trim("Owner_Name"))
            With objWord.Selection
            .GoTo What:=wdGoToBookmark, Name:="SellerName"
            .TypeText Text:=sOwner
            End With
       
    rst.MoveNext
    Loop
    End With

This inserts each Owner_Name consecutively in the "SellerName" bookmark and does not repeat the big "SellerSig" bookmark (which I need)

When I change it to:

For Each bkSellSig In objDoc.Bookmarks
With rst
    .MoveFirst
    Do While Not rst.EOF
    sOwner = rst.Fields(Trim("Owner_Name"))
             With objWord.Selection
            .GoTo What:=wdGoToBookmark, Name:="SellerName"
            .TypeText Text:=sOwner
            End With
       
    rst.MoveNext
    Loop
    End With
Next

It gives me each Owner_Name (there are 2) repeated 2x at the one "SellerName" bookmark location....

 

[SkipVought]

well you NEVER REFERENCE the object!!!!

Something like...

                .GoTo What:=wdGoToBookmark, Name:=[b]bkSellSig.Name[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pluto1415]

I'm sorry, I'm still stuck. I can't figure out how to make it repeat the "sellersig" bookmark (and maybe it's because I know you can't have multiples of the same bookmark in Word?)

Thanks for all your help, but I'm about to give up.

 

[pluto1415]

OK, getting much much closer. I've moved the big bookmark "SellerSig" that's 1/2 a page long to another document. Now it repeats the Bookmark "SellerSig" the correct number of times, but the data it inserts into the smaller bookmark "SellerName" isn't right.

What I want to see is "SellerSig"1 containing "SellerName"1, then "SellerSig"2 containing "SellerName"2

Here's my code:

Option Compare Database
Option Explicit
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Private Sub ClosingContracts_Click()
On Error GoTo ErrorHandler
Dim strDocName1 As String
strDocName1 = "S:\document preparation\Compliance 2010\Testing\12v2.dot"
Call OpenMergedDoc1(strDocName1)

Exit Sub

ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub
End Sub

Private Sub OpenMergedDoc1(strDocName1 As String)
'On Error GoTo WordError
    
    Dim sOwner As String
    Dim objWord As New Word.Application
    objWord.Visible = True
    Dim BkSource As Word.Document
    Dim MainObjDoc As Word.Document
    Set MainObjDoc = objWord.Documents.Add(strDocName1)
    Dim strTble As String
    strTble = "TblTesting"
    Set dbs = CurrentDb
    'Set rst = dbs.OpenRecordset(strTble, dbOpenTable)
    Set rst = dbs.OpenRecordset("SELECT * FROM " & strTble & " WHERE Owner_Name IS NOT NULL")
    Dim SellSigInsertAfter As Range
 
         
    With rst
    rst.MoveFirst
        Do While Not rst.EOF
        'sOwner = rst.Fields("Owner_Name")
        Set BkSource = Documents.Open("S:\\document preparation\\compliance 2010\\testing\\seller.dot")
          
        BkSource.Bookmarks("SellerSig").Range.Copy
        Set SellSigInsertAfter = MainObjDoc.Bookmarks("SellSigBlock").Range
        SellSigInsertAfter.Collapse Direction:=wdCollapseEnd
        SellSigInsertAfter.Paste
        BkSource.Close
            
        rst.MoveNext
        Loop
        MainObjDoc.Bookmarks("SellSigBlock").Delete
    End With
 
    
Exit Sub

WordError:
        MsgBox "Err #" & Err.Number & "  occurred." & Err.Description, vbOKOnly, "Word Error"
        objWord.Application.Documents.Close wdDoNotSaveChanges
        objWord.Quit
End Sub

When this runs, I see the following repeated in each "SellerSig"
sOwnersOwnerGross Irrevocable Trust Agreement dated 02/06/1995 Oliver Family Trust Agreement dated 09/03/1997 sOwner

What I think I should see is Gross Irrevocable Trust.... in the 1st iteration and Oliver Family Trust.... in the second iteration.

 

[pluto1415]

Ignore my post above, I figured out that stuff had gotten written into my source document and saved at some point.

New Problem - Now it doesn't place anything in the "SellerName" bookmark

 

[pluto1415]

I GOT IT!!!

But - it's not trimming the Owner_Name like it should.