?
Solved

Bulk emails from Access

Posted on 2009-05-05
14
Medium Priority
?
594 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +3
14 Comments
 
LVL 6

Expert Comment

by:BALMUKUND KESHAV
ID: 24303474
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
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)
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
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

Robert
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

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

Expert Comment

by:jmoss111
ID: 24303972
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
ID: 24305141
please let me look at the code jmoss111/
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24305345
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24311416
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
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
0
 
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.

;-)

JeffCoachman
0
 

Author Comment

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

Expert Comment

by:jmoss111
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")
                '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 2000 total points
ID: 24313686
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
ID: 24352504
;-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

765 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