Hi Alison,
you need to save the report with the filter, then email it,
then remove the filter (if you wish)
you can put these two procedures into a general module:
'------------------------------------ SetReportFilter
Sub SetReportFilter(pReportName As String, pFilter As String)
'Save a filter to the specified report
'You can do this before you send a report in an email
message
'You can use this to filter subreports instead of
putting criteria in the recordset
' USEAGE:
' example: in code that processes reports for viewing,
printing, or email
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments","City='Denver' AND
dt_appt=#9/18/05#"
' written by Crystal
' Strive4peace2006 at yahoo.com
' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string
On Error GoTo SetReportFilter_error
'---------- declare variables
Dim rpt As Report
'---------- open design view of report and set the
report object variable
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)
'---------- save and close the changed report
DoCmd.Close acReport, pReportName, acSaveYes
'---------- Release object variable
Set rpt = Nothing
Exit Sub
SetReportFilter_error:
Resume Next
msgbox Err.Description, , "ERROR " & Err.Number & "
SetReportFilter"
'press F8 to step thru code and fix problem
Stop
Resume
'next line will be the one with the error
End Sub
'------------------------------------ EMailReport
Sub EMailReport(pReportName As String, pEmailAddress As
String, pFriendlyName As String _
, pBooEditMessage As Boolean, pWhoFrom As String)
'Email a report to someone and construct the subject and
message
'SNAPSHOT Format
'example useage: on the command button code to process a
report
' EMailReport "rptSonglist", "(e-mail address removed)", _
"List of the Original Songs", _
false, "John Doe"
'PARAMETERS
'pReportName --> "rptSonglist"
'pEmailAddress --> "(e-mail address removed)"
'pFriendlyName --> "List of the Original Songs "
'pBooEditMessage --> true to edit the message before
mail is sent
' --> false to send automatically
'pWhoFrom --> "Susan Manager"
On Error GoTo EMailReport_error
DoCmd.SendObject acSendReport, pReportName, _
acFormatSNP, pEmailAddress _
, , , _
pFriendlyName & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
pFriendlyName & " is attached --- " _
& "Regards, " & pWhoFrom, pBooEditMessage
Exit Sub
EMailReport_error:
msgbox Err.Description, , "ERROR " & Err.Number & "
EMailReport"
'press F8 to find problem and fix -- comment out next 2
lines when code is done
Stop
Resume
End Sub
'------------------------------------
then, when you want to send the report:
SetReportFilter "ReportName", "criteria"
EMailReport "ReportName", "(e-mail address removed)", _
"Title", TRUE, "John Doe"
msgbox "title" & " has been emailed to " _
& "(e-mail address removed)", , _
"Done with " & "ReportName"
Warm Regards,
Crystal
MVP Microsoft Access
remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day