Send report with paramaters to a recordset.

[ferociousllama]

You all have been great on my previous question and i appreciate the help!
I'm working on creating code to run a query to get a list of mail addresses. Then loop through the mail addresses sending a report to each one. I got that to work but I want to use this with a report with parameters. The parameter I want set for each report is the mail I want it sent to...Not sure if that makes sense.

The recordset creates abmail for address1 I want the parameter for the report to be address1 then I want the code to loop to address2.

I hope this makes sense. here's the code I have so far but I'm not sure if Sendobject is going to work for this.

I kept getting a message about posting electronic mail addresses and it made me change all occurences of the shortened single word for electronic mail address. I changed the "E" to "g" in the code below.

Dim MyDB As DAO.Database
Dim rstgmail As DAO.Recordset
Dim strBuild As String
 
Set MyDB = CurrentDb
Set rstgmail = MyDB.OpenRecordset("Rt_gmailQ", dbOpenForwardOnly)
 
With rstgmail
  Do While Not .EOF
    strBuild = strBuild & ![gmail] & ";"        'Build the 'TO' String
      .MoveNext
  Loop
End With
 
If strBuild <> "" Then      'At least 1 Recipient
  strBuild = Left$(strBuild, Len(strBuild) - 1)      'Remove Trailing ';'
    DoCmd.SendObject acSendReport, "RaceresultsR", "HTML(*.html)", strBuild, "", "", "Race results", "", True, ""
End If
 
rstgmail.Close
Set rstgmail = Nothing

 

[pbaldy]

A few options. You can open the report filtered, then send, then close, opening using:

http://www.baldyweb.com/wherecondition.htm

You can add a criteria to the report's source query and populate that inside your loop. You can use this method to filter the report from within:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[ferociousllama]

That helped a lot. Thank you!

I almost have it. The only thing I'm having trouble with is getting the filter to work properly.

I can filter the report with the On Open event but I don't know how to reference the address that the loop is currently on.


Any guidance on this?

 

[pbaldy]

You can reference a form textbox and place the value in that textbox within the loop. Using the example from the link combined with yours:

Forms![Select Load List]![LoadID] = ![gmail]

 

[ferociousllama]

So I need to create a form that will have a text box populated by the address the code is currently on? I'm sorry if I'm just not getting it.


I ended up using some other code I already had in my database that I used to send emails. Here's what I ended up using:

Sub WeeklyReport()

 
Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set MyDB = CurrentDb
Set qdf = MyDB.QueryDefs("Weekly Report Contact List")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next
Set rsEmail = qdf.OpenRecordset()
Set oLook = CreateObject("Outlook.Application")
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
        myRecipient = .Fields(0)

DoCmd.SendObject acReport, "Weekly Report Received Invoices", "PDFFormat(*.pdf)", myRecipient, "", "", "This is subject", "This is message", True, ""


                    .MoveNext
        Loop
End With
Set oMail = Nothing
Set oLook = Nothing
End Sub

 

[pbaldy]

I don't see anything there that would filter the report to the email address.

 

[ferociousllama]

There is nothing there that would filter to the email address.

I wasn't understanding for sure what you were saying about filtering but after a short break I figured it out.

It works perfect now. I just need to put in some code to open the form before the loop starts then close it when it's done. That's easy enough.

You're awesome! Thank you!

 

[pbaldy]

Happy to help! An alternative to the form is a public variable. Same principle; populate it in your loop, use it in the report's open code.