Sending Unique Emails to Distinct Recipeints

[kjhyder]

First off, I am new to using VBA in Access. I have had a request to email out activity statements to our agents with year to date transactions. I have built a table, query, and report in Access that shows this information. My report is broken down with a page for each distinct agent displaying only their transactions. I have used code from multiple sources online to come up with this:

Option Compare Database
 
Private Sub YTD_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim temp As String
    Dim mypath As String
 
    Dim strEMail As String
    Dim oOutlook As Object
    Dim oMail As Object
    Dim rstEMail As DAO.Recordset
    Dim attach As String
 
 
    mypath = "C:\Users\khyder\documents\reports\YTD Transactions - "
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT distinct [Pay] FROM [QYTD]", dbOpenSnapshot)
 
'--------------------------------Generate Split and save the report pdfs to file---------------------------------------------------------------
    Do While Not rs.EOF  '
        temp = rs("Pay")
        MyFileName = rs("Pay")
 
        Debug.Print "Generated Path for " & temp & " - " & mypath & MyFileName
 
        DoCmd.OpenReport "RYTD", acViewReport, , "[Pay]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName & ".pdf"
        DoCmd.Close acReport, "RYTD"
        DoEvents
 
        rs.MoveNext
 
        Loop
'--------------------------------------------------------------------------------------------------------------------------------
 
    Set rstEMail = db.OpenRecordset("Select Email From YTD", dbOpenSnapshot, dbOpenForwardOnly)
 
    With rstEMail
      Do While Not .EOF
 
       Set oOutlook = CreateObject("Outlook.Application")
       Set oMail = oOutlook.CreateItem(0)
 
        'Build the Recipient String
        strEMail = ![email] & ";"
 
        'Build attachment String
        attach = ![Path] & ".pdf"
 
          With oMail
             .To = Left$(strEMail, Len(strEMail) - 1)
             .Body = "Please find attached YTD transactions"
             .Subject = MyFileName & " YTD Transactions"
             .Attachments.Add attach
             .Display
          End With
 
        Set oMail = Nothing
        Set oOutlook = Nothing
 
       .MoveNext
     Loop
    End With
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
End Sub

This does create a individual pdf for each agent and saves in a folder. But it returns a Run-time error '3265': Item not found in this collection. When debugged, it errors on this line attach = ![Path] & ".pdf"

Not sure what is wrong with the code. All help is greatly appreciated.

Thanks

[zmbd]

the error is most liking saying that ![Path] is not part of the recordset rstEMail
Double check your table either the field is spelled differently or is altogether missing.

[zmbd]

This conversation is being merged to the following thread:
Access Report to Send to Multiple Recipients

This thread is closed.