Excel Vba - Outlook Mail Routine - Mail in Outbox and verify it is open

Posted on 2012-08-22
Last Modified: 2012-08-23
Hello Experts:

I have a routine in VB to email out but I have an issue with the email staying in the Outbox and not automatically being pushed.  It will eventually send if I wait long enough if Outlook stays open or when I re-initialize it. (Since it is set to close Outlook, if it was not Open)

Is there a way to get this routine to give it a little nudge or kick to push it? This will resolve my problem.  Also, I want to verify that the code is correct to check if there is an instance of Outlook open and if not - Open it, send mail and then close it.  (But to empty the Outbox first)

All my end-users are using Office 2010 (Outlook & Excel)

Thank You!


Sub CheckOutlookSend()

    On Error Resume Next
    Set oOutlook = GetObject(, "Outlook.Application")
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error GoTo 0
     With OutMail
     .To = ""
     .Subject = "MonthEnd Data"
     .Attachments.Add ActiveWorkbook.FullName
     End With
     Set oOutlook = Nothing
     Set OutMail = Nothing
     Set OutApp = Nothing
End Sub
Question by:mike637
    LVL 11

    Expert Comment

    This is by design.  

    It requires sendkeys to get around it.

    Honestly the best way is to not use outlook, but to use SMTP.

    Then as long as the computer it is on is allowed to relay and you have the correct connection string it works much smoother.  

    If you're worried about getting a copy to your email box, just cc yourself on all the emails.


    Author Comment


    How about this - can I modify the code to keep Outlook Open and not auto-close it immediately, similair to how it is if an instance of it is already running?

    I then can add a Msg Box to say not to close Outlook.  Which leads me to ask - is there anyway to have a Msg Box appear - and stay on screen for 10 seconds and then close and exit the routine?

    But on the Outlook - I am too ignorant to see where it closes it. So I need some guidance on what to rem out or change.  And then the issue with a time-delayed MsgBox.

    LVL 11

    Expert Comment


    Because of all the spam that goes out these days, Outlook has been designed to make automatic sending of emails basically impossible without human or sendkeys intervention.  

    I highly recommend using SMTP to send your emails instead of Outlook.  There is no good reason to directly use Outlook.

    here is a block of SMTP code for sending email.  The only thing you need is a user name and password for the SMTP mail server, and permission to be able to relay.


    Function CDO_Mail_Small_Text(email As String, ttw As String)
    On Error GoTo emailnotsent
        Dim iMsg As Object
        Dim iConf As Object
        Dim strbody As String
        Debug.Print email
        Debug.Print ttw
        '    Dim Flds As Variant
        Set iMsg = CreateObject("CDO.Message")
        Set iConf = CreateObject("CDO.Configuration")
            iConf.Load -1    ' CDO Source Defaults
            Set Flds = iConf.Fields
            With Flds
                .Item("") = 2
                .Item("") _
                               = ""
                .Item("") = 25
                .Item("") = 1
                .Item("") = "username"
                .Item("") = "password"
            End With
        'Prevent alerts from displaying to users
        Me.Application.DisplayAlerts = False
        'turns off screen updating for speed gains
        Me.Application.ScreenUpdating = False
        strbody = "this sends an HTML email  so <b> HTML codes</b> can be used."
                  '"This is line 3" & vbNewLine & _
                  '"This is line 4"
        With iMsg
            Set .Configuration = iConf
            .To = email
            .CC = ""
            .BCC = ""
            .From = """sender"" <>"
            .Subject = "How much better SMTP is then Outlook for sending emails automatically"
            .HTMLBody = strbody
        End With
        'Activates alerts displaying to users
        Me.Application.DisplayAlerts = True
        'turns on screen updating for speed gains
        Me.Application.ScreenUpdating = True
        Exit Function
        'Activates alerts displaying to users
        Me.Application.DisplayAlerts = True
        'turns on screen updating for speed gains
        Me.Application.ScreenUpdating = True
        On Error GoTo 0
    End Function

    Open in new window


    Author Comment

    I will give this a try and adjust with pertinent username(s), password(s).

    But where in the code does it state to include the attachment.  This was the whole basis of the routine to take the newly created workbook (after copying the sheet out of a workbook into a new workbook as a single sheet - then kill it at the end of the routine).  The attachment is the most important part.

    I am confused on this attachment piece - please advise.

    Thank you,
    LVL 11

    Accepted Solution

    .AddAttachment "path\filename"

    in the imsg part.

    Author Closing Comment


    Thank you for your help and education into this new area for me.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    IDFix X500 addresses 5 17
    Copy Value of cell in formula 1 25
    Multiple Contact Sources Causing Search Dupes 2 13
    Search Box 13 27
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now