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

Send Individual Emails based on Query

P: n/a
Hi all,

Thank you already for the amount of information that has collected in
this group, very helpful indeed. I have a question in regards to
setting up individual emails to be sent based on a query:

What I currently have is a setup which is pretty much identical to this
(on a standalone access database, not network oriented, single user):

=====================================
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String

strRecipient = "emailaddr...@domain.com"
strSubject = "A New Vendor PO Has Been Entered, PO Number " &
Me![PO_Nbr]
strMessageBody = "This is to notify you that a new Vendor PO has
been entered." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "PO Number " &
Me![PO_Nbr] & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "This Email has been
generated from the Vendor Repair PO Database, Please DO NOT reply."

DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
=====================================

What I would like to add however is a script/macro/code which runs when
I open the database in the morning and sends out individual emails
(just like the above), however picking out the records which match a
query. I am not sure what the best way is to go around this and if it's
even possible? I thank you in advance for any help you can provide (I
would also populate a field when the email is sent, so that it doesn't
go out again the next morning. Query depends on days outstanding, i.e.
email is a reminder which is sent at various intervals)

Kind Regards,

Noel

Dec 14 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a

no*******@gmail.com wrote:
Hi all,

Thank you already for the amount of information that has collected in
this group, very helpful indeed. I have a question in regards to
setting up individual emails to be sent based on a query:

What I currently have is a setup which is pretty much identical to this
(on a standalone access database, not network oriented, single user):

=====================================
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String

strRecipient = "emailaddr...@domain.com"
strSubject = "A New Vendor PO Has Been Entered, PO Number " &
Me![PO_Nbr]
strMessageBody = "This is to notify you that a new Vendor PO has
been entered." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "PO Number " &
Me![PO_Nbr] & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "This Email has been
generated from the Vendor Repair PO Database, Please DO NOT reply."

DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
=====================================

What I would like to add however is a script/macro/code which runs when
I open the database in the morning and sends out individual emails
(just like the above), however picking out the records which match a
query. I am not sure what the best way is to go around this and if it's
even possible? I thank you in advance for any help you can provide (I
would also populate a field when the email is sent, so that it doesn't
go out again the next morning. Query depends on days outstanding, i.e.
email is a reminder which is sent at various intervals)

Kind Regards,

Noel
Open a recordset off the query and then loop through the recordset.
then use the SendObject command inside the loop.

Something along the lines of :
'---UNTESTED AIR CODE
Sub SendAllMessages()
dim qdf as dao.querydef
dim rst as dao.recordset

set qdf=DBEngine(0)(0).Querydefs("YourSelectQuery")
set rst=qdf.OpenRecordset

do until rst.EOF
DoCmd.SendObject acSendNoObject,,, rst.Fields("EMailAddress")
'<--Fill in the args as necessary from the recordset
rst.MoveNext
Loop

rst.Close
set rst=nothing
set qdf=nothing
End sub

Dec 15 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.