VBA code in MS Outlook 2007 to create custom popup alert

Question

I want to create a custom popup alert in MS Outlook 2007, that appears on click of the "Send" button. It should have 2 choices : Yes and no. On clicking "Yes", I want the system to open a particular excel sheet.

Is this possible by adding a VBA code snippet to my Outlook desktop client? I do not know how to go about achieving this. Any lead is appreciated.

Answer 1

Work with Application.ItemSend Event and simple MsgBox Function

Also look at this answer Finding a workbook in one of multiple Excel instances

Example Code goes under ThisOutlookSession

Public WithEvents olApp As Outlook.Application

Private Sub Application_Startup()
    Set olApp = Outlook.Application
End Sub

Private Sub olApp_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim xlApp As Excel.Application
    Dim xlBook As Workbook
    Dim Prompt As String

    Prompt = "Open Excel File?"

    If MsgBox(Prompt, vbYesNo + vbQuestion, _
                            "Sample") = vbNo Then
        Cancel = True
    Else
        Set xlApp = New Excel.Application
        Set xlBook = xlApp.Workbooks.Open( _
                               "C:\Temp\Temp.xlsm")
        xlApp.Visible = True

    End If

    Set xlApp = Nothing
    Set Book = Nothing
End Sub