Send PDF's to individual recipients

[Gina Maylone]

Good morning all! I am attempting to email monthly statements to members. I can successfully export them to separate PDF files, but when it comes to actually sending those PDF files to the appropriate member, I am getting the error: "The item has been moved or deleted". It errors on the .To=strto line. Can anyone tell me what I've got wrong please? TIA!! Hope you're having a great day!

Private Sub Command100_Click()
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String
Dim rst As DAO.Recordset
Dim LastName As Variant
Dim strBody As String, lngCount As Long, lngRSCount As Long
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [emailinvoicelist]ORDER BY [lastname];", dbOpenSnapshot)
Do While Not rst.EOF
    strRptFilter = "[memberid_PK] = " & rst![MemberID_PK]
    DoCmd.OutputTo acOutputReport, "GroupAsmtStatement", acFormatPDF, "C:\Clients\EmailTESTS" & "\" & rst![MemberID_PK] & ".pdf"
    DoEvents
    rst.MoveNext
Loop
'DoCmd.OutputTo acReport, "emailGroupAsmtStatement2", acFormatPDF, fileName
MsgBox "file created"
'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
      rst.MoveLast
      rst.MoveFirst
      Do Until rst.EOF
      lngCount = lngCount + 1
      
      strTo = rst!InvoiceEmail
      fileName = "C:\Clients\EmailTESTS" & "\" & rst![MemberID_PK] & ".pdf"
     
     '.Recipients.Add rst!InvoiceEmail
    Debug.Print rst!InvoiceEmail
    .TO = strTo
    .Subject = "Statement Attached"
    .Body = "Thank you"
    .Attachments.Add fileName
    
        .Send
    rst.MoveNext
      Loop
    
End With
MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
End Sub

[Wayne]

See in red below. Is that a command line or a comment line? You started it with an apostrophe.

Wayne

Private Sub Command100_Click()
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String
Dim rst As DAO.Recordset
Dim LastName As Variant
Dim strBody As String, lngCount As Long, lngRSCount As Long
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [emailinvoicelist]ORDER BY [lastname];", dbOpenSnapshot)
Do While Not rst.EOF
    strRptFilter = "[memberid_PK] = " & rst![MemberID_PK]
    DoCmd.OutputTo acOutputReport, "GroupAsmtStatement", acFormatPDF, "C:\Clients\EmailTESTS" & "\" & rst![MemberID_PK] & ".pdf"
    DoEvents
    rst.MoveNext
Loop
'DoCmd.OutputTo acReport, "emailGroupAsmtStatement2", acFormatPDF, fileName
MsgBox "file created"
'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
      rst.MoveLast
      rst.MoveFirst
      Do Until rst.EOF
      lngCount = lngCount + 1
      
      strTo = rst!InvoiceEmail
      fileName = "C:\Clients\EmailTESTS" & "\" & rst![MemberID_PK] & ".pdf"
     
     '.Recipients.Add rst!InvoiceEmail
    Debug.Print rst!InvoiceEmail
    .TO = strTo
    .Subject = "Statement Attached"
    .Body = "Thank you"
    .Attachments.Add fileName
    
        .Send
    rst.MoveNext
      Loop
    
End With
MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
End Sub



Reply   Reply With Quote    

[Gina Maylone]

Hi Wayne, it's actually commented out because I already have

DoCmd.OutputTo acOutputReport, "GroupAsmtStatement", acFormatPDF, "C:\Clients\EmailTESTS" & "\" & rst![MemberID_PK] & ".pdf"

above it. I should have erased it, but, since it references a different report, I left it in for my own information.

Thanks!

[pbaldy]

Does it send the first one (my guess is yes)? I think you need to create the email inside the loop, not before.

[Gina Maylone]

it doesn't send any, it error right away (after it outputs the pdf's). I will try it inside the loop. Thanks Paul!

[Gina Maylone]

Not getting the error access error -2147418107 it is illegal to call out while inside message filter. Errors on line "

Set oEmail = oApp.CreateItem(olMailItem)", I have never encountered this error before and I thought I'd seen them all!

Private Sub Command100_Click()
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String
Dim rst As DAO.Recordset
Dim LastName As Variant
Dim strBody As String, lngCount As Long, lngRSCount As Long
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [emailinvoicelist]ORDER BY [lastname];", dbOpenSnapshot)
Do While Not rst.EOF
    strRptFilter = "[memberid_PK] = " & rst![MemberID_PK]
    DoCmd.OutputTo acOutputReport, "emailGroupAsmtStatement2", acFormatPDF, "C:\Clients\EmailTESTS" & "\" & rst![MemberID_PK] & ".pdf"
    DoEvents
    rst.MoveNext

      
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
     
      
      strTo = rst!InvoiceEmail
      fileName = "C:\Clients\EmailTESTS" & "\" & rst![MemberID_PK] & ".pdf"
     
     '.Recipients.Add rst!InvoiceEmail
    Debug.Print rst!InvoiceEmail
    .TO = strTo
    Debug.Print strTo
    Debug.Print fileName
    .Subject = "Statement Attached"
    .Body = "Thank you"
    .Attachments.Add fileName
    .Send
    rst.MoveNext
  
    
End With
Loop
MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
End Sub

[Gina Maylone]

Ok, so this is weird, I copied the code to add it here, then continued and it "skipped" the error and actually emailed the statements! What the heck? It has self corrected, just ran it successfully two times. Had something to do with an Excel file I had open (no idea why, but, I closed it and it ran!) Thank you all!!

[pbaldy]

You've got 2 MoveNext lines in that unless I'm going blind. This is the relevant part from a working process:

    Do While Not rs.EOF
        Forms!frmStatementFilter.txtEmailCust = rs!CustomerAccount

        DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, "c:\AccessAp\" & rs!CustomerAccount & "Statement.pdf"

        Set MyOutlook = CreateObject("Outlook.Application")
        Set MyMail = MyOutlook.CreateItem(0)

        strEmail = rs!Email
        MyMail.To = strEmail 
        MyMail.Subject = rs!CompanyName & " statement"

        Set myattachments = MyMail.Attachments
        myattachments.Add "c:\AccessAp\" & rs!CustomerAccount & "Statement.pdf"

        MyMail.Body = "Your statement is attached"
        MyMail.display
        'MyMail.send

        rs.MoveNext
    Loop

[Gina Maylone]

No, you're not going blind, you're absolutely correct! I took out the first one and NOW I'm getting 3 emails instead of 2, so YAY!!! THANKS AGAIN!

[pbaldy]

Happy to help!