471,355 Members | 1,585 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

Send emails to users stored on each record

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
6 2488
MMcCarthy
14,534 Expert Mod 8TB
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
slider
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
14,534 Expert Mod 8TB
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
slider
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
14,534 Expert Mod 8TB
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
32,405 Expert Mod 16PB
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.

Similar topics

4 posts views Thread by Thom McGrath | last post: by
reply views Thread by Nathan Truhan | last post: by
4 posts views Thread by =?Utf-8?B?dHBhcmtzNjk=?= | last post: by
5 posts views Thread by pat | last post: by

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.