473,320 Members | 1,991 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 9052
RuralGuy
375 Expert 256MB
My favorite email link: http://www.granite.ab.ca/access/email.htm
Jan 21 '11 #2
ADezii
8,834 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,834 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, 309 views)
Jan 22 '11 #6
ADezii
8,834 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,834 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,834 Expert 8TB
You are quite welcome. The problem was in resolving the Query Parameter Value.
Jan 23 '11 #11

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

Similar topics

1
by: james00_c | last post by:
Greetings- I need to pass multiple email addresses to a "sendto" hidden field in a form. I need to do that because "CC" and "BCC" are not an option. One address webmaster@xyz.com would be...
3
by: jdph40 | last post by:
I'm using Access 97. In the OnClick event of a button I have the code below. I looks at txtEmail and sends an e-mail message without further action from the user (user does not have to click Send...
6
by: toufik | last post by:
Hi, I've a crystal reports Report, Is it possible to send it by email? how? Thanks
1
by: Arne Beruldsen | last post by:
I have a customer database with email addresses. How do I use WebMail to send out a monthly newsletter? Thanks...Arne
1
by: TCB | last post by:
How can I separate multiple email addresses entered in a single textbox, the email addresses are separated by (,) or (;) Thanks
1
by: mrajanikrishna | last post by:
Hello Friends, I am generating two reports but two files. First report is Shipment Booking Form. After generating this form, I want to send to the cargo management by email. As far everything...
1
by: donet programmer | last post by:
Is there a way to map multiple email addresses, for instance emailadd1@domain.com, emailadd2@domain.com.. etc to one email address emailadd@domain.com such that when an email is sent to emailadd1...
0
by: krouxsa | last post by:
Hi There people... Please help me with this query. I want to send 1 email to multiple email addresses stored in a database. Will it be possible? I'm using SQL Server 2005 and already set up...
3
by: dragonfly8563 | last post by:
Hi all, I created a listbox with employees' names and email addresses in it, and a button which grabs email address once it's clicked. Currently I can only pick up one address at a time, but I...
21
by: farisallil | last post by:
Hi I have a table called Info with a column called Email Address in it. I want to create a command button in a form called Customer ,which has the Info table as the Record Source, and I want this...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.