sending a report via email

[dark11984]

Hi there,
I have a code that sends my report to recipients that exist in a query. At the moment the report has a parameter query where you have to enter the receipeients name to get the results for that recipient before sending. I want to be able to get rid of this part and not have to write in the recipients name. the recipients name exist in field(0). Is there anyway to do this?

I hope this is clear enough because i think i've confused myself.

Private Sub Command0_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("QryConfVend", dbOpenSnapshot)
 
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(1)) = False Then
sToName = .Fields(1)
sSubject = .Fields(2) & " " & "Order Confirmation Test" & " " & Format(Now, "DD-MMM-YYYY")
sMessageBody = "Hi There"
 
DoCmd.SendObject acSendReport, "RepOrdConf", "snapshot format", _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With
 
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

 

[ajetrumpet]

anytime you access a report with a param, you're stuck putting it in. you told it to do so. so....if you want a way around this, put a box or something on a control with all possible recipients it, choose one, then shoot it of to them that way. hence, popup will not show

 

[dark11984]

I'd prefer not to have to send each one individually. The parameter for the report is in my query isn't there some way VBA can tell it which criteria to enter?

Cheers.

 

[bulrush]

There is a property in a Combobox, I THINK, that allows you to select multiple records in it. If not, use a ListBox, which I know does have the SelectMultiple property. (I think it's called SelectMultiple.)