How to send a report to multiple email addresses from a query with sendobject?

[neelsfer]

i would like to send a specific report "Raceresult sR", to the email addresses listed in a query called Rt_EmailQ and the field name in query is called "Email"
The code below only show a blank "To" block to add addresses manually
please help

DoCmd.SendObject acReport, "RaceresultsR", "HTML(*.html)", "", "", "", "Race results", "", True, ""

[RuralGuy]

My favorite email link: http://www.granite.ab.ca/access/email.htm

[ADezii]

Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset

Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Rt_EmailQ", dbOpenForwardOnly)

With rstEMail
  Do While Not .EOF
    DoCmd.SendObject acSendReport, "RaceresultsR", "HTML(*.html)", ![Email], "", "", "Race results", "", True, ""
      .MoveNext
  Loop
End With

rstEMail.Close
Set rstEMail = Nothing

[neelsfer]

Thx Mr Adezzi;
It only adds the first email address in the Rt_EmailQ query to the "To" email column of the email to be send.

I would like the code to take all the email addresses (in "email" text field) listed in this Rt_EmailQ (query), add it to the "To" column in the email where the addresses are separated by ";" for the report called "Raceresult sR".
I then click on the send button and off it go.
Thx for the trouble so far. Is it possible to do this?

[ADezii]

Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
Dim strBuild As String
  
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Rt_EmailQ", dbOpenForwardOnly)
  
With rstEMail
  Do While Not .EOF
    strBuild = strBuild & ![email] & ";"        '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

rstEMail.Close
Set rstEMail = Nothing

[neelsfer]

attachment

here is an attachment in 2003 if it may help. thx

[ADezii]

@neelsfer - No need, the Code in Post #5 will work.

[neelsfer]

If you open the file i attached, it does not add the different emails addresses from the Rt-EmailQ query, to the same email with the "Raceresult sR" attachment. Please have a look for me. thx

[ADezii]

Try this Revision:

Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
Dim strBuild As String
Dim strSQL As String

strSQL = "SELECT Cyclist.Surname, Cyclist.Name, Cyclist.City, Cyclist.Email, RaceEntry.RaceDate " & _
         "FROM Cyclist INNER JOIN RaceEntry ON Cyclist.IDNo = RaceEntry.IDNo " & _
         "WHERE Cyclist.Email Is Not Null And RaceEntry.Racedate = #" & [Forms]![Rt_Reportsf]![Racedate] & "# " & _
         "ORDER BY Cyclist.Surname;"
  
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  
With rstEMail
  Do While Not .EOF
    strBuild = strBuild & ![Email] & ";"        '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
  
rstEMail.Close
Set rstEMail = Nothing

[neelsfer]

works great! thx mr Adezzi.

[ADezii]

You are quite welcome. The problem was in resolving the Query Parameter Value.