How to send emails automatically from an access database query?

[MHutcheson81]

I have a MS Access 2010 db taht I want to send emails from automatically. I have the query setup but am getting stuck with the CDO VBA. They query is call 'qryEmails' and contains the following 4 fields:

ReturnCode\Sale sOrderNumber\Na me\EmailAddress

How do I get access to: 1. Loop through each record and send an email to each email address listed 2. In each email, have a message that will contain reference to the first 3 fields, so each message appears personalised 3. Have a dynamic subject, so the ReturnCode field is in each subject

I have been trying small steps at first, so far I am receiving 100's of emails to the same address. Here is my code (I have used XXX where I do not want to disclose info):

Dim rst As ADODB.Recordset 
Dim strSQL As String 
Dim strEmail As String 
Set rst = New ADODB.Recordset 
' strSQL = "[qryEmails]" 
'source of recordset rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic 
' Do While Not rst.EOF strEmail = rst.Fields("EmailAddress")
 
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Your refund is:" '
objMessage.FROM = """SENDER"" <[e-mail]>"
objMessage.To = rst.Fields("EmailAddress")
objMessage.TextBody = objMessage.TextBody & rst(1)
 
 
'==Add fields to email body
'Do While strEmail = rst.Fields("EmailAddress")
 
'rst.MoveNext
'If rst.EOF Then Exit Do
'Loop
' ========= SMTP server configuration
 
    objMessage.Configuration.Fields.Item _
     ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 
    'Name or IP of Remote SMTP Server
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "XXX"
 
    'Server port (typically 25)
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
 
    objMessage.Configuration.Fields.Update
 
    '==End remote SMTP server configuration section==
 
    'Send email
    objMessage.Send
    'Clear variable for next loop
    Set objMessage = Nothing
Loop
rst.Close Set rst = Nothing

Any ideas why this is sending 100's of emails? The query results so far is only returning two addresses for testing purposes.

Regards,

Michael

[zmbd]

How do I get access to: 1. Loop through each record and send an email to each email address listed 2. In each email, have a message that will contain reference to the first 3 fields, so each message appears personalised 3. Have a dynamic subject, so the ReturnCode field is in each subject

You have three questions. We ask that there be only one question per thread. I makes it easier to keep on topic and for other members to find the information they are looking when searching.

Please choose ONE and clearly describe what it is that you are having issues with in completing that goal. The more detail you provide the easier it is for us to help you.



Oh, and your posted code is not usable as given. Please post the actual working code

As for the commented out code

 
'==Add fields to email body
'Do While 
'strEmail = rst.Fields("EmailAddress")
'rst.MoveNext
'If rst.EOF Then Exit Do
'Loop

If this were to actually run with a valid recordset, fix the syntax, then every record with a value in [EmailAddress] would be added to the strEmail string.

You're also mising the "DO" for the "Loop" on line 43