none
Sending Reports to Multiple Recipients from Access 2010 Using Outlook 2010 RRS feed

  • Question

  • I am trying to send a individualized report from Access 2010 using Outlook. My subprocedure in VBA works perfectly, except for each recipient I need to click on the allow button to proceed.  What can I add to my code to make this run automatically?  I have included my code and the error message below:

    Private Sub SendReports_Click()
    On Error GoTo Err_SendReports_Click
    Dim StrTo As String
    Dim rs As DAO.Recordset, MsgTitle As String, MsgBody As String
    MsgTitle = "Tool Reimbursement Report"
    MsgBody = "This is your current Tool Reimbursement Statement."
    Set rs = CurrentDb.OpenRecordset("Select Distinct tblTechnicians.TechNumber, tblTechnicians.Email FROM tblTechnicians WHERE tblTechnicians.TechNumber is not Null")

       Do While Not rs.EOF
            Me.txtTechnicianNumber = rs.Fields("TechNumber")
         DoCmd.SendObject acSendReport, "rptTechBillableHoursVSSpentAll", _
           acFormatRTF, rs!Email, , , MsgTitle, MsgBody, False
         DoEvents
         rs.MoveNext
            Loop
    Exit_SendReports_Click:
         Exit Sub

    Err_SendReports_Click:
         MsgBox Err.Description
         Resume Exit_SendReports_Click
      
    End Sub

    Any help will be greatly appreciated.  Thank you.


    Alicia Hunsberger


    Thursday, October 4, 2012 10:54 PM

Answers

  • If your only problem is the confirmation dialogs you receive for each recipient, it's a security issue. The problem is not the code, it's the Outlook object model security and the setup on that computer.
     
    See if the information in this article, specific to Outlook 2010 and the code security settings helps: http://msdn.microsoft.com/en-us/library/office/ff864479.aspx
     
    You will need to set up the computer so it allows the code to run without warning dialogs based on that article.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "InstructorGirl" <=?utf-8?B?SW5zdHJ1Y3Rvckdpcmw=?=> wrote in message news:4e4bc2ae-0078-4d89-a631-e9ad1db4e91a...

    Ken,

    I noticed that you are an MVP for Outlook.  Can you help me with the coding portion of this procedure for the Outlook part of it? I could really use the help on this.  Thanks.


    Alicia Hunsberger


    Ken Slovak MVP - Outlook
    Tuesday, October 9, 2012 6:22 PM
    Moderator

All replies

  • You're running into the Outlook security. This article is a bit out of date, but most of what it has on Outlook 2007 applies also to Outlook 2010: http://www.outlookcode.com/article.aspx?id=52
     

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "InstructorGirl" <=?utf-8?B?SW5zdHJ1Y3Rvckdpcmw=?=> wrote in message news:df2949bc-8bbc-4dc0-a839-1e2c7c2e6cbc...

    I am trying to send a individualized report from Access 2010 using Outlook. My subprocedure in VBA works perfectly, except for each recipient I need to click on the allow button to proceed.  What can I add to my code to make this run automatically?  I have included my code and the error message below:

    Private Sub SendReports_Click()
    On Error GoTo Err_SendReports_Click
    Dim StrTo As String
    Dim rs As DAO.Recordset, MsgTitle As String, MsgBody As String
    MsgTitle = "Tool Reimbursement Report"
    MsgBody = "This is your current Tool Reimbursement Statement."
    Set rs = CurrentDb.OpenRecordset("Select Distinct tblTechnicians.TechNumber, tblTechnicians.Email FROM tblTechnicians WHERE tblTechnicians.TechNumber is not Null")

       Do While Not rs.EOF
            Me.txtTechnicianNumber = rs.Fields("TechNumber")
         DoCmd.SendObject acSendReport, "rptTechBillableHoursVSSpentAll", _
           acFormatRTF, rs!Email, , , MsgTitle, MsgBody, False
         DoEvents
         rs.MoveNext
            Loop
    Exit_SendReports_Click:
         Exit Sub

    Err_SendReports_Click:
         MsgBox Err.Description
         Resume Exit_SendReports_Click
      
    End Sub

    Any help will be greatly appreciated.  Thank you.


    Alicia Hunsberger



    Ken Slovak MVP - Outlook
    Friday, October 5, 2012 2:28 PM
    Moderator
  • Hi Ken,

    Thank you for responding.  I looked at the articles and am a bit confused.  I am not a programmer, so I struggle a bit with some of the language.  I tried to add some of the code mentioned in the examples but I'm getting an error on the line "Dim MyOutlook as Outlook.Application".  I want to use the code I have and incorporate the code in my procedure to use Outlook.  Could you please look at this and tell me why I am getting this error?  Thanks.


    Alicia Hunsberger

    Monday, October 8, 2012 5:56 PM
  • Do you have a reference set to Outlook in your VBA project references in Access?
     
    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "InstructorGirl" <=?utf-8?B?SW5zdHJ1Y3Rvckdpcmw=?=> wrote in message news:5ca57806-693a-471b-91b3-418934da7cac...

    Hi Ken,

    Thank you for responding.  I looked at the articles and am a bit confused.  I am not a programmer, so I struggle a bit with some of the language.  I tried to add some of the code mentioned in the examples but I'm getting an error on the line "Dim MyOutlook as Outlook.Application".  I want to use the code I have and incorporate the code in my procedure to use Outlook.  Could you please look at this and tell me why I am getting this error?  Thanks.


    Alicia Hunsberger


    Ken Slovak MVP - Outlook
    Monday, October 8, 2012 8:51 PM
    Moderator
  • Yes, I do now. I went into Tools...References and check marked the option for Microsof Office 14.0 Object Library.  I took out the code from the previous post and have been trying different things with no luck.  Now I'm back to my original procedure.

    Private Sub SendReports_Click()
     On Error GoTo Err_SendReports_Click
     Dim StrTo As String
     Dim rs As DAO.Recordset, MsgTitle As String, MsgBody As String
     MsgTitle = "Tool Reimbursement Report"
     MsgBody = "This is your current Tool Reimbursement Statement."
     Set rs = CurrentDb.OpenRecordset("Select Distinct tblTechnicians.TechNumber, tblTechnicians.Email FROM tblTechnicians WHERE tblTechnicians.TechNumber is not Null")
       Do While Not rs.EOF
            Me.txtTechnicianNumber = rs.Fields("TechNumber")
         DoCmd.SendObject acSendReport, "rptTechBillableHoursVSSpentAll", _
           acFormatRTF, rs!Email, , , MsgTitle, MsgBody, False
         DoEvents
         rs.MoveNext
            Loop
    Exit_SendReports_Click:
         Exit Sub
    Err_SendReports_Click:
         MsgBox Err.Description
         Resume Exit_SendReports_Click

    End Sub


    Alicia Hunsberger

    Monday, October 8, 2012 9:13 PM
  • Ken,

    I noticed that you are an MVP for Outlook.  Can you help me with the coding portion of this procedure for the Outlook part of it? I could really use the help on this.  Thanks.


    Alicia Hunsberger

    Monday, October 8, 2012 10:16 PM
  • If your only problem is the confirmation dialogs you receive for each recipient, it's a security issue. The problem is not the code, it's the Outlook object model security and the setup on that computer.
     
    See if the information in this article, specific to Outlook 2010 and the code security settings helps: http://msdn.microsoft.com/en-us/library/office/ff864479.aspx
     
    You will need to set up the computer so it allows the code to run without warning dialogs based on that article.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "InstructorGirl" <=?utf-8?B?SW5zdHJ1Y3Rvckdpcmw=?=> wrote in message news:4e4bc2ae-0078-4d89-a631-e9ad1db4e91a...

    Ken,

    I noticed that you are an MVP for Outlook.  Can you help me with the coding portion of this procedure for the Outlook part of it? I could really use the help on this.  Thanks.


    Alicia Hunsberger


    Ken Slovak MVP - Outlook
    Tuesday, October 9, 2012 6:22 PM
    Moderator
  • The IT department will not let us change the settings for Outlook, nor will they allow us to buy an add in to use Access with Outlook, so I am try to use cdo to send the reports.

    My sub procedure look like this:

    Public Sub SendReports()
     On Error GoTo Err_SendReports

     Dim rs As DAO.Recordset

     Dim EmailTo, EmailSubj, EmailText, EmailFrom, EmailAtach, objEmail, WScript
     
     Dim oFSO, sFile, sSig, oFile, sText, oFileA, sCurPath

     
    EmailFrom = "name@cox.net"
    EmailTo = "rs"
    EmailSubj = "Report Name"
    EmailText = "email.txt"
    EmailAtach = "Report.rtf"

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    sFile = "email.txt"
    If oFSO.FileExists(sFile) Then
        Set oFile = oFSO.OpenTextFile(sFile, 1)
            Do While Not oFile.AtEndOfStream
                sText = oFile.ReadAll
                    If Trim(sText) <> "" Then
                        EmailText = sText
                    End If
            Loop
    Else: WScript.Echo "The file was not there."
    End If
    sCurPath = oFSO.GetAbsolutePathName(".")
    Set objEmail = CreateObject("CDO.Message")
    objEmail.From = EmailFrom
    objEmail.To = EmailTo
    objEmail.Subject = EmailSubj
    objEmail.Textbody = EmailText
    objEmail.AddAttachment sCurPath & "\" & EmailAtach
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
            "name@cox.net"
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objEmail.Configuration.Fields.Update
    objEmail.Send
    oFile.Close

    Exit_SendReports:
         Exit Sub
    Err_SendReports:
         MsgBox Err.Description
         Resume Exit_SendReports

    End Sub

    When I run the procedure I get an error message, "Object required" and I can figure where it's coming from.  The email.txt file and the database are in the same folder. Any thoughts on this?


    Alicia Hunsberger

    Tuesday, October 16, 2012 9:10 PM
  • Don't even bother, CDO is subject to even more stringent security than the Outlook object model. It's also not supported any longer.
     
    If your IT department is setting those restrictions they are causing the problem and preventing solving it. There isn't much you can do other than using C++ or Delphi and Extended MAPI, and that can't be used from Access VBA code.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "InstructorGirl" <=?utf-8?B?SW5zdHJ1Y3Rvckdpcmw=?=> wrote in message news:4ff88783-d65a-437c-ac16-d53dd769ddbf...

    The IT department will not let us change the settings for Outlook, nor will they allow us to buy an add in to use Access with Outlook, so I am try to use cdo to send the reports.

    My sub procedure look like this:

    Public Sub SendReports()
     On Error GoTo Err_SendReports

     Dim rs As DAO.Recordset

     Dim EmailTo, EmailSubj, EmailText, EmailFrom, EmailAtach, objEmail, WScript
     
     Dim oFSO, sFile, sSig, oFile, sText, oFileA, sCurPath


    EmailFrom = "name@cox.net"
    EmailTo = "rs"
    EmailSubj = "Report Name"
    EmailText = "email.txt"
    EmailAtach = "Report.rtf"

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    sFile = "email.txt"
    If oFSO.FileExists(sFile) Then
        Set oFile = oFSO.OpenTextFile(sFile, 1)
            Do While Not oFile.AtEndOfStream
                sText = oFile.ReadAll
                    If Trim(sText) <> "" Then
                        EmailText = sText
                    End If
            Loop
    Else: WScript.Echo "The file was not there."
    End If
    sCurPath = oFSO.GetAbsolutePathName(".")
    Set objEmail = CreateObject("CDO.Message")
    objEmail.From = EmailFrom
    objEmail.To = EmailTo
    objEmail.Subject = EmailSubj
    objEmail.Textbody = EmailText
    objEmail.AddAttachment sCurPath & "\" & EmailAtach
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
            "name@cox.net"
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objEmail.Configuration.Fields.Update
    objEmail.Send
    oFile.Close

    Exit_SendReports:
         Exit Sub
    Err_SendReports:
         MsgBox Err.Description
         Resume Exit_SendReports

    End Sub

    When I run the procedure I get an error message, "Object required" and I can figure where it's coming from.  The email.txt file and the database are in the same folder. Any thoughts on this?


    Alicia Hunsberger


    Ken Slovak MVP - Outlook
    Tuesday, October 16, 2012 9:27 PM
    Moderator
  • Ken,

    Can you point me to an article that states that cdo is no longer supported? Thanks.


    Alicia Hunsberger

    Tuesday, October 16, 2012 9:38 PM
  •  
    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "InstructorGirl" <=?utf-8?B?SW5zdHJ1Y3Rvckdpcmw=?=> wrote in message news:720c2502-7b6f-4138-b253-97451a0de8d2...

    Ken,

    Can you point me to an article that states that cdo is no longer supported? Thanks.


    Alicia Hunsberger


    Ken Slovak MVP - Outlook
    Tuesday, October 16, 2012 9:43 PM
    Moderator
  • Thanks for the link on this Ken.

    Alicia Hunsberger

    Tuesday, October 16, 2012 11:46 PM
  • Ken,

    Our IT department has decided to change the group policy settings in Outlook to allow the database to run with Outllook. I have looked at the link you suggested to do this:

    http://msdn.microsoft.com/en-us/library/office/ff864479.aspx

    But I need to know what specific settings to change.  Can you help me with this?


    Alicia Hunsberger

    Thursday, October 18, 2012 5:51 PM
  • Sorry, I'm no admin and I'm not at all familiar with group policy settings.
     
    I'd suggest asking about this in an Outlook user or admin forum, where more people might be familiar with Outlook group policy settings.
     
    Another place to ask would be the forums at www.slipstick.com, which is a premier Outlook/Exchange resource run by an Outlook MVP who does do work with group policy stuff.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "InstructorGirl" <=?utf-8?B?SW5zdHJ1Y3Rvckdpcmw=?=> wrote in message news:3213b2f8-43ee-4686-bff0-cbc3051eb911...

    Ken,

    Our IT department has decided to change the group policy settings in Outlook to allow the database to run with Outllook. I have looked at the link you suggested to do this:

    http://msdn.microsoft.com/en-us/library/office/ff864479.aspx

    But I need to know what specific settings to change.  Can you help me with this?


    Alicia Hunsberger


    Ken Slovak MVP - Outlook
    Thursday, October 18, 2012 9:11 PM
    Moderator