Email created using VBA in Excel not sending

Question

I have some VBA code in Excel that creates and sends an automatic email via Outlook. However the actual message is not getting sent unless I manually open up outlook to trigger the "send/receive". Below is the code that I use to create the email. I would think that all I need is one line of code to trigger the send/receive code. However I should point out that outlook isn't opening during this code. So one way to solve the problem might be to open outlook before this code, then close it after the code.

Function RDB_Mail_PDF_Outlook(FileNamePDF As String, StrTo As String, _
                              StrCC As String, StrBCC As String, StrSubject As String, _
                              Signature As Boolean, Send As Boolean, StrBody As String)
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        If Signature = True Then .Display
        .To = StrTo
        .CC = StrCC
        .BCC = StrBCC
        .Subject = StrSubject
        .HTMLBody = StrBody & "<br>" & .HTMLBody
        .Attachments.Add FileNamePDF
        If Send = True Then
            .Send
        Else
            .Display

        End If
    End With
    On Error GoTo 0
        SendReceiveAll = True
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

When you call your function, make sure that your Send argument is set to True. — user6585761
– user6585761, Commented Jul 22, 2017 at 21:51
It is set to True. Maybe I need to check some setting or option in Outlook? — Jcarl
– Jcarl, Commented Jul 22, 2017 at 22:10
Also, I added the sendreceiveall =true line of code. It performed the same way prior to me adding that line. — Jcarl
– Jcarl, Commented Jul 22, 2017 at 22:13
Comment out (or temporary delete) On Error Resume Next, and then try it again. Do you get an error? — user6585761
– user6585761, Commented Jul 22, 2017 at 22:25

Dmitry Streblechenko · Accepted Answer · 2017-07-22 22:48:07Z

0

This is to be expected -message submission is an asynchronous process, and Outlook closes before it has a chance to send the message.

Make OutApp variable global so it does not get released when your sub finishes, and call OutApp.Session.SendAndReceive.

answered Jul 22, 2017 at 22:48

Dmitry Streblechenko

67.1k4 gold badges56 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jcarl Over a year ago

Thanks Dmitry. I tried a few things related to what you suggested, but it did not seem to work. I must be confused as to where to add in the code you said about OutApp.Session.Sendandreceive . Where should that code be located. And also where is the best place to make OutApp a global variable? At the beginning of my code or at the beginning of the function? And to confirm I should make it global by coding "public" instead of "Dim" correct? THanks in advance!!!!!

Dmitry Streblechenko Over a year ago

Declare OutApp outside of your function on the global level. Call SendAndReceive after you call Send.

Collectives™ on Stack Overflow

Email created using VBA in Excel not sending

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related