• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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

This code works fine:
Sub Send_Email_Current_Workbook()
Dim OutApp As Object
Dim OutMail As Object
 
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
 
On Error Resume Next
With OutMail
.To = "xxxxxxxx.xxxxxx@xxxx.xxxx.uk"
.CC = ""
.BCC = ""
.Subject = "XXX Update"
.Body = "See attached"
.Attachments.Add ActiveWorkbook.FullName
 
.Send
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
0
Pitfour
Asked:
Pitfour
  • 2
1 Solution
 
p_sieCommented:
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.
0
 
Martin LissRetired ProgrammerCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
p_sieCommented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now