Solved

Sending multiple emails from Access Query

Posted on 2014-01-22
2
79 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
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

864 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now