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

Send emails to users stored on each record

P: 27
Hey all i am using Access 2003 on Win Xp and i have been trying to make this code send emails to the users stored on each record. So far is works, except in the body of the email i need it to say a number of things, such as Dear (user), your current quota is (quota), and so on.

So far i can send the emails to multiple users but when it comes down to writing the message i cannot enter in more than one "rec("field)".

In my database i have four fields which are labeled Email, User, Name, Surname.

This code is VB btw

Thank you

Here is the code:

Expand|Select|Wrap|Line Numbers
  1. Public Sub SendMessage()
  2.  
  3. Dim ol As Outlook.Application
  4. Dim msg As MailItem
  5. Dim rec As DAO.Recordset
  6. Dim intCount As Integer
  7. Dim intLoop As Integer
  8.  
  9.  
  10.  
  11.  
  12.  
  13. Set ol = New Outlook.Application
  14.  
  15. Set rec = CurrentDb.OpenRecordset("q1")
  16. If Not (rec.BOF And rec.EOF) Then
  17. rec.MoveLast
  18. rec.MoveFirst
  19. intCount = rec.RecordCount
  20. For intLoop = 1 To intCount
  21. Set msg = ol.CreateItem(olMailItem)
  22. msg.Subject = "ENTER YOUR SUBJECT HERE"
  23. msg.To = rec("Email")
  24. msg.Body =
  25. msg.Send
  26. Set msg = Nothing
  27. rec.MoveNext
  28. Next intLoop
  29. End If
  30.  
  31. Set rec = Nothing
  32. Set ol = Nothing
  33.  
  34. End Sub
  35.  
Feb 7 '07 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Public Sub SendMessage()
  2.  
  3. Dim ol As Outlook.Application
  4. Dim msg As MailItem
  5. Dim rec As DAO.Recordset
  6. Dim intCount As Integer
  7. Dim intLoop As Integer
  8.  
  9. Set ol = New Outlook.Application
  10.  
  11. Set rec = CurrentDb.OpenRecordset("q1")
  12. If Not (rec.BOF And rec.EOF) Then
  13. rec.MoveLast
  14. rec.MoveFirst
  15. intCount = rec.RecordCount
  16. For intLoop = 1 To intCount
  17. Set msg = ol.CreateItem(olMailItem)
  18. msg.Subject = "ENTER YOUR SUBJECT HERE"
  19. msg.To = rec("Email")
  20. msg.Body = "Dear " & rec("User") & "," & vbCrLf & "Your current quota is: " & _
  21. rec("quota") & "." & vbCrLf & "Everthing else you want to say."
  22. msg.Send
  23. Set msg = Nothing
  24. rec.MoveNext
  25. Next intLoop
  26. End If
  27.  
  28. Set rec = Nothing
  29. Set ol = Nothing
  30.  
  31. End Sub
  32.  
Mary
Feb 7 '07 #2

P: 27
Hey! Thank you so much! Your so awesome! Ive been going nuts for the past few days trying to figure it out. I did try using the "&" but i was typing "and"! haha silly me

once again thank you and im sure i will be back for more answers

Jordy
Feb 7 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hey! Thank you so much! Your so awesome! Ive been going nuts for the past few days trying to figure it out. I did try using the "&" but i was typing "and"! haha silly me

once again thank you and im sure i will be back for more answers

Jordy
You're Welcome Jordy.

Glad it's working for you.

Mary
Feb 7 '07 #4

P: 27
hmm well yes it works, the only problem now is that i have to wait 5 seconds and click yes for every email that gets sent...im using Microsoft Outlook 2003 and its some security feature.

is there anyway around this or anyway i can change the code to use another program or bypass the security?
Feb 7 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
hmm well yes it works, the only problem now is that i have to wait 5 seconds and click yes for every email that gets sent...im using Microsoft Outlook 2003 and its some security feature.

is there anyway around this or anyway i can change the code to use another program or bypass the security?
Unfortunately, this is not a straightforward question. Have a look at this MSDN site.

Mary
Feb 7 '07 #6

NeoPa
Expert Mod 15k+
P: 31,494
If you dig around in here there are threads that use a different technique for sending e-mails. I would guess they send via SMTP. If so, there are different methods of combatting spam on a network for SMTP. So you wouldn't have the 5 second delay - but they may be blocked completely depending on how your network has been set up.
Feb 7 '07 #7

Post your reply

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