[Last Call] Learn how to a build a cloud-first strategyRegister Now


Bulk emails from Access

Posted on 2009-05-05
Medium Priority
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 www.aadconsulting.com)

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 http://www.dimastr.com/redemption/home.htm

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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:                  http://www.utterangel.com/UtterAngel/utterangel.aspx?cat=acc

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

Hope this helps.
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 = "http://schemas.microsoft.com/cdo/configuration/"
            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 = "ap@my.com"
                    '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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 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