Solved

Sending multiple emails from Access Query

Posted on 2014-01-22
2
85 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

856 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