locked
Automatically Send Email via VBA RRS feed

  • Question

  • If this is the wrong forum, let me know...  I have written vba code to automatically send email via an Access 2010 database.  I can't get past the security message saying that something is trying to send an email.  I have MS Essentials installed and up to date.  Outlook starts up in Administrator mode and recognizes that my antivirus is valid when I open it normally (from the taskbar), but when I use ".send" in the vba code, I get the security message.  Then, when I check Outlook's Trust Center settings again, the antivirus software is "invalid" again.

    Any suggestions on how to solve this problem, or how to force Outlook to run in Administrator mode using vba?
    Tuesday, January 22, 2013 3:31 PM

Answers

  • I read through the Security Behavior article, but still could not get the code to work.  I'm not sure I understood all of the article and how the code is supposed to work.  

    When I could not get it working properly, I decided to try a work-around using Windows Task Manager.  I set it to open Outlook first, then open Access to run the code, give Access a minute or two to finish, then close Access, and then close Outlook.  It seems to be working, although it is far from the optimal solution.

    In the future, if I have time to address this more fully, I hope to resolve the security issue and contain the entire process within vba, without using Task Manager.  

    Thanks, Eric, for your help!


    Brian Lundell

    • Marked as answer by Dummy yoyo Thursday, January 31, 2013 9:43 AM
    Sunday, January 27, 2013 5:13 PM

All replies

  • Are you using Access' email functions (DoCmd.Send I think?), or using the Outlook Object Model (OOM)?  If the former, that has nothing to do with Outlook and Outlook's security settings do not apply.

    If you are using OOM, then it is strange that the antivirus status is changing.  All I can suggest is that you double-check the requirements below.  Also see:

    Outlook "Object Model Guard" Security Issues for Developers:
    http://www.outlookcode.com/article.aspx?ID=52

    Code Security Changes in Outlook 2007:
    http://msdn.microsoft.com/en-us/library/bb226709(office.12).aspx

    Security Behavior of the Outlook Object Model:
    http://msdn.microsoft.com/en-us/library/ff864479(office.14).aspxCross-Process Add-ins

    By default, Outlook relies on the existence and the status of an appropriate antivirus software on the client computer to trust cross-process applications: if Outlook detects that antivirus software is running with an acceptable status, Outlook will disable security warnings for the end user. All cross-process COM callers and add-ins will run without security warnings if all of the following conditions hold:

    • The client computer is running Windows XP Service Pack 2 (SP2), Windows Vista, or a later version of Windows, and Windows Security Center (WSC) indicates that antivirus software on the computer is in a "Good" health status.
    • The antivirus software installed on the client computer is designed for Windows XP SP2, Windows Vista, or later.
    • Outlook is configured on the client computer in one of the following ways:
      • Uses the default Outlook security settings (that is, no Group Policy set up)
      • Uses security settings defined by Group Policy but does not have programmatic access policy applied
      • Uses security settings defined by Group Policy which is set to warn when the antivirus software is inactive or out of date


    Eric Legault MVP (Outlook)
    About me...
    Outlook Appins: Store Social Media fields in your Outlook Contacts!

    Tuesday, January 22, 2013 4:46 PM
  • Thank you, Eric for your reply!

    I am using a With statement and .Send.  Does this mean, then, that the setting in Outlook's Trust Center
    is not causing the problem?

    Brian Lundell

    Tuesday, January 22, 2013 7:58 PM
  • It would help if you show your code. Is your code stored/run in Access or Outlook? Regardless - something weird is going on that I've never heard of if the anti-virus status keeps changing like that.

    Eric Legault MVP (Outlook)
    About me...
    Outlook Appins: Store Social Media fields in your Outlook Contacts!

    Wednesday, January 23, 2013 4:26 AM
  • Here is the code, Eric.  It is stored in an Access 2010 form module and is called during the night to send automatic emails to those PSRs (salesmen) for whom I am waiting for paperwork.  The emails only go out if it has been seven days since I gave them the paperwork to complete, and every seven days after that.  The message changes after fourteen days, and again after twenty-eight days.

    Private Sub RoosPendingClientSignature()
    Dim dbCurr As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim SQL1 As String
    Dim SQL2 As String
    Dim number As Integer
    Dim tempSendToAddress As String
    Dim subject As String
    Dim tempProjectID As String
    Dim LPosition As Integer
    Dim FirstName As String
    Dim tempBody As String
    Dim ToClientDate As Date
    Dim ProjNo As String
    Dim PName As String
    Dim ClientCo As String
    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem
    Set dbCurr = CurrentDb()
    SQL1 = "SELECT tblDisposals.RooToClientForSigDate, " & _
                "tblMain.ProjectNo, " & _
                "tblMain.ClientCompany, " & _
                "tblMain.[PSR Name], " & _
                "tblDisposals.ProjectNo, " & _
                "tblDisposals.RooToClientForSigCheckbox, " & _
                "tblDisposals.RooClientSigCheckbox, " & _
                "tblMain.FoldersToPermanentFileCheckbox " & _
            "FROM tblMain INNER JOIN tblDisposals " & _
                "ON tblMain.ProjectNo = tblDisposals.ProjectNo " & _
            "WHERE tblDisposals.RooToClientForSigCheckbox=True AND " & _
                "tblDisposals.RooClientSigCheckbox=False AND " & _
                "tblMain.FoldersToPermanentFileCheckbox=False " & _
            "ORDER BY tblDisposals.RooToClientForSigDate, tblMain.ProjectNo;"
    Set rs1 = dbCurr.OpenRecordset(SQL1)
    If rs1.RecordCount <> 0 Then 'Test to see if recordset is empty. If empty, quit.
    rs1.MoveFirst
        Do
            ToClientDate = rs1.Fields(0)
            number = DateDiff("d", ToClientDate, Date)
            If (number Mod 7) = 0 Then
                ' Get first name out of the full name
                PName = rs1.Fields(3)
                LPosition = InStr(1, PName, " ")
                FirstName = Mid(PName, 1, LPosition - 1)
                ' Get PSR's email address.
                SQL2 = "SELECT tlkpPSR.PSREmailAddress, tlkpPSR.PSRName " & _
                    "FROM tlkpPSR " & _
                    "WHERE PSRName = '" & PName & "';"
                Set rs2 = dbCurr.OpenRecordset(SQL2)
                tempSendToAddress = rs2.Fields(0)
                ' Define the subject of the email.
                ProjNo = rs1.Fields(1)
                ClientCo = rs1.Fields(2)
                tempProjectID = Left$(ProjNo, 4) & ":" & Right$(ProjNo, 4)
                subject = number & " Day Reminder:  ROO for " & tempProjectID & " - " & ClientCo
                ' Define the body of the email. (three options)
                Select Case number
                    Case 1 To 14
                        tempBody = FirstName & "," & _
                            vbCrLf & vbCrLf & _
                            "The ROO for " & tempProjectID & " was submitted to you " & _
                            number & " days ago (on " & ToClientDate & ") to send to " & _
                            ClientCo & " for their signature.  Please obtain the signed ROO " & _
                            "from " & ClientCo & " and submit it for filing." & _
                            vbCrLf & vbCrLf & _
                            "Thank you," & vbCrLf & vbCrLf & _
                            "Brian Lundell"
                    Case 15 To 28
                        tempBody = FirstName & "," & _
                            vbCrLf & vbCrLf & _
                            "The ROO for " & tempProjectID & " was submitted to you " & _
                            number & " days ago (on " & ToClientDate & ") to send to " & _
                            ClientCo & " for their signature.  Please obtain the signed ROO " & _
                            "from " & ClientCo & " as quickly as possible and submit it for filing." & _
                            vbCrLf & vbCrLf & _
                            "Thank you," & vbCrLf & vbCrLf & _
                            "Brian Lundell"
                    Case Is > 29
                        tempBody = FirstName & "," & _
                            vbCrLf & vbCrLf & _
                            "The ROO for " & tempProjectID & " was submitted to you " & _
                            number & " days ago (on " & ToClientDate & ") to send to " & _
                            ClientCo & " for their signature.  Please obtain the signed ROO " & _
                            "from " & ClientCo & " immediately and submit it for filing." & _
                            vbCrLf & vbCrLf & _
                            "Thank you," & vbCrLf & vbCrLf & _
                            "Brian Lundell"
                End Select
                Set olMailItem = olApp.CreateItem(0)
                With olMailItem
                    .BodyFormat = olFormatHTML
                    .To = tempSendToAddress
                    .CC = "aborland@qaltek.com"
                    .BCC = "brian.lundell@qaltek.com"
                    .subject = subject
                    .Body = tempBody
                    .Send
                End With
            End If
            rs1.MoveNext
        Loop Until rs1.EOF
    End If
    Set olMailItem = Nothing
    Set olApp = Nothing
    Set dbCurr = Nothing
    Set rs1 = Nothing
    Set rs2 = Nothing
    End Sub

    I really appreciate your time.  Someday I hope to be at the knowledge level of a Microsoft MVP.

    Brian


    Brian Lundell

    Wednesday, January 23, 2013 1:49 PM
  • Okay, that verifies that you are using the Outlook Object Model.  It seems to me that there's something wrong with Microsoft Security Essentials and/or Windows.  Try using another AV program or testing your code on another PC.

    This article has some useful information as well:

    http://office.microsoft.com/en-us/outlook-help/i-get-warnings-about-a-program-accessing-email-address-information-or-sending-email-on-my-behalf-HA010355062.aspx


    Eric Legault MVP (Outlook)
    About me...
    Outlook Appins: Store Social Media fields in your Outlook Contacts!

    • Proposed as answer by Dummy yoyo Sunday, January 27, 2013 11:01 AM
    Wednesday, January 23, 2013 4:39 PM
  • Thanks, Eric.  I'm just reading the article.  I decided to run the program with Outlook already open.  It worked.  Interesting.  Another clue.

    Thanks for your help!  If it weren't for the generosity of people like you on forums I would struggle much more with programming.


    Brian Lundell

    Wednesday, January 23, 2013 4:55 PM
  • I've tested, and need help.

    I restarted the computer and the issue of Security Essentials not showing as valid in the Trust Center went away.  When I run the code, it doesn't change it to invalid.

    If I run the code to send the emails with Outlook OPEN, the code runs fine, and the emails are sent.  If I run the code with Outlook CLOSED, it produces the security message saying a program is trying to send an email.

    So, it appears I need to find a way to force the code to use Outlook in Administator mode when the code runs.  Has anyone run into this, or have any idea how to run Outlook in Administrator mode from vba?


    Brian Lundell


    Wednesday, January 23, 2013 6:46 PM
  • There is no "administrator" mode for Outlook in terms of running code.  As this article explains, cross-process code should not show the security prompts if the outlined conditions are met.

    Security Behavior of the Outlook Object Model:
    http://msdn.microsoft.com/en-us/library/office/ff864479(v=office.14).aspx

    I can't explain why the prompt is generated only when Outlook is closed; that's not expected behavior.  But you should do this: Don't use and declare olApp = New Outlook.Application right away.  Just declare it and use GetObject("", "Outlook.Application") to get an Outlook.Application object.  If it returns null, Outlook is closed; otherwise it's open.  If Outlook is closed, add this after Set olApp = New Outlook.Application:

    Dim olNS As Outlook.NameSpace
    
    Set olNS = olApp.GetNameSpace("MAPI")
    
    olNS.Logon



    Eric Legault MVP (Outlook)
    About me...
    Outlook Appins: Store Social Media fields in your Outlook Contacts!

    • Proposed as answer by Dummy yoyo Sunday, January 27, 2013 11:01 AM
    • Unproposed as answer by Brian Lundell Sunday, January 27, 2013 5:07 PM
    Wednesday, January 23, 2013 7:25 PM
  • I read through the Security Behavior article, but still could not get the code to work.  I'm not sure I understood all of the article and how the code is supposed to work.  

    When I could not get it working properly, I decided to try a work-around using Windows Task Manager.  I set it to open Outlook first, then open Access to run the code, give Access a minute or two to finish, then close Access, and then close Outlook.  It seems to be working, although it is far from the optimal solution.

    In the future, if I have time to address this more fully, I hope to resolve the security issue and contain the entire process within vba, without using Task Manager.  

    Thanks, Eric, for your help!


    Brian Lundell

    • Marked as answer by Dummy yoyo Thursday, January 31, 2013 9:43 AM
    Sunday, January 27, 2013 5:13 PM
  • Hi, If I am right, please consider to check this option:

    Start/Access Options/Trust Centre/Click Trust Centre Settings/Macro Settings/Enable All Options (not recommended)

    or create Self Certificate through SELFCERT.EXE

    Hope this should solve (sorry, If I have wasted your time)

    Regards
    Repath.


    Love the Love that Loves the Love and Hate the Love that Loves the Hate.

    Monday, January 28, 2013 3:40 PM
  • The gist of the article is basically to check the prerequisites (anti-virus software) so that trusted code can run without a prompt.  But you should modify your code in the way I suggested earlier.

    I have no idea what you are talking about re: Task Manager. It sounds like you are runing a .bat file that's launching Access and Outlook?  Again - look at my code.


    Eric Legault MVP (Outlook)
    About me...
    Outlook Appins: Store Social Media fields in your Outlook Contacts!

    Monday, January 28, 2013 4:06 PM
  • Sorry for bump in.

    Brian, I did a simple test to auto outlook from Access.The following code works fine no matter outlook is closed or opened.

    Also, I noticed that this emailing is processed at night. Is it started via Task Scheduler? If so, you may have a look at this KB:

    Considerations for server-side Automation of Office 

    Hope it helps.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 29, 2013 3:39 AM
  • Yoyo Jiang

    I was researching code to send email by way of MS Access and found these posts under "Automatically Send Email via VBA" re: an Access 2010 code to send via Outlook.

    I am working on very similar problem.  In your post, you noted "The following code works fine no matter outlook is closed or opened."  However, I did not see any additional code.  I am looking for the final working solution...  the posts overall were very helpful from all!

    thanks!

    Thursday, December 19, 2013 8:07 PM