Access VBA - send email for each record in a table

Posted on 2010-08-24
Medium Priority
Last Modified: 2013-11-27

I have an Access table with the records Email, Body, etc
How would I loop through all records automatically sending an email for each.

Question by:Murray Brown
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 33514214
Something like this...
Sub SendEmail
    Dim olApp As Object, olMsg As Object
    Dim rs As DAO.Recordset
    Set olApp = CreateObject("Outlook.Application")
    Set rs = CurrentDb.OpenRecordset("NameOfTable")
    Do While Not rs.EOF
        If Nz(rs!EmailAddr, "") <> "" Then
            Set olMsg = olApp.CreateItem(0)
            With olMsg
                .To = rs!EmailAddr
                .Subject = rs!Subject
                .Body = rs!Body
            End With
        End If
    Set olMsg = Nothing
    Set olApp = Nothing
    Set rs = Nothing
    MsgBox "Done"
End Sub

Open in new window


Author Closing Comment

by:Murray Brown
ID: 33514248
Thanks very much

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…

622 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