Solved

Bulk emails from Access

Posted on 2009-05-05
14
574 Views
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
0
Comment
Question by:AnnetteDavid
  • 4
  • 4
  • 2
  • +3
14 Comments
 
LVL 6

Expert Comment

by:BALMUKUND KESHAV
Comment Utility
CREATE A COMMAND BUTTON IN ANY ACCESS FORM
UNDER COMMAND BUTTON EVENT PROPERTY USE THE FOLLOWING COMMAND/ METHOD

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

Best of luck
Bm keshav
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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)
Paramaters:
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, _
SQLStatement:=stSQL

' Execute the mail merge.
objWord.MailMerge.Execute

End Function
0
 
LVL 2

Expert Comment

by:rsclark
Comment Utility
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

Robert
0
 

Author Comment

by:AnnetteDavid
Comment Utility
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.
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
Another work around is to use CDO and SMTP which totally bypasses Outlook. Actually pretty simple and I have code
0
 

Author Comment

by:AnnetteDavid
Comment Utility
please let me look at the code jmoss111/
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
Annette,
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
AnnetteDavid,

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?
...ect

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.

JeffCoachman

Access--EEQ-24380768-Indiviual-e.mdb
0
 

Author Comment

by:AnnetteDavid
Comment Utility
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility

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.

;-)

JeffCoachman
0
 

Author Comment

by:AnnetteDavid
Comment Utility
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
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
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")

                'rs.MoveLast

                rs.MoveFirst

          Do While Not rs.EOF

          'DoEvents

            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

AddTo:

              If strTo = "." Then GoTo AddCC Else

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

                sTo = strTo

AddCC:

              'If strCC = "." Then GoTo AddBCC Else

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

              '  sCC = strCC

AddBCC:

             ' 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.

AddSubject:

              strNoAddr = strTo '& strCC & strBCC

              If strNoAddr = "..." Then

                 GoTo NoAddr

              End If

              GoTo GoCDO

          
 

          

'======================================================================================

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

                            .Update

            End With

            With cdoMessage

                Set .Configuration = objCDOMail

                    .to = strTo

                    .From = "ap@my.com"

                    'If strCC = "." Then

                     '   GoTo DoBcc

                    'End If

                    '.cc = strCC

DoBcc:

                    'If strBCC = "." Then

                    '    GoTo DoSubject

                    'End If

                    '.Bcc = strBCC

DoSubject:

                    .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

                    .Fields.Update

                    .send

            End With

             If IsNull(sCC) Then

                sCC = "."

            End If

            If IsNull(sBcc) Then

                sBcc = "."

            End If

            db.Execute ("qriEmailLog")

            sTo = " "

            sCC = " "

            sBcc = " "

            GoTo MoveOn

NoAddr:

            sErrType = Err.Number

            sErrMesg = Err.Description

            db.Execute ("qriEmailErrors")

            Set cdoMessage = Nothing

            Set objCDOMail = Nothing

MoveOn:

          rs.MoveNext

          Loop
 
 

    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

0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
AnnetteDavid,

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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
;-)
0

Featured Post

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)

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 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

7 Experts available now in Live!

Get 1:1 Help Now