Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-15
6
Medium Priority
?
2,120 Views
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
class.

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.

Andreas
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 = "some@address.com"
        .Subject = "Test"
        .Body = "Test Body"
        .Display
    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

0
Comment
Question by:SwiftVBA
  • 3
  • 3
6 Comments
 
LVL 13

Accepted Solution

by:
Chris Raisin earned 2000 total points
ID: 36973820
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)
        RemoveWords
   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?
0
 
LVL 13

Expert Comment

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

       Private sub Application_NewMail()

(not NewMAil1)

Sorry about that

Cheers
Chris - Melbourne, Australia
(craisin)
0
 

Author Comment

by:SwiftVBA
ID: 36974109
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Assisted Solution

by:SwiftVBA
SwiftVBA earned 0 total points
ID: 36974125
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.
0
 

Author Closing Comment

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

Expert Comment

by:Chris Raisin
ID: 36976062
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.

Cheers
Chris
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This article will help to fix the below errors for MS Exchange Server 2016 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
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 …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

572 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