MS access conditional content of report for DoCmd.SendObject acSendReport

I am sending emails from MS Access by outlook using following command

DoCmd.SendObject acSendReport, report_name, acFormatPDF, my_email, , my_email, "Weekly", "Sending you weekly report", True 

where report_name is a report based on a query qQuickReport.

But I do not want to send all records to all recipients. Each recipient should receive his own records from the report. Creating 5 separate queries and 5 forms based on this forms is one of the options, but I would prefer to do it "properly". So I plan to loop recipient ID in the query qQuickReport adding a following condition:

WHERE (((qALL.Koordinator)=[whichone])) 

and set parameter [whichone] by VBA code as follows:

DoCmd.SetParameter "whichone", "4" 

Following two lines work fine if I open only query qQuickReport, it contains only records belonging to recipient 4:

DoCmd.SetParameter "whichone", "4" DoCmd.OpenQuery ("qQuickReport") 

But If I try to use it as follows:

DoCmd.SetParameter "whichone", "4" DoCmd.SendObject acSendReport, report_name, acFormatPDF, my_email, , my_email, "Weekly", "Sending you weekly report", True 

it asks me to input parameter [whichone] interactively (window Enter Parameter Value), so it means that query under the Report did not receive a value of the parameter [which].

Am I doing anything wrong or simply Setparameter with SendReport is not supported?

Thanks, PH

2 Answers

I saw this and thought that maybe someone would benefit seeing how this would work without the setparameter way to approach this issue.

There's a table called tblsupplierCl, and a report called rptsupplier

This works by setting the report SupplierNbr equal to the iteration record in the recordset loop (SupplierNbr = " & rs!SupplierNbr). It avoids the OP's issue.

It mails each supplier only the items that they should see.

 msgbody1 = "255 characters" 'each message body is limited to 255 'characters. You can use multiple message bodies to get around that! Set rs = CurrentDb.OpenRecordset ("SELECT distinct supplierNbr, supplieremail " & _ "suppliertext FROM tblsupplierCl") If Not rs.EOF Then rs.MoveFirst Do DoCmd.OpenReport "rptsupplier", acViewPreview, , _ "SupplierNbr = " & rs!SupplierNbr, acHidden DoCmd.SendObject acSendReport, "rptsupplier", _ acFormatPDF, rs!Supplieremail, , , msgtitle, msgbody1, False DoEvents DoCmd.Close acReport, "rptsupplier", acSaveNo rs.MoveNext Loop While Not rs.EOF End If 

I'm new here, so if posting this breaks the rules I apologize

Mark

As specified in the docs, DoCmd.SetParameter is only available for BrowseTo, OpenForm, OpenQuery, OpenReport, or RunDataMacro. It's not supported for SendObject

The only real way to work around this, is by not using parameters. You can replace your parameter with a TempVar, for example, to allow setting it in code.