473,322 Members | 1,241 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,322 software developers and data experts.

How to send emails automatically from an access database query?

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
1 4705
zmbd
5,501 Expert Mod 4TB
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

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

Similar topics

5
by: powerrun | last post by:
Help! I have tons of info loaded into an excel spreadsheet that I need to transfer into an existing database in access. There is a specific Query I need the information to transfer to. This...
1
by: Bj?rn Keil | last post by:
Hi, first of all I have to say I'am new to MS Access, I'm used to database systems like Oracle or MySQL. I created a simple database consisting of a single table and a form to fill in new entrys...
1
by: gpaille | last post by:
I got a big Access database I'd like to convert to MS SQL Server 2000 and I'm looking for an efficient automated tool to convert data (over 1 400 tables), query, forms and interface. Does...
1
by: nilsonj | last post by:
Trying to set up an admin page for an online training registration page. I am having trouble with a sql statement. Here is the info on the tables. I have two tables: classPerson and reglist ...
23
by: Bosnoval | last post by:
Access 2003 Databse ASP.NET 2 Win 2003 Server Currently I have a search page that only allows a single keyword (person) to be searched at a time. I thought upgrading that option via a multiple...
3
by: drnorbert | last post by:
What is the best way to receive and send emails in MsAccess? Could this be done without Outlook automatation?
5
by: harky | last post by:
Hi, I am looking for help with a project. I am looking for a database to show me who has not payed thier bill after 30 days and if this could be a pop message. I need it to be on a webpage then i...
1
by: Eglute | last post by:
Hello I want to show the results of MS Access database query in Visual basic. Do you know how to do this? I want to show the results in objects such as label or text box. Please help me. My code is:...
1
by: ng250009 | last post by:
I am using access database .i want to populate a query which includes i mean which call a function in the query.Finally i want the exact query which populates the variables and aswellas the function...
1
by: hrprabhu | last post by:
Hi All, How do you Send SMS from Access database? I received a SMS from my dentist and the receptionist told me that the database sent it. I was wondering how it is done. Raghu Prabhu
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...
1
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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
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.