0

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Ritwik Dey
  • 559
  • 1
  • 7
  • 17

1 Answers1

2

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
0m3r
  • 12,286
  • 15
  • 35
  • 71
  • Thanks for the info. Added code in 'ThisOutlookSession' and saved VB project. However, changes did not take effect. I know this is very less information but could you please help me further? – Ritwik Dey Mar 17 '17 at 06:26
  • @RitwikDey remember whenever you are working with a ThisOutlookSession you must restart your outlook in order for it to work – 0m3r Mar 17 '17 at 06:34
  • Thanks for replying. I tested it as a new macro. The popup is coming. I added the code that you gave inside 'olApp_ItemSend' method, in the new macro. However, I had to comment out all excel related code, because : `Dim xlApp As Excel.Application Dim xlBook As Workbook` these 2 lines of code are throwing error : compile error: User defined type not defined' – Ritwik Dey Mar 17 '17 at 06:46
  • Like I said, popup is coming. But only during testing. I restarted Outlook 2007, but it still isn't coming on click of send button. I replaced application-startup() with Initialize_handler(). No effect. – Ritwik Dey Mar 17 '17 at 06:56
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/138297/discussion-between-ritwik-dey-and-0m3r). – Ritwik Dey Mar 17 '17 at 06:59