How to use results from an Access query to populate e-mail addresses

[bmcilroy]

How do I use the results of an Access Query "Query Name", which are a bunch of e-mail addresses, to send out an e-mail within the following code?

___________________________________________________________

Sub Mass_Email()

Dim NewMail As Object
Dim objmail As Object

Set NewMail = CreateObject("Outlook.Application")
Set objmail = NewMail.CreateItem(olMailItem)
With objmail
.to = "HERE'S WHERE I WANT TO PUT THE RESULTS OF THE QUERY"
.Subject = "Health Insurance Rates for Me"
.Body = "Whatever"
.Attachments.Add "Whatever I define.pdf"
.Send

End With

Set NewMail = Nothing
Set objmail = Nothing

End Sub

__________________________________________________________

 

[JerryKlmns]

Dim myList As String ' Here is your distribution list

'For ADO
Dim rst As ADODB.Recordset
Set rst = CurrentProject.Connection.Execute("YourSelectQueryNameHere")
'For DAO
'Dim rst As DAO.Recordset
'Set rst = CurrentDB.Execute("YourSelectQueryNameHere")

If rst.EOF And rst.BOF Then
MsgBox "No Records"
rst.Close
Set rst = Nothing
End If
While Not rst.EOF
myList = myList & rst.Fields(EmailAddressFieldHere") & ";"
rst.MoveNext
Wend

myList = Left(myList, Len(myList)-1)

'Your code follows

Plus

Set objmail = Nothing
NewMail.Quit
Set NewMail = Nothing

 

[bmcilroy]

Jerry,

Here's the updated code below; I'm getting the following error:

"Run-time error '2147217900 (80040e14)':

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR UPDATE'



_________________________________________________________

Sub Mass_Email()

Dim myList As String ' Here is your distribution list

'For ADO
Dim rst As Recordset
Set rst = CurrentProject.Connection.Execute("Ad Hoc Reminders 2")
'For DAO
'Dim rst As DAO.Recordset
'Set rst = CurrentDB.Execute("Ad Hoc Reminders 2")

If rst.EOF And rst.BOF Then
MsgBox "No Records"
rst.Close
Set rst = Nothing
End If
While Not rst.EOF
myList = myList & rst.Fields("Email") & ";"
rst.MoveNext
Wend

myList = Left(myList, Len(myList) - 1)

'Your code follows

Dim NewMail As Object
Dim objmail As Object


Set NewMail = CreateObject("Outlook.Application")
Set objmail = NewMail.CreateItem(olMailItem)
With objmail
.to = myList
.Subject = "Health Insurance Rates for Me"
.Body = "Whatever"
.Attachments.Add "\\Our_Desktop\shareddocs\Truth Benefits\Truth Benefits Brochure.pdf"
.Send

End With

Set NewMail = Nothing
Set objmail = Nothing
NewMail.Quit


End Sub
_________________________________________________________

 

[JerryKlmns]

A bad name for an object to use :Ad Hoc Reminders 2
Either rename it to : Ad_Hoc_Reminders_2
Or Set rst = CurrentProject.Connection.Execute("[Ad Hoc Reminders 2]")
But, do prefer the first!