Microsfot Access : Report Loop Stuck on First Record

[JunkBot]

Hello
Could someone help with the following? I have the following code which should split a report and e-mail it.
The code correctly splits the pdf to the first record and then opens an email with the correct To/ Subject/ Body however after sending the first file the second email opens with the next To/ Subject/ Body but the attached still relates to the first record its 'looping' the contacts but not the report.
Any ideas?
Thanks
Junkbot

Private Sub Command3_Click()
    Dim dbName As Database
    Dim rst As Recordset
    Dim lRecNo As Long
    Dim lBillCnt As Long
    Dim zWhere As String
    Dim zMsgBody As String
    Dim zEmail As String
    Dim zSubject As String
    Dim zDocname As String
    zDocname = "rptFSMMonthly"
    Set dbName = CurrentDb()
    Set rst = dbName.OpenRecordset("qryReportData", dbOpenDynaset)
    rst.MoveFirst
    lBillCnt = 0
 
    Do While Not rst.EOF
        If rst![contact_email] <> "" Then
            zWhere = "[school_code] = " & (rst![school_code])
            DoCmd.OpenReport zDocname, acPreview, , zWhere
            zEmail = rst![tblHbschool.contact_email]
            zSubject = "Meals Report for : " & rst![school_name]
            zMsgBody = "Dear " & rst![contact_name] & vbCrLf & "Please find your attached." & vbCrLf & "Regards"
            DoCmd.SendObject acReport, zDocname, acFormatPDF, zEmail, , , zSubject, zMsgBody, True
            lBillCnt = lBillCnt + 1  '*** Count Emails Created ***
        End If
        rst.MoveNext        '*** Move to Next Record ***
    Loop
    MsgBox Format(lBillCnt, "#,###") & " Emails Created."
    Set rst = Nothing     '*** Close RecordSet ***
End Sub

 

[andshrew]

I suspect the issue is that your opening the report but never closing it, so the first report you run remains the active one throughout the loop.

Adding

DoCmd.Close acReport, zDocname, acSaveNo

after your DoCmd.SendObject will close close the report without saving it, so it should then open the next report on the following loop.