Outlook within Excel, get return code to know if user pressed SEND

[Guest]

Question: Using the Outlook object model within Excel, can I get the return
code to know if the Sender pressed the SEND button?


I use the following code within Excel to send Outlook email. I would like
to know how to determine if the user actually pressed the SEND button within
Outlook. I will use the return code to log (or not) the activity. The
sender must have the option to send or not. I am using this form vs the send
mail dialog because this form allows the customization of the EmailBody based
on information contained on the row associated with the recipient.

I have checked both the Excel and Outlook communities and haven't found the
answer. In addition, I have done a web search using "outlook object model
email send" as the criteria and have found some very interesting stuff, but
have not been able to get an answer (that I understand). Any help would be
appreciated.

Thanks, Regards,

Gary

'
'Code segment – send an email with a custom letter based on recipient
information 'contained in the row
'
With OutMail
.to = EmailTo
.CC = ""
.BCC = ""
.Subject = EmailSubject
.Body = EmailBody
.Display
End With
'
'Now check the return code. Did the sender press the send button, or did
the user 'Close/Exit?
'
If sent Then Log_it

'done

 

[Guest]

Take a look at my blog to see an example of how to wire up Outlook to handle
e-mail events:

Eric Legault My Eggo : Getting a Handle on Your E-mails with VBA:
http://blogs.officezealot.com/legault/pages/20086.aspx

 

[Guest]

Eric:
Thank you very much for the blog reference. There is a wealth of
information contained in it -- and I found the (several) specific discussions
about how to detect if the sender actually sent the message or closed and did
not send. I have spent the last several hours trying to make it work, but
alas, have come up empty. Your coding and discussions are clear -- the
problem is with me and how to implement the code.

I work in an environment that restricts what I can do on a system. I can do
the following:

Write excel macros that are triggered by the user pressing a button. A
button is assigned to a macro. The macro resides in a module. The module is
created by an "INSERT MODULE" menu action. These are not CLASS MODULEs.

So, I'm struggling with where to put your example code, because it is not
allowed in the everyday garden variety Module. I can put it in a CLASS
MODULE, but then I can't figure out how to reference it from a button on the
spreadsheet.

I have been using email code from Excel for quite some time, and it works
wonderfully (and, by the way, I dont seem to have the security problem that a
lot of people reference!!!) Your help is greatly appeciated, and a little
bit more to get me over the hurdle would also be greatly appreciated -- and a
great learning experience for me.

Thanks, Regards,
Gary

P.S. I believe the final answer warrants publishing to both the Excel and
Outlok communities.

 

[Guest]

Okay, it's a little different if you are hosting this code in Excel. You
need to map that custom button to a procedure of your choice in whatever
module that procedure is living in. Then instantiate the trapper class
(you'll still need to create that Class Module with all the code) from that
proc. E.g.:

[code below in 'Module1']

Option Explicit

Public myMailItemTrapper As clsMailItemTrapper

Sub MyCustomButtonWillRunThisProcedure()

Set myMailItemTrapper = New clsMailItemTrapper

End Sub

To be clean, you should call Set myMailItemTrapper = Nothing from the
appropriate Deactivate event when the app or workbook is closed.

--
Eric Legault - Outlook MVP, MCDBA, MCTS (SharePoint programming, etc.)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/