Bulk emails from Access

Posted on 2009-05-05
Last Modified: 2013-11-28
I have a Student Database and I want to send bulk emails to groups of students using Access 2007 and Outlook - any suggestions please
Question by:AnnetteDavid
  • 4
  • 4
  • 2
  • +3

Expert Comment

ID: 24303474

DoCmd.SendObject acSendTable,"tablename",outputformat,to,cc,subject,messagetext,editmessage

Best of luck
Bm keshav
LVL 38

Expert Comment

ID: 24303659
There are a number of possibilities here. You can create a mail merge in Word using your Access query as the Data Source (see example below). You can also create your form letter as an Access Report.   You can do an e-mail merge with Outlook as well.

Run A Word MailMerge from Access (from the tips page of

Copy and paste into a new Access module: then call module from button code or other code.

Option Compare Database
Option Explicit
Function MergeIt(stFilePath As String, stQuery As String)
stFilePath As String - Full path to merge letter template
stQuery As String - SQL String for merge fields data from the CurrentDB

Dim stDBName As String
Dim objWord As Word.Document
Dim stSQL As String

stSQL = "Select * from " & stQuery & ""
stQuery = "QUERY " & stQuery

stDBName = CurrentDb.Name

Set objWord = GetObject(stFilePath, "Word.Document")

' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the Northwind database
objWord.MailMerge.OpenDataSource _
Name:=stDBName, _
LinkToSource:=True, _
Connection:=stQuery, _

' Execute the mail merge.

End Function

Expert Comment

ID: 24303737
In an earlier version of Access/Outlook I had problems when new security service pack was installed that meant I couldn't send bulk e-mails from Access.  The work-around was to use some other dlls - see


Author Comment

ID: 24303871
Hi Bm Keshav

Many thanks for your response and the others.  I am familiar with the mail merge option and I am really looking for an easier solution than this as the users are really data capturers, thanks also for the info on dlls but I was looking at the students template in Access 2007 and the ability to export and import contacts to and from outlook was super, plus to email the student.  I am going to use this facility but I was wondering if there was a way to send the same email to a selection of the students using a grouping method.
LVL 18

Expert Comment

ID: 24303972
Another work around is to use CDO and SMTP which totally bypasses Outlook. Actually pretty simple and I have code

Author Comment

ID: 24305141
please let me look at the code jmoss111/
LVL 38

Expert Comment

ID: 24305345
If you go to this web site, you can download two free working demo email applications with source code:        

1. Email multiple recipients using Outlook.
2. Email multiple recipients using CDO

Hope this helps.
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)

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24311416

You are not providing any specifics on:

The structure of your tables
Your skill level with emailing through automation
How and where you are assigning these "Groups"
How you are deciding which group gets emailed.
Can one person belong to more than one group
What "specifically" are you actually "emailing"?
Are the contents of the email the same for all members of the group, or specific to all members
When you want the email to be sent
Do you need the data to be in the Body of the email, or in an attachment.
What format do you need the attachment in.
Do you need general help in designing this or do you need detailed help?

As you can see, asking "How do I send an email" is like asking: "How do I build a search form in Access"
There are countless ways and approaches depending on your "exact" needs.

So can you be a bit more detailed on what you have now, and describe "exactly" how you need the "Email" system to function.

In any event, here is a generic full functioning sample DB that illustrates hoe to send an email to certain "Groups".

This should get you off to a good start.



Author Comment

ID: 24312090
Hi Jeff

Many thanks for taking the time to help me.  I have done my best to try and answer your questions and understand now why it needs to be thought out.  I would appreciate any comments and help you can give me

Q - The structure of your tables
A- Learner Table with a Group Table as a look up, so each learner is allocated a group

Q Your skill level with emailing through automation
A- Beginner

Q How and where you are assigning these "Groups"
A- in the Learner Form at registration

Q How you are deciding which group gets emailed.
A- my idea was to have a form which asked which group to you want to email or could be based on a filter and then enter a message and poss an attachment (the same message to everyone) - no record would have to be kept in access of the emails sent (althought that would be lovely)

Q Can one person belong to more than one group
A- No

Q What "specifically" are you actually "emailing"?
A - probably a message - saying that An assignment is due or date of opening or changes of events.  I dont need to go down to more than one level of grouping, although if we could us Access 2007 filtering system in the table to get the learners and then send one email, that would be event better.   I am not looking at building a CRM, this is really to be small addon

Q Are the contents of the email the same for all members of the group, or specific to all members
A- same to all member

Q-When you want the email to be sent Do you need the data to be in the Body of the email, or in an attachment.
A - in the body of the email

Q What format do you need the attachment in
A- if we attached a document it would prob be a word doc or an Access Report (whichever would be easier) - easier being the operative word

Q Do you need general help in designing this or do you need detailed help?
A ...ect - both please

Thanks again Jeff
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24312373

Did you test out my sample?

It does much of what you require.

See how far you get by modifying my sample on your own.



Author Comment

ID: 24312526
Hi Jeff, we are busy with the sample now thanks, our problem is that it seems to actually send each email separately, so you have to click send and it does one email and then send and it does another.

do you think it is possible to achieve what we are looking for, is it a possibility, in other words it groups the learners (which the sample does), then when you go to outlook it sends it in one email (i.e. like a distribution list) where the recipients dont see each others emails.  Or am I reaching for the skies
LVL 18

Expert Comment

ID: 24312782
Hi Annette. My sample below doesn't use Outlook and therefore bypasses Outlook security. It sends one email to each recipient with having to answer OK or use any additional software. It will generate a log table of email sent.
Public Sub SendSMTP()

On Error GoTo NoAddr 'ErrorHandler ' Enable error-handling routine.


Dim db As dao.Database

Dim ThsDay As String

Dim ThisDay As String

Dim ThisYY As String

Dim ThisMM As String

Dim ThisDD As String

Dim strTo As String

Dim strCC As String

Dim strBCC As String

Dim strFrom  As String

Dim strTitle As String

Dim rs As Recordset

Dim strMailTo As String

Dim txtAttach1 As String

Dim txtAttach2 As String

Dim txtAttach3 As String

Dim fRetVal As Boolean

Dim strSubj As String

Dim strBody As String

Dim strSender As String

Dim strNoAddr As String

Dim strSupplier As String

Dim strDate As String

Dim strDate2 As String

Set db = CurrentDb

ThisDay = Date$

ThsDay = Right([ThisDay], 4) & Left([ThisDay], 2) & Mid([ThisDay], 4, 2)

strDate = ThsDay


Dim cdoMessage As Object

Dim objCDOMail As Object

Dim strschema As String

'On Error GoTo ErrorHandler ' Enable error-handling routine.


                'Db.Execute ("qrdEmailLog")

                Set rs = db.OpenRecordset("qrsEmailSpecialBodyAndContents1")



          Do While Not rs.EOF


            strTo = rs!Email1

            'If Rs!Email2 Is Not Null Then

            'strCC = rs!Email2

            'strBCC = rs!Email1

            'strFrom = rs!SenderEmail

            'End If

            'If Rs!Email3 Is Not Null Then

                'strCC = strCC & " ; " & Rs!Email3

            'End If

            strSubj = rs!SubjectLine

            strBody = rs!BodyContents

            'strBCC = Rs!PlantEmailBCC

          ' Enter path to documents to be attached -----------------------------------------

            'txtAttach1 = "J:\JimShared\ERFC\Memo.pdf"

            'txtAttach2 = "J:\JimShared\ERFC\Centralized Email Addresses.xls"

            'txtAttach3 = "J:\JimShared\ERFC\Query Res Org Chart.ppt"

            'sPathFile = txtAttach3

            ' Create the message.

          'GoTo AddBCC


              If strTo = "." Then GoTo AddCC Else

              ' Add the To recipient(s) to the message.

                sTo = strTo


              'If strCC = "." Then GoTo AddBCC Else

              ' Add the CC recipient(s) to the message.

              '  sCC = strCC


             ' If strBCC = "." Then GoTo AddSubject Else

             ' Add the BCC recipient(s) to the message.

             '  sBcc = strBCC


              'strNoAddr = strTo & strCC & strBCC

              If strNoAddr = "..." Then

                 GoTo NoAddr

              End If

             ' Set the Subject, Body, and Importance of the message.


              strNoAddr = strTo '& strCC & strBCC

              If strNoAddr = "..." Then

                 GoTo NoAddr

              End If

              GoTo GoCDO





            Set cdoMessage = CreateObject("CDO.Message")

            Set objCDOMail = CreateObject("CDO.Configuration")

            strschema = ""

            objCDOMail.Load -1 ' CDO Source Default

            'If you have illegal or wrong smtp address here it will run for 30-60

            'seconds and finally give transport error

            With objCDOMail.Fields

                            .Item(strschema & "sendusing") = 2 ' cdoSendUsingPort

                            '.Item(strschema & "smtpserver") = "999.999.999.999" ' "Your SMTP server address here"

                            .Item(strschema & "smtpserver") = "999.999.999.999" ' "Your SMTP server address here"

                            .Item(strschema & "smtpserverport") = 25 'specify port number

                            .Item(strschema & "smtpconnectiontimeout") = 120


            End With

            With cdoMessage

                Set .Configuration = objCDOMail

                    .to = strTo

                    .From = ""

                    'If strCC = "." Then

                     '   GoTo DoBcc

                    'End If

                    '.cc = strCC


                    'If strBCC = "." Then

                    '    GoTo DoSubject

                    'End If

                    '.Bcc = strBCC


                    .Subject = strSubj

                    '.Subject = "IMPORTANT : Concerning Electronic Invoicing for Suppliers"

                    '.textBody = strBody

                    .CreateMHTMLBody "file://J:/JimShared/iSupplierPortalLetter.htm"

                    '.AddAttachment txtAttach1

                    '.AddAttachment txtAttach2

                    '.AddAttachment txtAttach3


                    ' Update DSN Options below for the send notifications from above


                    .Fields("urn:schemas:mailheader:disposition-notification-to") = strFrom

                    .DSNOptions = cdoDSNSuccessFailOrDelay

                    '.DSNOptions = 14



            End With

             If IsNull(sCC) Then

                sCC = "."

            End If

            If IsNull(sBcc) Then

                sBcc = "."

            End If

            db.Execute ("qriEmailLog")

            sTo = " "

            sCC = " "

            sBcc = " "

            GoTo MoveOn


            sErrType = Err.Number

            sErrMesg = Err.Description

            db.Execute ("qriEmailErrors")

            Set cdoMessage = Nothing

            Set objCDOMail = Nothing




    Set cdoMessage = Nothing

    Set objCDOMail = Nothing

    Exit Sub ' Exit to avoid handler.

ErrorHandler: ' Error-handling routine.

    Debug.Print Err.Number & "-" & Err.Description

    Set cdoMessage = Nothing

    Set objCDOMail = Nothing

Exit Sub

End Sub

Open in new window

LVL 74

Accepted Solution

Jeffrey Coachman earned 500 total points
ID: 24313686

If you read the code commets it tells you to chage the last argument from True to False.
And tell you a product to obtain to bypass the security pop-up

Again examine the sample carefully.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24352504

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Transfer records between two Access tables 6 38
Restrict list data depending upon user name 3 20
Getting Run-Time Error 13 - Type Mismatch 3 27
access 7 25
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

920 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

13 Experts available now in Live!

Get 1:1 Help Now