How to send mail when the .Send does not work?

2019-08-25 03:25发布

I am trying to send email through VBA.

When the .Send wouldn't work, I found the Sendkeys method which works but the email has to display and then you can't touch the computer or you may disrupt the macro.

How can I make the .Send method work?

    Dim OutApp As Object
    Dim OutMail As Object
    Dim count As Integer

    EmailTo = Worksheets("Email Addresses").Range("A2")

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItemFromTemplate( _
    Sheets("Start Here").Range("B25"))

    On Error Resume Next
    With OutMail

        .To = EmailTo
        '.CC = ""
        '.BCC = ""
        '.Subject = ""
        '.Body = ""
        '.Attachments.Ad' ActiveWorkbook.FullName
        ' You can add other files by uncommenting the following line.
        '.Attachments.Add ("C:\test.txt")
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        '.Display
        'SendKeys "^{ENTER}" ' <---this was the fix I found when .Send didn't work
        .Send

    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

1条回答
【Aperson】
2楼-- · 2019-08-25 04:08

Your email security settings are what is most likely causing the issue. In outlook>>File tab>>Options>>Trust Center>>Trust Center settings>>Programmatic Access. – Sorceri 1 hour ago

In line with what @Sorceri said, you may, or may not be able to change those settings, according to IT department. For example, in my current job, I can only display emails automatically, but cannot send them through code. Cyber-Security team will not allow it. – Scott Holtzman 56 mins ago

So it seems like the verdict is that the SendKeys is the best option because security settings that are controlled by my IT department have locked me out of the programmatic access in trust settings.

Thank you to @Sorceri and @Scott Holtzman

查看更多
登录 后发表回答