Bypass Outlook Security when sending email via Excel macro

Question

I've done an automatically Reminder to send emails from Excel, triggered by a VBS file (placed in the StartUp folder). When I open my PC, the reminder is triggered and it should send the email, but i have a security error.

This is the Excel Macro:

Sub Email()
Dim aOutlook As Object
Dim aEmail As Object
Dim i As Integer
For i = 1 To 100      
If Cells(i, 3).Value = Date And IsEmpty(Cells(i, 7)) Then  
        Set aOutlook = CreateObject("Outlook.Application") 
        Set aEmail = aOutlook.CreateItem(0)
        aEmail.Importance = 2
        aEmail.Subject = ActiveSheet.Cells(i, 4)
        aEmail.Body = ActiveSheet.Cells(i, 5)
        aEmail.To = ActiveSheet.Cells(i, 6)
        aEmail.Send
        Cells(i, 7).Value = "Sent: " & Now
End If
Next i
End Sub

This is the VBS file:

Dim xlApp, xlBook
Set wshShell = CreateObject( "WScript.Shell" )
Set xlApp = CreateObject("Excel.Application") 
userName = wshShell.ExpandEnvironmentStrings( "%UserName%" )
Set xlBook = xlApp.Workbooks.Open("C:\Users\" + userName + "\Desktop\RM.xlsm", 0, False) 
xlApp.Application.Run "Email"
xlBook.Save
xlBook.Close
xlApp.Quit 
Set xlApp = Nothing
Set xlBook = Nothing

How can I can bypass this error, by adding something more in my macro?
(I've tried all the codes from google, but none of them work. I also don't want to change nothing on Outlook Settings or Windows Reg.)

Install & use the Redemption objects dimastr.com/redemption/home.htm — Alex K.
In order to avoid that warning, you will need to either change Outlook security settings, or change a Registry value, or use a 3rd party application/plug (which would also be changing Outlook's settings in the registry}. You cannot avoid the warning by adding more code, otherwise the warning would have purpose it the first place. That's like wanting code to bypass a virus scanner. If it were possible, virus scanners would never catch viruses. Generally, it's a bad idea to bypass security settings on "anything"; they are there for a reason. The problem is that you're using a VBS. — ashleedawg
You can avoid Outlook all together with Microsoft Collaboration Data Objects (CDO). rondebruin.nl/win/s1/cdo.htm — thx1138v2

Dmitry Streblechenko Dmitry Streblechenko · Accepted Answer · 2017-11-24T23:54:46

You have a few options:

Install up-to-date antivirus software (Outlook will not display a prompt then) if you can control the environment where your code runs
Extended MAPI (C++ or Delphi, does not apply in case of VB script). You can however use a wrapper like Redemption (I am its author) that uses Extended MAPI but is accessible from any language including VBS.
A product like ClickYes.

See http://www.outlookcode.com/article.aspx?id=52 for a discussion and a list of available options.

Bypass Outlook Security when sending email via Excel macro

2 Answers