Bulk emails from Access

I have a Student Database and I want to send bulk emails to groups of students using Access 2007 and Outlook - any suggestions please
Who is Participating?
Jeffrey CoachmanMIS LiasonCommented:

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.

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

Best of luck
Bm keshav
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

AnnetteDavidAuthor Commented:
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.
Another work around is to use CDO and SMTP which totally bypasses Outlook. Actually pretty simple and I have code
AnnetteDavidAuthor Commented:
please let me look at the code jmoss111/
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.
Jeffrey CoachmanMIS LiasonCommented:

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.


AnnetteDavidAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:

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.


AnnetteDavidAuthor Commented:
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
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

Jeffrey CoachmanMIS LiasonCommented:
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.

All Courses

From novice to tech pro — start learning today.