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

How to send emails automatically from an access database query?

P: 1
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\SalesOrderNumber\Name\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):

Expand|Select|Wrap|Line Numbers
  1. Dim rst As ADODB.Recordset 
  2. Dim strSQL As String 
  3. Dim strEmail As String 
  4. Set rst = New ADODB.Recordset 
  5. ' strSQL = "[qryEmails]" 
  6. 'source of recordset rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic 
  7. ' Do While Not rst.EOF strEmail = rst.Fields("EmailAddress")
  8.  
  9. Set objMessage = CreateObject("CDO.Message")
  10. objMessage.Subject = "Your refund is:" '
  11. objMessage.FROM = """SENDER"" <XXX@somewhere.com>"
  12. objMessage.To = rst.Fields("EmailAddress")
  13. objMessage.TextBody = objMessage.TextBody & rst(1)
  14.  
  15.  
  16. '==Add fields to email body
  17. 'Do While strEmail = rst.Fields("EmailAddress")
  18.  
  19. 'rst.MoveNext
  20. 'If rst.EOF Then Exit Do
  21. 'Loop
  22. ' ========= SMTP server configuration
  23.  
  24.     objMessage.Configuration.Fields.Item _
  25.      ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  26.  
  27.     'Name or IP of Remote SMTP Server
  28.     objMessage.Configuration.Fields.Item _
  29.     ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "XXX"
  30.  
  31.     'Server port (typically 25)
  32.     objMessage.Configuration.Fields.Item _
  33.     ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  34.  
  35.     objMessage.Configuration.Fields.Update
  36.  
  37.     '==End remote SMTP server configuration section==
  38.  
  39.     'Send email
  40.     objMessage.Send
  41.     'Clear variable for next loop
  42.     Set objMessage = Nothing
  43. Loop
  44. 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
Dec 16 '13 #1
Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,332
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. '==Add fields to email body
  3. 'Do While 
  4. 'strEmail = rst.Fields("EmailAddress")
  5. 'rst.MoveNext
  6. 'If rst.EOF Then Exit Do
  7. '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
Dec 17 '13 #2

Post your reply

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