Access VBA - send email for each record in a table

Posted on 2010-08-24
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:murbro
LVL 92

Accepted Solution

Patrick Matthews earned 500 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

ID: 33514248
Thanks very much

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
Outlook Free & Paid Tools
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

12 Experts available now in Live!

Get 1:1 Help Now