VBA to Email Filtered Report to Addresses Listed on Table

[Bkper087]

I have found a number of threads on this topic, but none of them seem to have been solved or are doing something a little different. I’ll try to keep it simple.

I want to email a report so that each recipient will receive a pdf of only the section of the report that is related to them. I have a table with manager information tblManager. This table has the fields: ID, Team, FirstName, LastName, Phone, Email. I also have a report called rptMonthlyUpdate

My goal is to use vba code to email each manager a filtered report that shows only the information related to their team. Here is the code I attempted. It obviously isn’t working. Any suggestions?

Private Sub btnSendReport_Click()
DoCmd.SendObject acSendReport, "rptMonthlyUpdate", acFormatPDF, DLookup("[Email]", "[tblManager]", "[Team]=" & [Team]), , , "Monthly Report", "Attached is a copy of the monthly report."

End Sub

[Micron]

No, it's not real obvious. Maybe the report isn't being attached? Maybe the DLookup isn't working because Team is a string and not a number? Maybe the email isn't being generated? What you have won't filter the report, so is that the part that "isn't working"?

[pbaldy]

In order to use that method, you either have to have the report's source query use the form for a criteria, or open the report filtered using the method below, email it, then close the report. Nothing in your code would filter it.

BaldyWeb wherecondition

[andy49]

There's no field in the dlookup statement?

And as pbaldy says you have no filter on the report (only who it goes to)


Sent from my iPhone using Tapatalk