By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

code not working

P: 72
Hi,
this code is fine but it doesnot loop. It only send one email (the top one on the list of the table). How to fix it?
thanks in advance

Private Sub Command0_Click()

Dim db As Database
Dim rs As DAO.Recordset
'Dont't forget to reference the Outlook Object Library
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Email FROM Table1")


Do Until rs.EOF
On Error GoTo ErrorHandler

With objEmail
.To = rs!Email
.Subject = "Happy Holidays"
.HTMLBody = "Greetings from CD <br><br>"
.Attachments.Add "P:\Greetings.jpg", olByValue, , "Stuff"


.Send
End With
ErrorHandler:

Resume Next

rs.MoveNext

Loop


End Sub
Dec 13 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,669
Hi,
this code is fine but it doesnot loop. It only send one email (the top one on the list of the table). How to fix it?
thanks in advance

Private Sub Command0_Click()

Dim db As Database
Dim rs As DAO.Recordset
'Dont't forget to reference the Outlook Object Library
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Email FROM Table1")


Do Until rs.EOF
On Error GoTo ErrorHandler

With objEmail
.To = rs!Email
.Subject = "Happy Holidays"
.HTMLBody = "Greetings from CD <br><br>"
.Attachments.Add "P:\Greetings.jpg", olByValue, , "Stuff"


.Send
End With
ErrorHandler:

Resume Next

rs.MoveNext

Loop


End Sub
Try this revised code and let me know how it works out:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2. On Error GoTo ErrorHandler
  3.  
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6.  
  7. 'Dont't forget to reference the Outlook Object Library
  8. Dim objOutlook As Outlook.Application
  9. Dim objEmail As Outlook.MailItem
  10.  
  11. Set objOutlook = CreateObject("Outlook.Application")
  12. Set objEmail = objOutlook.CreateItem(olMailItem)
  13.  
  14. Set db = CurrentDb
  15. Set rs = db.OpenRecordset("SELECT Email FROM Table1")
  16.  
  17. Do Until rs.EOF
  18.   With objEmail
  19.     .To = rs!Email
  20.     .Subject = "Happy Holidays"
  21.     .HTMLBody = "Greetings from CD <br><br>"
  22.     .Attachments.Add "P:\Greetings.jpg", olByValue, , "Stuff"
  23.       .Send
  24.   End With
  25.     rs.MoveNext
  26. Loop
  27.  
  28. Exit_Handler:
  29.   Exit Sub
  30.  
  31. ErrorHandler:
  32.   MsgBox Err.Description, vbExclamation, "Error in E-Mail Transmission"
  33.   Resume Exit_Handler
  34. End Sub
Dec 14 '07 #2

P: 72
Perfect. Thank you so much and have a happy Holidays
Dec 14 '07 #3

P: 72
Sorry, but it's still sending to one email only. also, I want to know if it's possible not to show the recipient's email address. I am sending this to 206 clients and we don't want to show everyone address.
thanks
Dec 14 '07 #4

Post your reply

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