How can I automatically run a macro when an email is sent in Outlook?

Question

The script below works great but I have to manually run the Initialize_handler routine every time I open Outlook for it to work.

Public WithEvents myOlApp As Outlook.Application

Public Sub Initialize_handler()
Set myOlApp = CreateObject("Outlook.Application")
End Sub

Private Sub myOlApp_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim prompt As String
prompt = "Are you sure you want to send " & Item.Subject & "?"
If MsgBox(prompt, vbYesNo + vbQuestion, "Sample") = vbNo Then
    Cancel = True
    End If
End Sub

As far as I can see to make this work automatically I should be able to add the below script to ThisOutlookSession. However this doesn't work and I have no idea why.

My macro security is set properly and it is running the code on startup but for some reason it doesn't work until I manually open the VBA editor click into Initialize_handler and press F5.

Private Sub Application_Startup()
  Initialize_handler
End Sub

niton · Accepted Answer · 2016-03-04T23:04:19.443

8

The convoluted method described here https://msdn.microsoft.com/en-us/library/office/ff865076.aspx indicates "The sample code must be placed in a class module".

I suggest you use the special class module ThisOutlookSession only. You could experiment with your own class module but if you just want this to work then replace all your code with this in ThisOutlookSession.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim prompt As String
prompt = "Are you sure you want to send " & Item.Subject & "?"
If MsgBox(prompt, vbYesNo + vbQuestion, "Sample") = vbNo Then
    Cancel = True
    End If
End Sub

edited Mar 04 '16 at 23:04

answered Mar 04 '16 at 22:09

niton

8,771
21
32
52

This piece of code on its own doesn't do anything even if run manually. – CocodaMonkey Mar 04 '16 at 22:26
You cannot run code that expects a parameter, **Item**, without passing a parameter. That is not the main problem though. Did you restart Outlook before testing? – niton Mar 04 '16 at 22:41
Yes but it wouldn't matter as this piece of code shouldn't do anything either way. If you replace all the code with this there is nothing ever calling it. – CocodaMonkey Mar 04 '16 at 22:46
There are many examples on this site to demonstrate this as the standard usage of ItemSend. http://stackoverflow.com/questions/23474306/how-to-retreive-the-sender-email-address , http://stackoverflow.com/questions/28698291/missing-outlook-mailitem-properties-in-application-itemsend , http://stackoverflow.com/questions/32171589/search-for-a-string-in-a-subject , http://stackoverflow.com/questions/2522884/outlook-vba-to-bcc-emails-sent-not-working-in-outlook-2007 The only thing left is to check your security settings. – niton Mar 04 '16 at 23:00
Thank you for your help. I must have had some piece of code messing it up somewhere. I got feed up and deleted everything I had including some other modules and now it does indeed work as you say. Not sure where I went wrong but thank you very much for your help. – CocodaMonkey Mar 04 '16 at 23:41

score 2 · Answer 2 · answered Mar 04 '16 at 22:02

2

You can instead put it directly in ThisOutlookSession:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    MsgBox "Sent somthing!"
End Sub

answered Mar 04 '16 at 22:02

Tim Williams

154,628
8
97
125

That does not work. In fact that code doesn't do anything all on its own even if executed manually. – CocodaMonkey Mar 04 '16 at 22:23
As @niton points out you cannot call this manually without passing in parameters – Tim Williams Mar 04 '16 at 23:05

score 1 · Answer 3 · answered Jun 09 '16 at 08:35

As simple as that. Note you need to change your outMail.Display to outMail.Display (True) and there you are, full code:

...
...
...
outMail.Display (True)

On Error Resume Next
bSent = outMail.sent 'This will NOT SEND. Used to get error.
If Err <> 0 Then
    'Email was sent. Put followed actions here.
Else
    'Email was not sent. Put followed actions here.
End If
On Error GoTo 0

Pros:

You get what you want
It's simple.

Cons:

Excel (or any other runtime you are running this code from) will freeze until you cancel or send email.

How can I automatically run a macro when an email is sent in Outlook?

3 Answers3

Linked