code not working

  • Thread starter Thread starter ielmrani via AccessMonster.com
  • Start date Start date
I

ielmrani via AccessMonster.com

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
 
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
Click to expand...
Just a little reordering should do it:

Private Sub Command0_Click()

On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

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

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

Do While Not rs.EOF
With objEmail
.To = rs!Email
.Subject = "Happy Holidays"
.HTMLBody = "Greetings from CD <br><br>"
.Attachments.Add "P:\Greetings.jpg", olByValue, , "Stuff"
.Send
End With
rs.MoveNext
Loop

Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume Next

End Sub


mfG
--> stefan <--
 
thanks for your quick reply. But It's still sending to only one email
address
 
It only send one email
You only create one email. If you want to create more than one then I would
think that
Dim objEmail As Outlook.MailItem
would need to be *inside* the loop (immediately preceding "With objEmail")

Also: "Resume Next" is preventing any errors from making themselves known,
so you have no way to address/debug them.
As written, once an error occurs the code will simply keep going, looping
with a probable error on every line, doing absolutely nothing.
 
It worked. thank you.
George said:
It only send one email
Click to expand...
You only create one email. If you want to create more than one then I would
think that
Dim objEmail As Outlook.MailItem
would need to be *inside* the loop (immediately preceding "With objEmail")

Also: "Resume Next" is preventing any errors from making themselves known,
so you have no way to address/debug them.
As written, once an error occurs the code will simply keep going, looping
with a probable error on every line, doing absolutely nothing.
thanks for your quick reply. But It's still sending to only one email
address
Click to expand...
[quoted text clipped - 37 lines]
Click to expand...
 
Back
Top