MS access conditional content of report for DoCmd.SendObject acSendReport

Question

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

Mark Mark · Accepted Answer · 2020-03-12T19:17:17

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

MS access conditional content of report for DoCmd.SendObject acSendReport

2 Answers