Send emails to users stored on each record

[slider]

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:

Public Sub SendMessage()
 
Dim ol As Outlook.Application
Dim msg As MailItem
Dim rec As DAO.Recordset
Dim intCount As Integer
Dim intLoop As Integer
 
 
 
 
 
Set ol = New Outlook.Application
 
Set rec = CurrentDb.OpenRecordset("q1")
If Not (rec.BOF And rec.EOF) Then
rec.MoveLast
rec.MoveFirst
intCount = rec.RecordCount
For intLoop = 1 To intCount
Set msg = ol.CreateItem(olMailItem)
msg.Subject = "ENTER YOUR SUBJECT HERE"
msg.To = rec("Email")
msg.Body =
msg.Send
Set msg = Nothing
rec.MoveNext
Next intLoop
End If
 
Set rec = Nothing
Set ol = Nothing
 
End Sub
 

[MMcCarthy]

Try this ...

Public Sub SendMessage()
 
Dim ol As Outlook.Application
Dim msg As MailItem
Dim rec As DAO.Recordset
Dim intCount As Integer
Dim intLoop As Integer
 
Set ol = New Outlook.Application
 
Set rec = CurrentDb.OpenRecordset("q1")
If Not (rec.BOF And rec.EOF) Then
rec.MoveLast
rec.MoveFirst
intCount = rec.RecordCount
For intLoop = 1 To intCount
Set msg = ol.CreateItem(olMailItem)
msg.Subject = "ENTER YOUR SUBJECT HERE"
msg.To = rec("Email")
msg.Body = "Dear " & rec("User") & "," & vbCrLf & "Your current quota is: " & _
rec("quota") & "." & vbCrLf & "Everthing else you want to say."
msg.Send
Set msg = Nothing
rec.MoveNext
Next intLoop
End If
 
Set rec = Nothing
Set ol = Nothing
 
End Sub
 

Mary

[slider]

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

[MMcCarthy]

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

[slider]

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?

[MMcCarthy]

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

[NeoPa]

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.