Send email to multiple recipients on query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have access 2003 running on an MSSQL db. My db has "owners" with email
addresses in the table. I would like to run a query on the owners' workflow
and send summarized (lists in emails) to each owner based on the result. I
would also like it to be actioned similar to a stored procedure, on a regular
timed interval schedule. I would also like to be able to add additional text
to the the email.

If anyone has some code samples, I will be extremely grateful.

Regards,

Layne
 
I didn't fully understand the 'lists in emails' bit, but to get you
started, the most basic SUB would like this

Sub sendEmail()

Dim appOutlook as Outlook.Application
Dim rs = DAO.RecordSet
Dim strSQL as string


strSQL = [SQL Statement -OR- Saved query]
Set rs = CurrentDB.OpenRecordSet(strSQL, dbOpenForwardOnly)
Set appOutlook = CreateObject("Outlook.Application")

While not rs.EOF
Set newMail = appOutlook.CreateItem(olMailItem)
newMail.To = "EMAIL ADDRESS" or rs.Fields("EmailAddress")
newMail.Subject = "EMAIL SUBJECT"
newMail.Body = "BODY TEXT"
newMail.Send
Set newMail = Nothing
rs.MoveNext
wend
rs.Close

Set appOutlook = Nothing

end sub

You'll have to update the .To and .Subject properties accordingly to
your specific application. Also, in order to DISPLAY the MailItem change
the newMail.Send to newMail.Display. Once you hit SEND on the MailItem
toolbar, the loop should continue. I've never actually interrupted a
loop as such I can't predict what would happen if you simply CLOSE the
window without SENDING. (It should just continue the loop - theoretically.)

As to automating the sub, I would run the SUB from within Outlook using
a Task to schedule it. I haven't actually done that myself, but I know
that it is possible and that the code should be fairly simple.

David H
 
One question...Where do I put this code?


Regards,

Layne

David C. Holley said:
I didn't fully understand the 'lists in emails' bit, but to get you
started, the most basic SUB would like this

Sub sendEmail()

Dim appOutlook as Outlook.Application
Dim rs = DAO.RecordSet
Dim strSQL as string


strSQL = [SQL Statement -OR- Saved query]
Set rs = CurrentDB.OpenRecordSet(strSQL, dbOpenForwardOnly)
Set appOutlook = CreateObject("Outlook.Application")

While not rs.EOF
Set newMail = appOutlook.CreateItem(olMailItem)
newMail.To = "EMAIL ADDRESS" or rs.Fields("EmailAddress")
newMail.Subject = "EMAIL SUBJECT"
newMail.Body = "BODY TEXT"
newMail.Send
Set newMail = Nothing
rs.MoveNext
wend
rs.Close

Set appOutlook = Nothing

end sub

You'll have to update the .To and .Subject properties accordingly to
your specific application. Also, in order to DISPLAY the MailItem change
the newMail.Send to newMail.Display. Once you hit SEND on the MailItem
toolbar, the loop should continue. I've never actually interrupted a
loop as such I can't predict what would happen if you simply CLOSE the
window without SENDING. (It should just continue the loop - theoretically.)

As to automating the sub, I would run the SUB from within Outlook using
a Task to schedule it. I haven't actually done that myself, but I know
that it is possible and that the code should be fairly simple.

David H
I have access 2003 running on an MSSQL db. My db has "owners" with email
addresses in the table. I would like to run a query on the owners' workflow
and send summarized (lists in emails) to each owner based on the result. I
would also like it to be actioned similar to a stored procedure, on a regular
timed interval schedule. I would also like to be able to add additional text
to the the email.

If anyone has some code samples, I will be extremely grateful.

Regards,

Layne
Click to expand...
Click to expand...
 
This is something that you have to determine for yourself. Most likely
it will be in the onClick event for a command button on a form.
One question...Where do I put this code?


Regards,

Layne

:

I didn't fully understand the 'lists in emails' bit, but to get you
started, the most basic SUB would like this

Sub sendEmail()

Dim appOutlook as Outlook.Application
Dim rs = DAO.RecordSet
Dim strSQL as string


strSQL = [SQL Statement -OR- Saved query]
Set rs = CurrentDB.OpenRecordSet(strSQL, dbOpenForwardOnly)
Set appOutlook = CreateObject("Outlook.Application")

While not rs.EOF
Set newMail = appOutlook.CreateItem(olMailItem)
newMail.To = "EMAIL ADDRESS" or rs.Fields("EmailAddress")
newMail.Subject = "EMAIL SUBJECT"
newMail.Body = "BODY TEXT"
newMail.Send
Set newMail = Nothing
rs.MoveNext
wend
rs.Close

Set appOutlook = Nothing

end sub

You'll have to update the .To and .Subject properties accordingly to
your specific application. Also, in order to DISPLAY the MailItem change
the newMail.Send to newMail.Display. Once you hit SEND on the MailItem
toolbar, the loop should continue. I've never actually interrupted a
loop as such I can't predict what would happen if you simply CLOSE the
window without SENDING. (It should just continue the loop - theoretically.)

As to automating the sub, I would run the SUB from within Outlook using
a Task to schedule it. I haven't actually done that myself, but I know
that it is possible and that the code should be fairly simple.

David H
I have access 2003 running on an MSSQL db. My db has "owners" with email
addresses in the table. I would like to run a query on the owners' workflow
and send summarized (lists in emails) to each owner based on the result. I
would also like it to be actioned similar to a stored procedure, on a regular
timed interval schedule. I would also like to be able to add additional text
to the the email.

If anyone has some code samples, I will be extremely grateful.

Regards,

Layne
Click to expand...
Click to expand...
Click to expand...
 
The reason I ask is that this is going to be a global type instruction. So,
shouldn't it go in one of the modules instead of one of the forms?

Layne

David C. Holley said:
This is something that you have to determine for yourself. Most likely
it will be in the onClick event for a command button on a form.
One question...Where do I put this code?


Regards,

Layne

:

I didn't fully understand the 'lists in emails' bit, but to get you
started, the most basic SUB would like this

Sub sendEmail()

Dim appOutlook as Outlook.Application
Dim rs = DAO.RecordSet
Dim strSQL as string


strSQL = [SQL Statement -OR- Saved query]
Set rs = CurrentDB.OpenRecordSet(strSQL, dbOpenForwardOnly)
Set appOutlook = CreateObject("Outlook.Application")

While not rs.EOF
Set newMail = appOutlook.CreateItem(olMailItem)
newMail.To = "EMAIL ADDRESS" or rs.Fields("EmailAddress")
newMail.Subject = "EMAIL SUBJECT"
newMail.Body = "BODY TEXT"
newMail.Send
Set newMail = Nothing
rs.MoveNext
wend
rs.Close

Set appOutlook = Nothing

end sub

You'll have to update the .To and .Subject properties accordingly to
your specific application. Also, in order to DISPLAY the MailItem change
the newMail.Send to newMail.Display. Once you hit SEND on the MailItem
toolbar, the loop should continue. I've never actually interrupted a
loop as such I can't predict what would happen if you simply CLOSE the
window without SENDING. (It should just continue the loop - theoretically.)

As to automating the sub, I would run the SUB from within Outlook using
a Task to schedule it. I haven't actually done that myself, but I know
that it is possible and that the code should be fairly simple.

David H

Layne wrote:

I have access 2003 running on an MSSQL db. My db has "owners" with email
addresses in the table. I would like to run a query on the owners' workflow
and send summarized (lists in emails) to each owner based on the result. I
would also like it to be actioned similar to a stored procedure, on a regular
timed interval schedule. I would also like to be able to add additional text
to the the email.

If anyone has some code samples, I will be extremely grateful.

Regards,

Layne
Click to expand...
Click to expand...
Click to expand...
 
Sorry about that. I myself would put in a free standing module (as
opposed to a form module) since there might be situations where its
called from multiple forms and whatnot.
The reason I ask is that this is going to be a global type instruction. So,
shouldn't it go in one of the modules instead of one of the forms?

Layne

:

This is something that you have to determine for yourself. Most likely
it will be in the onClick event for a command button on a form.
One question...Where do I put this code?


Regards,

Layne

:



I didn't fully understand the 'lists in emails' bit, but to get you
started, the most basic SUB would like this

Sub sendEmail()

Dim appOutlook as Outlook.Application
Dim rs = DAO.RecordSet
Dim strSQL as string


strSQL = [SQL Statement -OR- Saved query]
Set rs = CurrentDB.OpenRecordSet(strSQL, dbOpenForwardOnly)
Set appOutlook = CreateObject("Outlook.Application")

While not rs.EOF
Set newMail = appOutlook.CreateItem(olMailItem)
newMail.To = "EMAIL ADDRESS" or rs.Fields("EmailAddress")
newMail.Subject = "EMAIL SUBJECT"
newMail.Body = "BODY TEXT"
newMail.Send
Set newMail = Nothing
rs.MoveNext
wend
rs.Close

Set appOutlook = Nothing

end sub

You'll have to update the .To and .Subject properties accordingly to
your specific application. Also, in order to DISPLAY the MailItem change
the newMail.Send to newMail.Display. Once you hit SEND on the MailItem
toolbar, the loop should continue. I've never actually interrupted a
loop as such I can't predict what would happen if you simply CLOSE the
window without SENDING. (It should just continue the loop - theoretically.)

As to automating the sub, I would run the SUB from within Outlook using
a Task to schedule it. I haven't actually done that myself, but I know
that it is possible and that the code should be fairly simple.

David H

Layne wrote:


I have access 2003 running on an MSSQL db. My db has "owners" with email
addresses in the table. I would like to run a query on the owners' workflow
and send summarized (lists in emails) to each owner based on the result. I
would also like it to be actioned similar to a stored procedure, on a regular
timed interval schedule. I would also like to be able to add additional text
to the the email.

If anyone has some code samples, I will be extremely grateful.

Regards,

Layne
Click to expand...
Click to expand...
Click to expand...
 
Back
Top