Email vba problems

[Gina Maylone]

Hi everyone!



I am trying to email statements to members. One statement per member. Right now Access is putting all of them into one report so members are getting other people's statements as well. Can anyone see what I've missed? I've been through it 100 times at least and cannot figure it out for the life of me. I thank you in advance.

Private Sub Command103_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];")


Do While Not rst.EOF
    strRptFilter = "[memberid_PK] = " & rst![MemberID_PK]
    Debug.Print rst![MemberID_PK]
    
    DoCmd.OutputTo acOutputReport, "EmailALLDamAssessStatementG1", acFormatPDF, "C:\Emailtests" & "\" & rst![MemberID_PK] & "dam.pdf"
    DoEvents




Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
      
      strTo = rst!InvoiceEmail
      fileName = "C:\Emailtests" & "\" & rst![MemberID_PK] & "dam.pdf"
     
      
    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

[Welshgasman]

You are not filtering the report for MemberID_PK ?

[Gina Maylone]

Isn't that what this is?

strRptFilter = "[memberid_PK] = " & rst![MemberID_PK]

[Welshgasman]

Isn't that what this is?

strRptFilter = "[memberid_PK] = " & rst![MemberID_PK]

Yes, but where do you use it?

[Gina Maylone]

I THINK when it outputs the report it should only output for that member_id.??

[Welshgasman]

No, that is why you are getting a complete report for every email.? Even if it did, you still have to use it somewhere, which you have not?

Try something along the lines of (this is from one of my subs)

Private Sub cmdShip_Click()
On Error GoTo Err_cmdShip_Click

    Dim stRptName As String, stParam As String, stLinkCriteria As String, stDBpath As String, stFTPpath As String
    Dim iPreview As Integer, iDialog As Integer, blnPrintIt As Boolean
    
    stDBpath = CurrentProject.Path & "\"
    stFTPpath = stDBpath & "Gazette\"
    iPreview = acViewPreview
    If Me.ChkPreview Then
       ' iPreview = 2
        iDialog = acWindowNormal
    Else
        iDialog = acHidden
    End If
    
    stRptName = "Main_by_Ship"
    
    stParam = Replace(LCase(Me.cboShip.Value), " ", "_")
    stLinkCriteria = "[Ship] = '" & Me.cboShip.Value & "'"
    
    'DoCmd.CopyObject , stParam, acReport, stRptName
        
    If Me.ChkPreview Then
        DoCmd.OpenReport stRptName, iPreview, , stLinkCriteria, iDialog
    Else
        DoCmd.OpenReport stRptName, iPreview, , stLinkCriteria, iDialog
        DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
        DoCmd.Close acReport, stRptName
    End If
    'DoCmd.DeleteObject acReport, stParam

Exit_cmdShip_Click:
    Exit Sub

Err_cmdShip_Click:
    MsgBox Err.Description
    Resume Exit_cmdShip_Click
    
End Sub

[Gina Maylone]

Thanks Walgasman! I wasn't able to get it to work. What is literally happening is all three (test) members statements are being put in the same PDF. Instead of individual ones. Hope this sheds a little light.

[Minty]

Gasman showed you the issue.

You need to open the report filtered, then export it then close it. See below;

Private Sub Command103_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];")




Do While Not rst.EOF
    strRptFilter = "[memberid_PK] = " & rst![MemberID_PK]
    Debug.Print rst![MemberID_PK]
    
	DoCmd.OpenReport  "EmailALLDamAssessStatementG1", acviewPreview, strRptFilter
    DoCmd.OutputTo acOutputReport, "EmailALLDamAssessStatementG1", acFormatPDF, "C:\Emailtests" & "\" & rst![MemberID_PK] & "dam.pdf"
    DoCmd.Close acReport "EmailALLDamAssessStatementG1"
	DoEvents




Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
      
      strTo = rst!InvoiceEmail
      fileName = "C:\Emailtests" & "\" & rst![MemberID_PK] & "dam.pdf"
     
      
    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]

Thanks for this Minty,

This put both reports into the same PDF. So confused.

[Welshgasman]

I cannot see it would do that?

Start from basics then.
Open the report in preview mode and check it is all correct?

Edit: Ok, it looks like Minty missed a comma for the arguments.
Look at my syntax, I just copied and pasted my code.

[Minty]

Apologies As WGM spotted I had missed an important "," in the report opening line.
It should be

DoCmd.OpenReport "EmailALLDamAssessStatementG1", acviewPreview, , strRptFilter , acWindowNormal