CODEWITHSUNDEEP
vbams-access
D.Ex
D.Ex

Reputation: 61

Is it possible to mail merge individual records instead of having all records in one document?

The code given below takes all records and combines them into 1 mail merged document. I tried modifying the code itself to try to mail merge individual documents for each record.

However, it only gives out an error which is the "Object variable or With block variable not set". My suspicion is because the records taken using the SQL statement grabs the entire existing records in the database.

Is it possible to somehow only mail merge and save as individual documents for each record?

Example:

  1. The records of Record 1 = Record1.docx
  2. The records of Record 2 = Record2.docx
  3. The records of Record 3 = Record3.docx
Sub startMergeAL()
   Dim oWord As Object, oWdoc As Object
   Dim wdInputName As String, wdOutputName As String, outFileName As String

   'Temporary variables
   Dim totalRecord As Long, recordNumber As Long

   '------------------------------------------------
   ' Set Template Path
   '------------------------------------------------
   wdInputName = CurrentProject.Path & "\Acceptance form V3.docx"

   '------------------------------------------------
   ' Create unique save filename with minutes
   ' and seconds to prevent overwrite
   '------------------------------------------------
   outFileName = "Acceptance Letter - " & Format(Now(), "yyyymmddmms")

   '------------------------------------------------
   ' Output File Path w/outFileName
   '------------------------------------------------
   wdOutputName = CurrentProject.Path & "\Results\" & outFileName

   Set oWord = CreateObject("Word.Application")
   Set oWdoc = oWord.Documents.Open(wdInputName)

   '------------------------------------------------
   ' Start mail merge
   '------------------------------------------------
   With oWdoc.MailMerge
       .MainDocumentType = 0 'wdFormLetters
       .OpenDataSource _
        Name:=CurrentProject.FullName, _
        AddToRecentFiles:=False, _
        LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=CurrentProject.FullName;" _
        , SQLStatement:="SELECT * FROM `Acceptance_Letter`"
            .Destination = 0 'wdSendToNewDocument
            .Execute Pause:=False
    End With

    '------------------------------------------------
    ' Hide Word During Merge
    '------------------------------------------------
            oWord.Visible = False

            totalRecord = DCount("*", "Acceptance_Letter")
            Debug.Print ("totalRecord: " & totalRecord)


           ''Error
            For recordNumber = 1 To totalRecord

               Debug.Print ("Print: " & recordNumber)
               outFileName = "Acceptance Letter - " & recordNumber

               '------------------------------------------------
               ' Save file as Word Document
               '------------------------------------------------
               oWord.ActiveDocument.SaveAs2 wdOutputName & recordNumber & ".docx"

               '------------------------------------------------        
               ' Quit Word to Save Memory
               '------------------------------------------------
               oWord.Quit savechanges:=False

               '------------------------------------------------
               ' Clean up memory
               '------------------------------------------------
               Set oWord = Nothing
               Set oWdoc = Nothing

            Next recordNumber
    End Sub

Upvotes: 1

Views: 311

Answers (1)

June7
June7

Reputation: 21389

Don't use apostrophes to delimit table name in SQL statement, use [ ]. Code fails if not used in statement assigned to SQLStatement property.

Quitting Word and cleaning memory inside loop is probably cause of error. However, I doubt that loop can save each record to individual doc file. This may require code looping through a recordset with field(s) that would be used as criteria to build filtered SQL for single record to merge, save Word doc, close Word docs, move to next record, repeat merge. Consider:

Sub startMergeAL()
Dim oWord As Object, oWdoc As Object, rs As DAO.Recordset
Set oWord = CreateObject("Word.Application")
Set rs = CurrentDb.OpenRecordset("SELECT ID FROM Acceptance_Letter")
' Hide Word During Merge
oWord.Visible = False
Do While Not rs.EOF
    Set oWdoc = oWord.Documents.Open(CurrentProject.Path & "\Acceptance form V3.docx")
    ' Start mail merge
    With oWdoc.MailMerge
        .MainDocumentType = 0 'wdFormLetters
        .OpenDataSource _
            Name:=CurrentProject.FullName, _
            LinkToSource:=True, _
            AddToRecentFiles:=False, _
            SQLStatement:="SELECT * FROM [Acceptance_Letter] WHERE ID = " & rs!ID, _
            Connection:= _
                 "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
                 "Data Source=CurrentProject.FullName;"
        .Destination = 0 'wdSendToNewDocument
        .Execute Pause:=False
    End With
    ' Save file as new Word Document
    oWord.ActiveDocument.SaveAs2 CurrentProject.Path & "\Results\Acceptance Letter - " & _
                   Format(Now(), "yyyymmddmms") & "_" & rs!ID & ".docx"
    oWord.ActiveDocument.Close False
    oWdoc.Close False
    rs.MoveNext
Loop
' Quit Word
oWord.Quit savechanges:=False
End Sub

Upvotes: 1

Related Questions