Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Is there anyway to send Bulk emails using Access and Outlook together?

    I would really appreciate any advice and assistance in such a big headache that i get everyday when I am asekd to send email to different clients. I was thinking, is there any easy way to use access to send bulk email from within access BUT the each recepient should only see his/her own email address in the To field?

    Thanking you all for your help!
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Oct 2005
    Posts
    34
    Quote Originally Posted by Emal
    I would really appreciate any advice and assistance in such a big headache that i get everyday when I am asekd to send email to different clients. I was thinking, is there any easy way to use access to send bulk email from within access BUT the each recepient should only see his/her own email address in the To field?

    Thanking you all for your help!
    Tinker with Docmd.Sendobject within VBA. I was able to create a recordset holding the emails of the recipients and have it loop through the statement. Worked with Lotus Notes, should probably work even better with Outlook.

  3. #3
    Join Date
    May 2006
    Posts
    386
    Could you kindly give some clear instruction on how the whole process can be done?
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Oct 2005
    Posts
    34
    Quote Originally Posted by Emal
    Could you kindly give some clear instruction on how the whole process can be done?
    Give this a try.

    Private Sub Send_Emails()
    Dim rsPeople As ADODB.Recordset
    Dim cnn As ADODB.Connection

    Set rsPeople = New ADODB.Recordset
    Set cnn = New ADODB.Connection

    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.FullName & ""
    cnn.Open

    Set rsPeople = cnn.Execute("SELECT * FROM List_of_People")

    Do

    DoCmd.SendObject , "", "", rsPeople("List_of_People"), "", "", "The Subject Line of your Email", "The body of your email.", False, ""
    rsPeople.MoveNext
    Loop Until rsPeople.EOF
    cnn.Close
    End Sub

  5. #5
    Join Date
    May 2006
    Posts
    386
    I am sorry! Could you tell me where to place this code? and how will this pick email address from the table which holds details about clients including their email addresses?

    May be you are dealing with biginner on this!
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Feb 2013
    Posts
    2
    Emal, did you ever find a solution to this as it looks like the same sort of thing I want to be able to do, and like you I'm unsure how to write or place script.

  7. #7
    Join Date
    May 2006
    Posts
    386
    no, not at all.
    Emi-UK
    Love begets Love, Help Begets Help

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. What exactly do you want to send (Text, Form, Report, external file, ...) and in which format?
    2. Where are the addresses stored (please detail)?
    3. What's wrong (error code, etc.) with the code posted by wysiwyg6000?
    4. Which version of Office do you use?
    Have a nice day!

  9. #9
    Join Date
    May 2006
    Posts
    386
    Hi, please see my response below in the order

    1. Reports and also sometime external files. Reports are for reminders
    2.addresses are stored in my db table i.e. employeestable
    3.this code does not do what i want
    4. Office 2002
    Emi-UK
    Love begets Love, Help Begets Help

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A solution would consist in sending the email with all the recipients addresses on the BCC address line. You can open a RecordSet on the table containing the addresses, assemble the line containing the addresses, then use the DoCmd.SendObject method to send the message. Here's a example based on this technique and that sends an email with a report in Excel format in attachment:
    Code:
    Public Sub BulkMail(Optional ByVal SQLSource As String, _
                        Optional ByVal Subject As String, _
                        Optional ByVal ReportName As String, _
                        Optional ByVal Message As String)
    
        Const c_SQL As String = "SELECT Email_Address FROM Tbl_Employees WHERE Inactive=False"
        Const c_Subject As String = "New Users report."
        Const c_RptName As String = "Rpt_Users"
        Const c_Text As String = "Dear all,||In attachment, please find the newest Users report.||Regards."
        
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strRptName As String
        Dim strAddress As String
        Dim strSubject As String
        Dim strText As String
        
        If Len(SQLSource) > 0 Then strSQL = SQLSource Else strSQL = c_SQL
        If Len(Subject) > 0 Then strSubject = Subject Else strSubject = c_Subject
        If Len(ReportName) > 0 Then strRptName = ReportName Else strRptName = c_RptName
        If Len(Message) > 0 Then strText = Message Else strText = Replace(c_Text, "|", vbNewLine)
        
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                If Len(strAddress) > 0 Then strAddress = strAddress & ";"
                strAddress = strAddress & !Email_Address
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        DoCmd.SendObject acSendReport, strRptName, acFormatXLS, , , strAddress, strSubject, strText, False
        
    End Sub
    Unfortunately, depending on the version of Office (and the level of patching/Service pack), various annoyances can get in the way. Actually presented as security features, they cause a popup message to appear asking you to confirm that you actually want to send the email. Cherry on the cake: this popup box has a progress bar with a counter that forces you to wait for 5 seconds before pressing the confirmation (OK) button which remains disabled during those 5 seconds.

    Another technique that uses Automation to communicate with Outlook is no better. The popup window is different but annoying all the same.

    There are several ways to get rid of those "security features" and solutions are provided on various sites (there must be something in the code bank here too). Some if these solutions are complex, some only work with certain version(s) of Office, some a dangerous because they remove all security settings, and some are buggy and do not work at all (although they probably worked in the past).
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •