Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sending multiple emails from Access Query

Posted on 2014-01-22
2
Medium Priority
?
110 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 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