Automatically open a copy of the attached file when I open a specific message with subject on specific outlook folder

Question

I created a custom folder on outlook with name “MyTemplate” and inside that folder an email message with subject Auto Plan ( It is a template email with oft extension) ,
and inside that email an excel workbook.
For automation purpose, I need after I open that email message, then a copy of the attached workbook will be opened automatically.
I found the below code, But I cannot utilize it to fulfil my need.
Notes: for testing purpose I set outlook and excel macro security settings to “Enable all macros”.
That is my own email meassge (I fully trust) ,I also added the a personal digital certificate to the workbook and ThisOutlookSession. I am using outlook 2016 32Bit with Windows 10 64Bit.
As always, grateful for any assistance.

Public WithEvents myItem As Outlook.MailItem
Public EventsDisable as Boolean
 
Private Sub Application_ItemLoad(ByVal Item As Object)
    If EventsDisable = True Then Exit Sub
    If Item.Class = olMail Then
        Set myItem = Item
    End If
End Sub
 
Private Sub myItem_Open(Cancel As Boolean)
    EventsDisable=True
   'Your code
    EventsDisable=False
End Sub

Answer 1

1. As I tried suggesting in my comments, you should modify Outlook Macro Security Settings to 'Notifications for all macros'. Then, the session must be closed and reopen choosing Macro Enabled.

2. Copy the next code on top of the previous Sub:

Option Explicit

Public WithEvents MyItem As Outlook.MailItem
Public EventsDisable As Boolean

Private Sub Application_ItemLoad(ByVal Item As Object)
    If EventsDisable = True Then Exit Sub
    If Item.Class = olMail Then
        Set MyItem = Item
    End If
End Sub
Private Sub myItem_Open(Cancel As Boolean)
    EventsDisable = True
        If MyItem.Subject = "Auto Plan" And Application.ActiveExplorer.CurrentFolder.Name = "MyTemplate" Then
            If MyItem.Attachments.Count > 0 Then
                Dim obAttach As Attachment, strSaveMail As String, objExcel As Object
                Set obAttach = MyItem.Attachments(1)
                strSaveMail = "C:\Teste VBA Excel\outlook-attachments\"
                obAttach.SaveAsFile strSaveMail & obAttach.DisplayName
  
                Set objExcel = CreateObject("Excel.Application")
                objExcel.Workbooks.Open strSaveMail & obAttach.DisplayName
                objExcel.Visible = True: AppActivate objExcel.ActiveWindow.Caption
                Set objExcel = Nothing
            End If
        End If
    EventsDisable = False
End Sub

The Open event previously save the attachment workbook, creates an Excel session, makes it visible and open it there. It, probably, could get the existing session, but I was working there on a project and I did not dare to risk accidentally closing it...

Please, test it and send some feedback. It, probably, can be optimized, but I only tried obtaining a workable solution. It worked on my environment...

Answer 2

The ItemLoad event is fired when the Outlook item begins to load into memory. Data for the item is not yet available, other than the values for the Class and MessageClass properties of the Outlook item, so an error occurs when calling any property other than Class or MessageClass for the Outlook item returned in Item.

Instead, I'd suggest handling the SelectionChange event of the Explorer class which is fired when the user selects a different or additional Microsoft Outlook item programmatically or by interacting with the user interface. This event also occurs when the user (either programmatically or via the user interface) clicks or switches to a different folder that contains items, because Outlook automatically selects the first item in that folder.

Public WithEvents myOlExp As Outlook.Explorer 
 
Public Sub Initialize_handler() 
 Set myOlExp = Application.ActiveExplorer  
End Sub 
  
Private Sub myOlExp_SelectionChange()
 MsgBox myOlExp.Selection.Count & " items selected." 
End Sub

In the event handler you could check the Explorer.CurrentFolder property which returns a Folder object that represents the current folder displayed in the explorer.

If you need to handle inspector windows also you need to use the Inspectors.NewInspector event which is fired whenever a new inspector window is opened, either as a result of user action or through program code.