Good morning everyone,
I haven't posted in awhile but am always browsing the information. Unfortunately I have been stuck for a few days on this one and I swear I had it working in a different application at one time.
Working with Access 2007 and am knowledgeable enough to mess around with everyone else's codes trying to get them to work for me but don't have the understanding yet to write my own stuff.
So I am trying to attach a pdf of a report to an email and send it through outlook. I am using a filter and loop to pull out the correct report record and email address for each record. I have been able to loop through the different email address and attaché a single report record (as opposed to a continuous report with all the records) but it does not keep filtering the report correctly. Every email address gets the same first page of the report instead of the correctly filtered one. So I think my problem is on looping the form through the records so the report re-opens with a new record.
OK here is the code stuff:
Report Rpt_TBB2016-17_ResAppAcceptance with record source query TBB2016-17_AppLtr_Res
Form TBB2016-17_Email with record source query TBB2016-17_AppLtr_Res and button for Emailing
Can anyone tell what I am doing wrong to make it not loop through the report records?
I haven't posted in awhile but am always browsing the information. Unfortunately I have been stuck for a few days on this one and I swear I had it working in a different application at one time.
Working with Access 2007 and am knowledgeable enough to mess around with everyone else's codes trying to get them to work for me but don't have the understanding yet to write my own stuff.
So I am trying to attach a pdf of a report to an email and send it through outlook. I am using a filter and loop to pull out the correct report record and email address for each record. I have been able to loop through the different email address and attaché a single report record (as opposed to a continuous report with all the records) but it does not keep filtering the report correctly. Every email address gets the same first page of the report instead of the correctly filtered one. So I think my problem is on looping the form through the records so the report re-opens with a new record.
OK here is the code stuff:
Report Rpt_TBB2016-17_ResAppAcceptance with record source query TBB2016-17_AppLtr_Res
Code:
Private Sub Report_Open(Cancel As Integer)
Me.Filter = "AcctNum=" & Forms![TBB2016-17_Email]![AcctNum]
Me.FilterOn = True
End Sub
Code:
Option Compare Database
Private Sub EMailAccLtr_Click()
Dim MyDB As DAO.Database, rs As DAO.Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set rs = MyDB.OpenRecordset("TBB2016-17_AppLtr_Res")
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
StrTo = rs!TurfEmail
DoCmd.SendObject acSendReport, "Rpt_TBB2016-17_ResAppAcceptance", acFormatPDF, StrTo, "", "", "TBB 2016-17 Acceptance HEADER HERE", "BODY HERE", False
rs.MoveNext
Loop
rs.Close
MyDB.Close
Set rs = Nothing
Set MyDB = Nothing
Close
End Sub