In Excel 2003 VBA email macro works fine but no information to user. How do I add this to the macro

Posted on 2014-07-16
Last Modified: 2014-11-11
This code works fine:
Sub Send_Email_Current_Workbook()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "XXX Update"
.Body = "See attached"
.Attachments.Add ActiveWorkbook.FullName
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Open in new window

but the only confirmation is seeing an email in the Sent items. Just something to say that it has been sent would be fine, user input is not necessarily needed.

Robin, London
Question by:Pitfour
    LVL 9

    Accepted Solution

    You could use a messagebox. Something like:
    MsgBox "E-mail is sent. ", vbInformation

    Open in new window

    Place it after the .Send command you use. and remove the On Error Resume Next on line 9, because that would cause the messagebox to Always be displayed.

    Notice: Excel does not know if the mail is delivered to the recipient.
    LVL 44

    Expert Comment

    by:Martin Liss
    I've requested that this question be deleted for the following reason:

    Not enough information to confirm an answer.
    LVL 9

    Expert Comment

    I think the asker could certainly give the user information about the fact that a e-mail message has been sent.
     So I think the question is answered.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    755 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

    20 Experts available now in Live!

    Get 1:1 Help Now