Emailing multiple contacts in a query

Hello, I'm new to access and I could use some help. I am a volunteer coordinator for Friends of the Parks groups. I've set up a parameter query (called 'MyEmailAddresses') which pulls data from my 'Volunteer List' for Friends associated with one specific park which I enter on the prompt window. I want to be able to email everyone who shows up in this query at once in Outlook, and I'd preferably also like to have them all in the BCC: line instead of To:.

I tried a macro for this based off one I use to email an individual person which goes like this:
OnError Next,
SendObject ,,,=[Volunteer Name] & IIf(Nz[E-mail Address])<>","[&[E-mail Address] & "]"),,,,,Yes,
MsgBox =[MacroError].[Description],Yes,None,

I took this macro and added

OpenQuery MyEmailAddresses, Datasheet, Edit

to the top of it...But this only adds one email address when it launches Outlook and I want them all (i.e. bob@aol.com; sue@msn.com; joe@yahoo.com etc.)

Then, I tried a module with the code below, created a macro called Distribution List and set it to the button in my form that i want to click on to email everyone. But, I get the error 'the object doesn't contain the Automation object 'DistributionList' when I click the button. I'm not sure what this means or how to fix it.

Code:

Option Compare Database

Private Sub Command309_Click()
On Error GoTo Err_Command309_Click

Dim strTo As String
Dim strCCName As String
Dim strTitle As String
Dim strMessage As String

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("MyEmailAddresses")

Do While Not rs.EOF
strTo = rs!Email & "; "
rs.MoveNext
Loop

rs.Close

strCCName = "jacque.williamson@dehort.org"
strTitle = "Friends"
strMessage = "Hello Friends!"

DoCmd.SendObject , , , strTo, strCCName, , strTitle, strMessage, False

Exit_Command309_Click:
Exit Sub

Err_Command309_Click:
MsgBox Err.Description
Resume Exit_Command309_Click

End Sub

So any suggestions on my macro or my code? Which will work best for me?

 

I don't see any problem in subroutine

Can you attach the database (empty or with sample data will work)?

 

So, here's what I've got. It's still in the building stage, but I basically have it set up so that I can look at an event that we've had at a park, and see every volunteer who attended that day (or RSVPd).
I've also got it set up to work the other way around, so I can look at a vol. and see every event they've attended. So, this person is a 'friend of the park', who I may want to invite them and everyone else notated as a friend of this park to an upcoming event. But when I click the email friends group button, and type the park, it just opens up outlook with the first person's email address in my address book, not even in the query.

The page that has the buttons on it that I'm trying to setup for the email list is the 'volunteer list' page.

So, I'm at a stand-still now. Any help would be greatly appreciated!

 

I believe you are referring to common problem in posts 1 and 3.

Few Comments:

1] I see that the code you mentioned above "Private Sub Command309_Click()" was written on the buttonclick on the form, and then later copied to module DistributionList which is being called by Macro.

Use which ever you may want but keep it only once (Advice)

2] Since you are calling it from the macro, the macro expects a function and not a subroutine
So, under the module distributionlist, make it as function
like Function fnDistributionList

Then update the macro to point it to fnDistributionList

then you won't get the error you mentioned before
'the object doesn't contain the Automation object 'DistributionList'

3] In the code below you are calling the query MyEmailAddresses which expects a paramater value, so you will get an error, "Too Few Parameter..."

Code:

Set rs = CurrentDb.OpenRecordset("MyEmailAddresses")

Kindly supply the value.

4] In the code below, there's a problem so you are getting only 1 email address and not all

Code:

Do While Not rs.EOF
strTo = rs!Email & "; "
rs.MoveNext
Loop

Change it to

Code:

Do While Not rs.EOF
strTo = rs!Email & "; " [B]& strTo[/B]
rs.MoveNext
Loop

5] Since you want all the email id's to appear in BCC Field and not To, make the following change

Code:

DoCmd.SendObject , , , strTo, strCCName, , strTitle, strMessage, False

to

Code:

DoCmd.SendObject , , , , strCCName, strTo, strTitle, strMessage, False

 

Hi, Thanks for your help.

I'm not entirely sure I've changed everything according to your directions. I'm very new to access and am self taught thus far.

I'm attaching what I've got now, and the changes I think I've made to your recommendations-however, I'm still getting error message about the automation object, so right off the bat, I know I haven't quite gotten that correct.

I also made some comments in the code for the distribution list module about some of my questions regarding your suggestions. Again, I'm a beginner, so I'm still learning a lot of the methods to the madness of SQL.

Thanks again for all of your help!