Email report by macro

[tjham]

I am trying to email a report to different recipients. The report has different data according to the recipient.
I've created a macro to open the report and in the "where condition" I put in the criteria. Which all works. After it opens, the next step in the macro is to Sendobject, which I have setup to whichever recipient. The next step is to close the report.

Then the next step is to open the report again, with the "where condition" set up for the next recipient. and so on.

Each recipient has the:
OPEN
SENDOBJECT
CLOSE

The report is ran from a form that has a date range on it and a button to run the macro.

It will run the first set of OPEN, SENDOBJECT, CLOSE and send it. Anything after that it will run but not send.

If I run them individually, it will do the first but none of the others. I have to close the database, open it again and then it will run the next. I don't think I want to have close the database between each.
I have 13 recipients and would like this to be automated. Any suggestions?

Thanks in advance!

 

[luceze]

Can you post your code?

 

[tjham]

This is the macro that I converted to VB. I only pasted 3 recipients...13 was way to much to post. Anyway, It basically opens and sends the 1st recipient. But, after that it only opens and closes the rest.

Function EmailSupervisorsSAVE()


DoCmd.RunMacro "RunTimeSheetPrepSupvEmail", , ""
DoCmd.OpenReport "TimeSheetEmail", acPreview, "timelogqry", "[empno]=""1234"" Or [empno]=""0123"""
DoCmd.SendObject acReport, "TimeSheetEmail", "SnapshotFormat(*.snp)", "1st Email Recipient", "", "", "Employee Time Sheet", "Please review and ""FORWARD"" your approval as soon as possible. Thank you, Sender", False, ""
DoCmd.Close acReport, "TimeSheetEmail"
DoCmd.OpenReport "TimeSheetEmail", acPreview, "timelogqry", "[empno]=""0010"""
DoCmd.SendObject acReport, "TimeSheetEmail", "SnapshotFormat(*.snp)", "2nd Email Recipient", "", "", "Employee Time Sheet", "Please review and ""FORWARD"" your approval as soon as possible. Thank you, Sender", False, ""
DoCmd.Close acReport, "TimeSheetEmail"
DoCmd.OpenReport "TimeSheetEmail", acPreview, "timelogqry", "[empno]=""0012"""
DoCmd.SendObject acReport, "TimeSheetEmail", "SnapshotFormat(*.snp)", "3rd Email Recipient", "", "", "Employee Time Sheet", "Please review and ""FORWARD"" your approval as soon as possible. Thank you, Sender", False, ""
DoCmd.Close acReport, "TimeSheetEmail"
End Function