How do I catch the user triggered Send event of a programmatically created Outlook Mailitem using VBA

Posted on 2011-10-15
Last Modified: 2012-05-12
I'm currently working on an application based on Excel 2010 and VBA.
An important functionality of the application, is the ability to create and display
an Outlook mailitem to the user and record the contents of the created mailitem
when it is manually sent by the user (the send button of the displayed mailitem is clicked
by the user). To clarify:

1. The Excel application creates and displays to the user an Outlook Mailitem
2. The user alters the Mailitem and manually sends the Mailitem (clicks send)
3. The applications is supposed to catch the manual send event and create a copy
of the Mailitem

While most of this is relatively simple to implement - I use a class module and declarations
using the WithEvents statement as recommended - I can't seem to get the Send event to
trigger when the mailitem is manually sent. Note that the send event fires perfectly as it
should when the mailitem is programmatically sent using the Send method of the MailItem

So is there another and effective way of catching the send event of a created Mailitem
when it is manually sent?

I have attached an example of a Class Module (I would think ought to work) handling the
send event catching. The instance of the class module will of course not lose state after
creating the mailitem.

I hope someone is able to help.

Option Explicit

Private WithEvents mOutlook As Outlook.Application
Private WithEvents mMailItem As Outlook.MailItem

Private Sub Class_Initialize()
    Set mOutlook = New Outlook.Application
End Sub

Public Sub CreateAndDisplayMailItem()
    Set mMailItem = mOutlook.CreateItem(olMailItem)
    With mMailItem
        .To = ""
        .Subject = "Test"
        .Body = "Test Body"
    End With
End Sub

Private Sub mMailItem_Send(Cancel As Boolean)
    mMailItem.SaveAs "c:\test\test.msg", OlSaveAsType.olMSG
    Debug.Print "Copy Saved"
End Sub

Private Sub Class_Terminate()
    Set mMailItem = Nothing
    If Not mOutlook Is Nothing Then mOutlook.Quit
    Set mOutlook = Nothing
End Sub

Open in new window

Question by:SwiftVBA
    LVL 13

    Accepted Solution

    Whenever you send an email item via Outlook it is sent via a code block called:

        Private Sub Application_ItemSend(ByVal Item As Object, _
                                           Cancel As Boolean)

    So this code runs EVERY time an email item is sent.

    If you create a macro withion Outlook called exactly that "Application_ItemSend"
    and place any code you like between that header and the "end sub" then that code will run every time you send an email (it actually overrides Outlook's existig code). It is important you do not change the name of the subroutine otherwise it will not run.

    For example, I have the following code to automatically run another bit of code I have written called "RemoveWords" as follows:

       Private Sub Application_ItemSend(ByVal Item As Object, _
                                           Cancel As Boolean)
       end sub

    That code looks ta any email item being sent out and removes the word "Spam" if it exists in the email's Subject line. It was used to answer another person's question about catching email being sent out.

    As a matter of interest, the same sort of thing can be perdormed on any email just received within outlook by using the following code segment:

      Private Sub Application_NewMail1()
           .......    'place yourc code in here
      end sub

    These code segment runs wnever ANY email is sent/received whether it is manually or via other code.

    Is this the sort of advice you are after?
    LVL 13

    Expert Comment

    by:Chris Raisin
    Oops!...That second segment of code should read:

           Private sub Application_NewMail()

    (not NewMAil1)

    Sorry about that

    Chris - Melbourne, Australia

    Author Comment

    Thanks for your suggestion, and yes, this works without any problems using Outlook only. However I'm controlling Ouitlook from Excel, which apparently changes the way of things. I am also familiar with the ItemSend event of the Outlook.Application class, and I've tried using that too, but the problem persists.

    Of course a solution would be to write an ItemSend event that overrides the native one in Outlook for each user of the application, but I would avoid that i possible since it is a little cumbersome since there will be many users.

    Assisted Solution

    Guess what, I forgot a very important part in my code. Even though I explicitly wrote "The instance of the class module will of course not lose state after creating the mailitem", I actually forgot to write the code in a way in which it didnt loose state. After declaring the class module (code originally posted) as a public variable in a code module the events fired perfectly even when using the Outlook object model from Excel. Sorry for your time craisin.

    Author Closing Comment

    I identified that I had overlooked an important part of the code, and thus in some way solved my own problem.
    LVL 13

    Expert Comment

    by:Chris Raisin
    Glad you worked it out, and thanks for the points. My comments will probably help others who are not as aware as us as to how Outlook works.


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
    Set OWA language and time zone in Exchange for individuals, all users or per database.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    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

    18 Experts available now in Live!

    Get 1:1 Help Now