[MSAccessProfessionals] Filter report before emailing it

Hello everyone,

 

I have an attendance list report that I would like to filter by supervisor and email each supervisor only their pages of the report. I have sub procedure, “SendMailAttachment”, that will email the report. It’s variables are the report name and recipients. I have setup this VBA code that will loop through the supervisors and pull out their email address. I can also filter the report and print it by supervisor. I just don’t know how to filter the report and then email the filtered report to each supervisor. Here is the code:

Public Sub AttendanceList()

    'Email Attendance list to each supervisor

    Dim db As Database

    Dim rst As DAO.Recordset

    Dim lngSupID As Long

    Dim SupEmail As String

      

        Set db = CurrentDb

        Set rst = db.OpenRecordset("tblSupervisorEmail")

        Do Until rst.EOF

            lngSupID = rst![EEID-1]

            SupEmail = rst![Email]

            SendMailAttachment "rptAttendanceSheet-Email", SupEmail

            DoCmd.OpenReport "rptAttendanceSheet-Email", acViewNormal, , "SupID = " & lngSupID

            Debug.Print rst![Email]

            rst.MoveNext

        Loop

        rst.Close

        Set rst = Nothing

        Set db = Nothing

       

End Sub

 

This line will email the complete report to each supervisor   SendMailAttachment "rptAttendanceSheet-Email", SupEmail

This line filters the report and will print each supervisor’s report separately, DoCmd.OpenReport "rptAttendanceSheet-Email", acViewNormal, , "SupID = " & lngSupID

 

I need to know how to add the filter to the sendMailAttachment sub procedure.

 

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269