Results 1 to 5 of 5
  1. #1
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114

    VBA to Send E-mail to Addresses from Query

    Hi all,
    I have a database with information about the instructional staff in my school (Instructors, Teaching Assistants, Readers). They teach different types of classes (math, composition, general education).
    I have a juncture table to assign a role to each individual. It links Course, Person, and Role (Instructors, TAs, Readers).

    I would like to create a form that has a few command buttons that will each allow sending an e-mail message to a group of instructors, such as all instructors who teach math, all TAs, all instructors who teach composition, etc.)

    I believe that I will first have to create a query that filters the appropriate individuals and pulls their e-mail addresses.

    But how do I set up the VBA code to compose messages to all of those people?

    I would like to click a button in the form and make all of the addresses from the query go to the bcc field. I would like the form to have several such buttons, for each of the groups mentioned above.

    I do not need a form letter or attachments.

    I just need to be able to click a button and have a new Outlook message populate with the relevant addresses in the bcc field.

    Any help would be appreciated.

    Thanks!

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

  3. #3
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114

    Helpful but more questions

    Thanks for the link. It was helpful.

    The scenario in the video is somewhat different from mine.
    I do not need to attach anything, and I do not want separate emails for each address.
    I need all of the addresses from the query to be added to the bcc field and be separated by commas. When I click the button a new e-mail message would open with all of the addresses from the query in the bcc field.

    Any ideas?

  4. #4
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114

    Solved

    I have pieced together code from different sources, and this ended up working. So hopefully this can help others as well.

    Basically what this does is open a new message in Outlook with all the addressed listed in query appearing in the Bcc line. The ; between the addresses are added automatically, but I think that is an Outlook feature rather that something that the code accomplishes. The To, Subject, and Body remain blank, and there are no attachments. The user inserts those manually and clicks Send in Outlook to send the message to the group.

    Private Sub EmailAllInstructors_Click()
    Dim db As DAO.Database
    Dim rsRecip As DAO.Recordset
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    Set db = CurrentDb

    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    ' Open the recordset so you can loop through it. qryEmailAllInstructors is
    ' the name of the query that contains the email addresses; adjust it to the ' name of your query.
    Set rsRecip = db.OpenRecordset("qryEmailAllInstructors")

    Do Until rsRecip.EOF

    ' Add recipients; Email is the field in qryEmailAllInstructors that contains
    ' the addresses.

    Set objOutlookRecip = _
    objOutlookMsg.Recipients.Add(rsRecip("Email"))
    objOutlookRecip.Type = olBCC

    rsRecip.MoveNext

    Loop

    objOutlookMsg.Display

    'Cleanup
    ExitProc:
    If Not rsRecip Is Nothing Then
    rsRecip.Close: Set rsRecip = Nothing
    End If
    Set db = Nothing
    Exit Sub
    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "Error in SendMessage Procedure..."
    Resume ExitProc
    Resume
    End Sub

  5. #5
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Thanks for posting a solution.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Send e-mail with info from Report
    By mari_hitz in forum Import/Export Data
    Replies: 22
    Last Post: 12-17-2011, 06:24 PM
  2. send a form via mail
    By Fabdav in forum Forms
    Replies: 1
    Last Post: 10-12-2011, 07:35 AM
  3. send email to email addresses in database?
    By cnstarz in forum Access
    Replies: 5
    Last Post: 03-02-2011, 09:46 PM
  4. Send mail to the chosen ones
    By carstenhdk in forum Import/Export Data
    Replies: 0
    Last Post: 05-18-2010, 11:51 PM
  5. Send Object to addresses in table
    By cm-net in forum Access
    Replies: 1
    Last Post: 04-26-2010, 02:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums