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.
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)
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 ***
rst.MoveNext '*** Move to Next Record ***
MsgBox Format(lBillCnt, "#,###") & " Emails Created."
Set rst = Nothing '*** Close RecordSet ***
Could it be that as the PDF File has been created its just sending that one again.
your coding look OK
What i do know is when you PDF a file it uses the Caption report name as its file name
So you could change Caption name in the Report on the Fly
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.