Send a Word template document (.dotx) via an Access form

Question

Good morning,,

I have created a mail merge document in word named "ÉtatDeCompte.dotx", I have created as a template and the data source is a text file created from my access form from an export specification. That text file name is txtMessages.txt and the path for these 2 files is C:\Auto-Caisse\Clients\Courriel client

For my data source I have 3 records for testing purpose.

When I press the button "SendAllClients" from my form, it does send an email to all récipients from my text file, but the attachements (ÉtatDeCompte.dotx) shows 1 documents, but not to the name it supposed to, for example, if I open the attachment, I would see a name from the list but not necessarily the recipient he received the email. If I click on mail and merge from that attachment, then I can see that there are 3 records.

Goal: I want each client to receive their own document with the fields that belongs to them, here is my code:

Private Sub SendAllClients_Click()
Dim db As DAO.Database, rs As DAO.Recordset, strSQL$
On Error GoTo err_lbl
    Set db = CurrentDb
    strSQL = "SELECT * FROM [tblMessages Requête]"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    Do While Not rs.EOF
    
        SendOutlookMessage _
        rs.Fields("Courriel").Value, _
        vbNullString, _
        vbNullString, _
        "Subject", _
        "Dear " & rs.Fields("Prénom").Value & " " & rs.Fields("NomClient").Value & "," & vbCrLf & "Body", _
        False, _
      "C:\Auto-Caisse\Clients\Courriel client\ÉtatDeCompte.dotx"
      rs.MoveNext
      Loop
exit_lbl:
On Error Resume Next
    rs.Close
    Exit Sub
err_lbl:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "MyApp"
    Resume exit_lbl
End Sub

 

Everyone in the txtMessages.txt is receiving an email with the same documents, can you help?

Claude from Quebec, Canada

---

Claude Larocque

Answer 1

Claude,

If I'm following properly, you'd have to first change your MailMerge routine to generate a customize MailMerge for each recipient and then perform your email routine or add the customize MailMerge within your Email loop, something like:

Private Sub SendAllClients_Click()
Dim db As DAO.Database, rs As DAO.Recordset, Dim strSQL As String
On Error GoTo err_lbl
    Set db = CurrentDb
    strSQL = "SELECT * FROM [tblMessages Requête]"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    Do While Not rs.EOF
        'Custom Single Recipient MailMerge
        '...   

        'Actually send the e-mail now
        SendOutlookMessage _
        rs![Courriel], _
        vbNullString, _
        vbNullString, _
        "Subject", _
        "Dear " & rs![Prénom] & " " & rs![NomClient] & "," & vbCrLf & "Body", _
        False, _
      "C:\Auto-Caisse\Clients\Courriel client\ÉtatDeCompte.dotx"
      rs.MoveNext
      Loop
exit_lbl:
On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
err_lbl:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "MyApp"
    Resume exit_lbl
End Sub

I'd probably create the MailMerge file and save it with the Recipient's Name and current date.

---

Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Thank you Daniel for you answer, you seem to understand my needs, it is exactly in that section:

   'Custom Single Recipient MailMerge
        '...  

However, can you help me fill out that custom single recipient mailmerge... that is my question, how do I send that letter to each individual customer with their own letter...

Thanks

Claude

---

Claude Larocque

Answer 3

When it comes to Mail Merges I always refer people to Albert Kallal's Super Easy Word Merge which can be found at: http://www.kallal.ca/msaccess/msaccess.html

In it you'll find 2 functions, 1 to merge all records, and another which merges 1 records.  So you should be able to get what you need with the code as is.  Simply copy paste!

---

Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

Answer 4

Thanks Daniel,

The link you gave me is very useful, but if you look back at the question, I want to send the merge letter by email to all customers with their own attachment, at the moment, it send only the document for the first customer.

For now, the merge works, and the outlook do send a letter...

Claude

---

Claude Larocque

Answer 5

Claude,

If I were in your shoes,I'd be looking at executing the MergeAllWord function within your loop mechanism to generate a Merge file for the current client and then send the e-mail with it.  So something along the lines of:

Private Sub SendAllClients_Click()
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim strSQL                As String

    On Error GoTo err_lbl
    Set db = CurrentDb
    strSQL = "SELECT * FROM [tblMessages Requête]"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            'Custom Single Recipient MailMerge
            Call MergeAllWord("SELECT * FROM ... WHERE [ID]=" & rs![Id],  ...)

            'Actually send the e-mail now
            SendOutlookMessage _
                    rs![Courriel], _
                    vbNullString, _
                    vbNullString, _
                    "Subject", _
                    "Dear " & rs![Prénom] & " " & rs![NomClient] & "," & vbCrLf & "Body", _
                            False, _
                            "C:\Auto-Caisse\Clients\Courriel client\ÉtatDeCompte.dotx"
            rs.MoveNext
        Loop
    Else
        MsgBox "There are no records to process."
    End If

exit_lbl:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
err_lbl:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "MyApp"
    Resume exit_lbl
End Sub

---

Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net