Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
611 views
in Technique[技术] by (71.8m points)

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

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:est.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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...