Solved

Sending multiple emails from Access Query

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
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 …

803 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