Send Object Macro for multiple emails?

[hockey8837]

Is there a way to set the Send Object macro to email more than one person; i.e. a distribution list?

I have a parameter query which finds a select group of contacts, and I would like to create a button which, when clicked, would run the query prompt asking which group I'd like to find, and then input all of their email addresses into the BCC field of my Outlook message.

I currently have been able to setup the Send Object macro to email one person, but when applied to the above setup, it will only take the first email address in all of my contacts (not the query just run) and put it into the BCC.

I have little experience with modules, so I was hoping there was a way I could do this via the send object macro.
Thanks!

 

[dbDamo]

Just create a blank command button and paste this code in its on click event:-

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("[B]Enter QueryNameHere[/B]", dbOpenSnapshot)
 
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields([B]Enter the number of the column the email address is held in here - leftmost column is 0, 2nd column would be 1 etc[/B])) = False Then
sToName = .Fields([B]Enter the number of the column the email address is held in here - leftmost column is 0, 2nd column would be 1 etc[/B])
sSubject = "[B]EnterSubjectHere[/B]"
sMessageBody = "[B]EnterYourTextHere[/B]"
 
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With
 
Set MyDb = Nothing
Set rsEmail = Nothing

 

[hockey8837]

Hi, Thanks for the reply. I pasted the text into my button's code, and I get an error message saying "Run-time Error '3061': Too few parameters. Expected 1."

Here's the text in my code, as it is now:

Option Compare Database
Option Explicit

Private Function OpenFilterFavorites()

On Error GoTo ErrorHandler

' show the control and drop down the combo box
Me.cboFilterFavorites.Visible = True
Me.cboFilterFavorites.SetFocus
Me.cboFilterFavorites.Dropdown

ExitProcedure:
Exit Function

ErrorHandler:
Resume ExitProcedure

End Function

Private Sub EmailGroup_Click()

Sub email_adrs()

Dim rsmytable As Recordset
Dim i1, rcount As String
Dim i As Integer
Dim oMailApp As Outlook.Application
Dim oMail As Outlook.mailItem

Set rsmytable = New ADODB.Recordset
rsmytable.ActiveConnection = CurrentProject.Connection
rsmytable.Open "QRY_EMAIL_ADRS", , adOpenKeyset, adLockOptimistic, _
adCmdTable

rcount = rsmytable.RecordCount

For i = 1 To rcount
i1 = rsmytable.Fields("UserName")

DoCmd.SetWarnings False

Set oMailApp = CreateObject("Outlook.Application")
Set oMail = oMailApp.CreateItem(olMailItem)
With oMail
.TO = "name@email"
.BCC = i1
.Subject = "test email"
.body = vbCrLf & vbCrLf & " test "
.Display
'.Send
End With

Next

rsmytable.Close


End Sub

Private Sub Email_Distribution_List_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("Query Friends Emails", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(2)) = False Then
sToName = .Fields(2)
sSubject = "Hello Friends!"
sMessageBody = "Hello Friends"

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing


End Sub


Thanks again for all your help-this has been driving me crazy!

 

[dbDamo]

Just to confirm, the field which contains the email address is the 3rd column from the left?

You should also go to Tools>References from VBE and ensure you have Microsoft DAO 3.6 Object Library checked (and maybe set as higher priority than Microsoft ADO 2.5 Library)

If your references are fine then my next suggestion would be to change the design of your query to allow the email address field to be the leftmost field and change your .Fields(2) to .Fields(0) - seems like a silly change but I had to do it for mine to work.

If none of the above sort it, then upload a copy of your db minus any sensitive data and I will have a play.

 

[hockey8837]

I switched everything in my query so that the email field is (0) now, but still get the error.

I cannot check the DAO 3.6 Object Library because it says it "conflicts with another existing module, project, or library."
I'm using Access & Outlook 2007, and I have in the references MS Office 12.0 Access database engine object library selected as well as the MS Access 12.0 Object Library selected.

Does this effect the module?

I've attached the DB. To find the button, go to 'Volunteer List'. At the top you'll see a gray button titled 'email distribution list,' which is the one I'm trying to play with the module on. I've still got the old button, with the macro on it, titled 'send email to friends group.' The module button will not work, and the macro one will only email the first person in my contact table.

Like I said before, thanks so much for helping out-I'm going batty.

 

[dbDamo]

Ok, ignore the references. When using an Access 2007 ACCDB file within VBA, DAO will refer to the ACEDAO library automatically, so you're fine there.

I don't have Access 2007 here at work, for which I am quite glad, but I do have a trial version installed at home, so I will take a look later for you.

 

[hockey8837]

Thank you. Thank you. Thank you!

Ok, ignore the references. When using an Access 2007 ACCDB file within VBA, DAO will refer to the ACEDAO library automatically, so you're fine there.

I don't have Access 2007 here at work, for which I am quite glad, but I do have a trial version installed at home, so I will take a look later for you.

 

[dbDamo]

Ok, it seems the problem is that we aren't setting the parameter for the query. We can't set the parameter in VB because it will change everytime, i.e. you won't send to the same distribution list each time, so we would need to find a way to add the parameter when the button is pressed.

Unfortunately I don't think I'm going to be able to help you on this as it seems quite tricky. I'm not one to give up, so I will keep trying but I would imagine someone else will come up with an answer long before I could.

How many distribution lists are you likely to have? If you're only going to have a few then we could create a query for each list and then create a button for each list based on the new queries. Not ideal having more than one button I know, but thats my best idea for now (its been a long day).

If however it is possible that you may have more than a few dist lists, we will need to look at a much more clever and efficient way.

 

[hockey8837]

Well, I'm a volunteer coordinator for Friends of the Parks, so I could potentially have one for every park we're working with... That being said, I currently operate about 6-8 distribution lists in Outlook, but it will be a growing number, over time.

Right now, it's nice to be able to pull up everyone in that Friends group and see who's in it-if I could sync this list with a working outlook distribution list, that would work too. I basically need to be able to contact anyone who's ever volunteered for a certain park, multiple parks, or a certain event, and send them an email. Whether I do this via Access or Outlook, it doesn't matter. They just need to be the same people, and Outlook can't track what events the contacts have attended-only what park they belong to (via Categories). So, my outlook categories have been somewhat helpful, but every time I add a new group of volunteers from an event, I'm entering them multiple times, and have to create a new distribution list every time.

Hopefully that helps you get an idea of what I'm trying to do-and then perhaps we can figure out of there's a better way.
Thanks again!

Ok, it seems the problem is that we aren't setting the parameter for the query. We can't set the parameter in VB because it will change everytime, i.e. you won't send to the same distribution list each time, so we would need to find a way to add the parameter when the button is pressed.

Unfortunately I don't think I'm going to be able to help you on this as it seems quite tricky. I'm not one to give up, so I will keep trying but I would imagine someone else will come up with an answer long before I could.

How many distribution lists are you likely to have? If you're only going to have a few then we could create a query for each list and then create a button for each list based on the new queries. Not ideal having more than one button I know, but thats my best idea for now (its been a long day).

If however it is possible that you may have more than a few dist lists, we will need to look at a much more clever and efficient way.

 

[dbDamo]

Ok, multiple buttons really aren't an option then. We will have a think and see what we can come up with. I'm sure we can achieve it through Access, just might not be very straight forward!!!