Auto generate email to send report with parameters

[klnlsu]

I have a list box on a form that lets me choose invoices that I want to print. This is the code is use to create the parameter that lets me print just the selected invoices in the list box:

Dim strwhere As String
If (IsNull(List9.Value) And List9.ItemsSelected.Count = 0) Then 'NO ORDERS SELECTED
strwhere = ParseWhere
Else
Dim I As Variant
strwhere = ""
For Each I In List9.ItemsSelected
strwhere = strwhere & "ord_no = '" & List9.ItemData(I) & "'" & " OR "
Next
strwhere = Left$(strwhere, Len(strwhere) - 3)


End If

DoCmd.OpenReport "Order Confirmation", acPreview, , strwhere

This is fairly simple and straightforward and works great.

Now I want to create a button that will allow the user to send this report with parameters in an auto generated email. I would like to use the DoCmd.SendObject acSendReport statement for this, but from what I have gathered I cannot attach a report with parameters using this method. Can someone guide me to a solution?

[ranman256]

you would only send the report,
the query of the report would have the params.

Are the params settings/controls on a form?



'alter the query with the criteria....

'----------------
sub btnOpenQry_click()
'----------------
dim sWhere as string 
dim qdf as querydef

sWhere = " where 1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

set qdf = currentdb.querydefs("qsMyQry")
qdf.sql = "Select * from table " & swhere
qdf.close
docmd.openquery qdf.name

end sub

[pbaldy]

You can actually use SendObject right after the OpenReport line. It will (should) send the filtered report. So your code would be

DoCmd.OpenReport "Order Confirmation", acPreview, , strwhere
DoCmd.SendObject...
DoCmd.Close... 'close the report here

[klnlsu]

The parameters are not saved on the form. Is there a way to reference selected items in a listbox in a query? I couldn't find a way.

I will have to study up on querydefs because I have never used them. Thank you for the information.

[klnlsu]

WOW! That worked! Thank you, pbaldy, for a very simple solution!

[pbaldy]

Happy to help!