1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Emailing multiple contacts in a query

Discussion in 'MS Access' started by ParksCoordinator, Aug 28, 2009.

  1. ParksCoordinator

    ParksCoordinator New Member

    Joined:
    Aug 28, 2009
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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?
     
    Last edited by a moderator: Aug 29, 2009
  2. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    I don't see any problem in subroutine

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

    ParksCoordinator New Member

    Joined:
    Aug 28, 2009
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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!
     

    Attached Files:

  4. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    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
     
  5. ParksCoordinator

    ParksCoordinator New Member

    Joined:
    Aug 28, 2009
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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!
     

    Attached Files:

Share This Page