Solved: Outlook VB macro not running on items mailed from Excel

[chris.s]

Hi all,

I've written a macro in Outlook that is triggered when the "Send" button is pushed on an email, popping up a custom form that lists who the email was addressed to and giving users one last chance to make changes before sending. Works great.

Now, when in Excel (2007 for example), and Office Button->Send->Email is selected, even though it opens an Outlook mail window, it does not call the Outlook macro when Send is pressed and just sends the email right away.

The exception to this is when the new mail window is spawned by Excel, before I actually hit send, I open and close the VB Editor window from the mail item's ribbon bar. Then it runs my macro, but it crashes Outlook, Excel throws an error and both VB and the Outlook VB Editor is unable to open afterwards. Anybody have any idea why this is happening and how to get it to run properly from Excel?

The code that calls the custom form is the following, in ThisOutlookSession:


----------------

Private Sub Application_ItemSend _
(ByVal Item As Object, Cancel As Boolean)
Dim strMsg As String
Dim popUpBox As ConfirmBox
Set popUpBox = New ConfirmBox

If Item.MessageClass = "IPM.Note" Then
strMsg = "To recipients = " & Item.To & vbCrLf & vbCrLf & _
"Cc recipients = " & Item.CC & vbCrLf & vbCrLf & _
"Are you sure you want to send this message?"

popUpBox.TextBox1.Text = strMsg
popUpBox.Show

If popUpBox.Tag = 0 Then
Cancel = True
End If

End If

Unload popUpBox

End Sub


---------------------

Thanks in advance,
-Chris

[JP2112]

Sending email through Excel using File > Send commands actually bypasses Outlook completely. The only way to get your Outlook code to run would be to instantiate Outlook to send the message (thereby adding VBA code to your workbook), or create a sending function in Excel VBA, convert your code to Excel VBA and run it from Excel.

[chris.s]

Thanks JP. Would I be able to modify the function of the file->send command to prevent it's current action and instead create a new instance of outlook, or would it have to be a separate way of sending altogether?

This is something we are trying to implement in our company so people HAVE to use this pop-up, and I'm trying to prevent any workarounds.

[JP2112]

You have to get into the wonderful world of the Ribbon. Here's one example: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/e9aadf58-dbdf-43a1-aae7-e20310880e94

Keep in mind this example is for a COM add-in. All of this for a simple message box confirming that you want to send an email? It's none of my business, but it seems like a lot of work.

[chris.s]

Yeah, I didn't realize it would be such a complicated task. At this point, I'm going to have to pass responsibility on to the user if they send the wrong email to the wrong person using Excel. Thanks for your help.