Sending email to a list of addresses from a query

[Mark Hettler]

I have a table containing personnel information, which
includes an email address field. I want to be able to run
a query based on certain criteria and return a list of
email addresses, and send a message to those addresses.
Kind of like a mailmerge except it's an email-merge. Is
there an easy way to do this? If not, how about a hard
way? Thanks.

 

[Cheryl Fischer]

Here is some code to get you started. You will need to add a reference to
the Microsoft xx.x Outlook Object Library and the Microsoft DAO x.xx Object
Library appropriate to the version of Office that you are using.


Dim oApp As Outlook.Application
Dim objNewMail As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim db as DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String

Set oApp = New Outlook.Application

strSQL = "Select from MyTable WHERE ..."
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL, DBOpenDynaset)

Set objNewMail = oApp.CreateItem(olMailItem)
With objNewMail
rs.MoveFirst
' All recipients will get the same email
Do While Not rs.EOF
If Len(Trim(rs!email)) > 0 Then
Set objOutlookRecip = .Recipients.Add(rs!email)
objOutlookRecip.Type = olTo
End If
rs.MoveNext
Loop
.Subject = "Your subject here."
.Body = "Your text message here."
.Save
.Send
End With

hth,

 

[Mark Hettler]

Cheryl, looks like exactly what I need. Thanks a million.

-----Original Message-----
Here is some code to get you started. You will need to add a reference to
the Microsoft xx.x Outlook Object Library and the Microsoft DAO x.xx Object
Library appropriate to the version of Office that you are using.


Dim oApp As Outlook.Application
Dim objNewMail As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim db as DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String

Set oApp = New Outlook.Application

strSQL = "Select from MyTable WHERE ..."
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL, DBOpenDynaset)

Set objNewMail = oApp.CreateItem(olMailItem)
With objNewMail
rs.MoveFirst
' All recipients will get the same email
Do While Not rs.EOF
If Len(Trim(rs!email)) > 0 Then
Set objOutlookRecip = .Recipients.Add(rs! email)
objOutlookRecip.Type = olTo
End If
rs.MoveNext
Loop
.Subject = "Your subject here."
.Body = "Your text message here."
.Save
.Send
End With

hth,


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

[QUOTE="Mark Hettler"]
I have a table containing personnel information, which
includes an email address field. I want to be able to run
a query based on certain criteria and return a list of
email addresses, and send a message to those addresses.
Kind of like a mailmerge except it's an email-merge. Is
there an easy way to do this? If not, how about a hard
way? Thanks.[/QUOTE]


.
[/QUOTE]

 

[Cheryl Fischer]

You're welcome.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Cheryl, looks like exactly what I need. Thanks a million.

-----Original Message-----
Here is some code to get you started. You will need to add a reference to
the Microsoft xx.x Outlook Object Library and the Microsoft DAO x.xx Object
Library appropriate to the version of Office that you are using.


Dim oApp As Outlook.Application
Dim objNewMail As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim db as DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String

Set oApp = New Outlook.Application

strSQL = "Select from MyTable WHERE ..."
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL, DBOpenDynaset)

Set objNewMail = oApp.CreateItem(olMailItem)
With objNewMail
rs.MoveFirst
' All recipients will get the same email
Do While Not rs.EOF
If Len(Trim(rs!email)) > 0 Then
Set objOutlookRecip = .Recipients.Add(rs! email)
objOutlookRecip.Type = olTo
End If
rs.MoveNext
Loop
.Subject = "Your subject here."
.Body = "Your text message here."
.Save
.Send
End With

hth,


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

[QUOTE="Mark Hettler"]
I have a table containing personnel information, which
includes an email address field. I want to be able to run
a query based on certain criteria and return a list of
email addresses, and send a message to those addresses.
Kind of like a mailmerge except it's an email-merge. Is
there an easy way to do this? If not, how about a hard
way? Thanks.[/QUOTE]


.
[/QUOTE][/QUOTE]