I created a custom folder on outlook with name “Waleed” and inside that folder a 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 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

CodePudding user response：

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. As I also said in my comments, a specific category should be allocated to the template in discussion. If you will find another way to limit the event code execution to it, please use it. I created a template and set "MyTemplate" as its Category, using the next code. Please, copy it also in ThisOutlookSession:

Sub AddCategoryToTemplate()
 Dim MyItem As Outlook.MailItem, templFullName As String
 'use here your real template full name:
 templFullName = "C:\Users\Fane Branesti\AppData\Roaming\Microsoft\Templates\MyFolder\Auto Plan.oft"

 EventsDisable = True 'to avoid triggering the event when the template is open...
  Set MyItem = Application.CreateItemFromTemplate(templFullName)
  With MyItem
        .Categories = "MyTemplate"
        .SaveAs templFullName, OlSaveAsType.olTemplate
        .Categories = "MyTemplate":  .Close olSave
  End With
 EventsDisable = False
End Sub

In case you did some tests with the event, I designed it in a way to not let the event being triggered when the template is open for this purpose.

3. 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.Categories = "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, create 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...

CodePudding user response：

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.