?
Solved

Sending multiple emails from Access Query

Posted on 2014-01-22
2
Medium Priority
?
103 Views
Last Modified: 2015-12-20
I want to be able to send multiple emails from Access, hopefully using Outlook.  I have a database that I will run a query on to get a list of people to who the email will go to.  Once I have the results of that query, I would like to email those people.  I can get the results in a query, report, form what ever.  Doesn't really matter.  But what I do want to be able to do it click and how the emails go to those people "records".  Each email will have to be personalized for each person.  So if I have 30 people as a result it will have to be 30 emails, with 30 subjects lines (could be similar, and the body will need to reference a field value that will be different for each person.  So in other words the email is not generic is that same data goes to each person.  It would need to say Dear "firstname" "lastname", please note that your appointment is on "Date of appointment".  If you have any questions please contact us at ....
0
Comment
Question by:PriceD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39800155
You'd have to let us know more about the table structure, but assuming you have a table for Appointments, which includes a Field to relate to the Person:

You would use VBA Code for this, which could be executed from a Form. The simplest way is to use SendObject.

For example, if you have a form, perhaps you'd add a Button named "cmEmail", and in the Click event of that button you'd fire this code:

Function EmailCustomers()
    Dim rst    As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT AppointmentTable.*, CustomerTable.Email,  CustomerTable.FirstName, CustomerTable.LastName,  FROM AppointmentTable INNER JOIN CustomerTable ON CustomerTable.IDField=AppointmentTable.CustomerField")

    Do Until rst.EOF
        Dim sTo As String
        Dim sSubject As String
        Dim sBody As String

        sTo = rst("Email")
        sSubject = "Appointment for " & rst("FirstName") & " " & rst("LastName")
        sBody = "Dear " & rst("FirstName") & " " & rst("LastName") & ":  You have an Appointment on " & rst("AppointmentDate")

        DoCmd.SendObject acSendNoObject, "", "", sTo, "", "", sSubject, sBody, False

        rst.MoveNext
    Loop
End Function

Open in new window

Obviously you'd have to change the Table and Field names to match your own, and you'd have to change the sBody to reflect what you want it to say.

If you want to send HTML emails, you'll have to use Outlook Automation (assuming you're using Outlook, of course). Here's an EE article about that:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html

If you're willing to spend a little money, this product will do what you want (and more) right out of the box:

https://www.fmsinc.com/MicrosoftAccess/Email.asp

I have no affiliation with FMS other than being a satisfied customer ...
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 39803108
Hi Scott, Thanks for suggesting our Total Access Emailer product.

PriceD: Let me know if you have any questions about it. A free trial is available here: https://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html

Unlike Outlook, it uses SMTP so you can automate emails and specify the FROM address no matter who's using the system and whether they have Outlook open or installed. SMTP also avoids the many security and MAPI limitations of Outlook.

Good luck!
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question