469,306 Members | 1,982 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

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

547 512MB
i would like to send a specific report "RaceresultsR", 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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SendObject acReport, "RaceresultsR", "HTML(*.html)", "", "", "", "Race results", "", True, ""
Jan 21 '11 #1

✓ answered by ADezii

Try this Revision:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstEMail As DAO.Recordset
  3. Dim strBuild As String
  4. Dim strSQL As String
  5.  
  6. strSQL = "SELECT Cyclist.Surname, Cyclist.Name, Cyclist.City, Cyclist.Email, RaceEntry.RaceDate " & _
  7.          "FROM Cyclist INNER JOIN RaceEntry ON Cyclist.IDNo = RaceEntry.IDNo " & _
  8.          "WHERE Cyclist.Email Is Not Null And RaceEntry.Racedate = #" & [Forms]![Rt_Reportsf]![Racedate] & "# " & _
  9.          "ORDER BY Cyclist.Surname;"
  10.  
  11. Set MyDB = CurrentDb
  12. Set rstEMail = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  13.  
  14. With rstEMail
  15.   Do While Not .EOF
  16.     strBuild = strBuild & ![email] & ";"        'Build the 'TO' String
  17.       .MoveNext
  18.   Loop
  19. End With
  20.  
  21. If strBuild <> "" Then      'At least 1 Recipient
  22.   strBuild = Left$(strBuild, Len(strBuild) - 1)      'Remove Trailing ';'
  23.     DoCmd.SendObject acSendReport, "RaceresultsR", "HTML(*.html)", strBuild, "", "", "Race results", "", True, ""
  24. End If
  25.  
  26. rstEMail.Close
  27. Set rstEMail = Nothing

10 8724
RuralGuy
375 Expert 256MB
My favorite email link: http://www.granite.ab.ca/access/email.htm
Jan 21 '11 #2
ADezii
8,800 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstEMail As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set rstEMail = MyDB.OpenRecordset("Rt_EmailQ", dbOpenForwardOnly)
  6.  
  7. With rstEMail
  8.   Do While Not .EOF
  9.     DoCmd.SendObject acSendReport, "RaceresultsR", "HTML(*.html)", ![email], "", "", "Race results", "", True, ""
  10.       .MoveNext
  11.   Loop
  12. End With
  13.  
  14. rstEMail.Close
  15. Set rstEMail = Nothing
Jan 22 '11 #3
neelsfer
547 512MB
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 "RaceresultsR".
I then click on the send button and off it go.
Thx for the trouble so far. Is it possible to do this?
Jan 22 '11 #4
ADezii
8,800 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstEMail As DAO.Recordset
  3. Dim strBuild As String
  4.  
  5. Set MyDB = CurrentDb
  6. Set rstEMail = MyDB.OpenRecordset("Rt_EmailQ", dbOpenForwardOnly)
  7.  
  8. With rstEMail
  9.   Do While Not .EOF
  10.     strBuild = strBuild & ![email] & ";"        'Build the 'TO' String
  11.       .MoveNext
  12.   Loop
  13. End With
  14.  
  15. If strBuild <> "" Then      'At least 1 Recipient
  16.   strBuild = Left$(strBuild, Len(strBuild) - 1)      'Remove Trailing ';'
  17.     DoCmd.SendObject acSendReport, "RaceresultsR", "HTML(*.html)", strBuild, "", "", "Race results", "", True, ""
  18. End If
  19.  
  20. rstEMail.Close
  21. Set rstEMail = Nothing
Jan 22 '11 #5
neelsfer
547 512MB
here is an attachment in 2003 if it may help. thx
Attached Files
File Type: zip EmailSend 2003 probl.zip (455.0 KB, 285 views)
Jan 22 '11 #6
ADezii
8,800 Expert 8TB
@neelsfer - No need, the Code in Post #5 will work.
Jan 22 '11 #7
neelsfer
547 512MB
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 "RaceresultsR" attachment. Please have a look for me. thx
Jan 22 '11 #8
ADezii
8,800 Expert 8TB
Try this Revision:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstEMail As DAO.Recordset
  3. Dim strBuild As String
  4. Dim strSQL As String
  5.  
  6. strSQL = "SELECT Cyclist.Surname, Cyclist.Name, Cyclist.City, Cyclist.Email, RaceEntry.RaceDate " & _
  7.          "FROM Cyclist INNER JOIN RaceEntry ON Cyclist.IDNo = RaceEntry.IDNo " & _
  8.          "WHERE Cyclist.Email Is Not Null And RaceEntry.Racedate = #" & [Forms]![Rt_Reportsf]![Racedate] & "# " & _
  9.          "ORDER BY Cyclist.Surname;"
  10.  
  11. Set MyDB = CurrentDb
  12. Set rstEMail = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  13.  
  14. With rstEMail
  15.   Do While Not .EOF
  16.     strBuild = strBuild & ![email] & ";"        'Build the 'TO' String
  17.       .MoveNext
  18.   Loop
  19. End With
  20.  
  21. If strBuild <> "" Then      'At least 1 Recipient
  22.   strBuild = Left$(strBuild, Len(strBuild) - 1)      'Remove Trailing ';'
  23.     DoCmd.SendObject acSendReport, "RaceresultsR", "HTML(*.html)", strBuild, "", "", "Race results", "", True, ""
  24. End If
  25.  
  26. rstEMail.Close
  27. Set rstEMail = Nothing
Jan 22 '11 #9
neelsfer
547 512MB
works great! thx mr Adezzi.
Jan 23 '11 #10
ADezii
8,800 Expert 8TB
You are quite welcome. The problem was in resolving the Query Parameter Value.
Jan 23 '11 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by toufik | last post: by
1 post views Thread by Arne Beruldsen | last post: by
1 post views Thread by TCB | last post: by
1 post views Thread by mrajanikrishna | last post: by
1 post views Thread by donet programmer | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.