Send Individual Emails based on Query

[noel.tock]

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 = "[e-mail]"
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

[pietlinden]

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 = "[e-mail]"
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