Access to Word Mail Merge - Trouble with Last Part

Question

I'm having some type of fault with my code, but I'm not sure what. This code was working for me less than a month ago.

What I am trying to do: Have it spit out a mail merge letter for each address in the database.

The result of my code: Depending on how many addresses I check telling it gives me that many exact replicas of the template filled with the FIRST and LAST entries of the database.

Example: I check off 18 addresses in the database and pass that variable to be in my export table. It spits out 18 microsoft word files, each word file has a copy of the template filled in with the first address filled in on page 1 and the last address filled in on page 2.

What am I doing wrong?

Private Sub MailMerge(ClientTemplate As String)
Dim Word As Object
Dim Doc As Object
Dim Sel As Object
Dim nRows As Integer


Set Word = CreateObject("Word.Application")
Set Doc = Word.Documents.Open(ClientTemplate)
Set Sel = Word.Selection
nRows = DCount("*", "tblFinalMailMerge")


With Word
    .Visible = True
    .Application.Activate
    '.Documents.Open (ClientTemplate)
End With

With Doc.MailMerge
    .MainDocumentType = wdFormLetters
    .Destination = wdSendToNewDocument
    .OpenDataSource Name:=CurrentDb.Name, SQLStatement:="SELECT * FROM [tblFinalMailMerge]"
    With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
    End With


    .Execute Pause:=False
End With

For x = 1 To nRows

    With Doc.MailMerge.DataSource
        .ActiveRecord = x
        If ActiveRecord > .LastRecord Then Exit For
    End With
        Word.ActiveDocument.SaveAs _
        FileName:="C:\temp\test" & x & ".docx"

Next x

Anyone? This one is really biting me in the ass - it has wdSendToNewDocument, I don't understand why it's making 2 templates (first and last) in one document. — user2296381

Unknown Unknown · Accepted Answer · 2014-02-16T11:25:29

The starting point here is that merging Form Letters to a new document, the .Execute will result in a single ActiveDocument that contains the same number of form letters (i.e. nRows) as there are records in your data source. Your "For x" loop then makes nRows copies of that document.

What isn't so clear is why each document only contains the first and last records from your data. However, if your mail merge main document contains any { NEXT } or { NEXTIF } fields, each letter may consume more than one record from the data source (whereas the "For x" loop will still create nRows copies of it).

As an initial debug step, I would cut out the For loop and see what the mrege produced. If I was still mystified, I'd probably create a simple "form letter" containing nothing but the fields in the data source, and merge that instead.

However, to do this, you will need to use another approach - there are other questions here (some recent) that discuss the matter of "splitting" a mailmerge output file, or producing one document per data source record. Have a look around.

Access to Word Mail Merge - Trouble with Last Part

1 Answers