VBA Outlook Run Excel Macro When Received email in Outlook Subfolder

[CHHIEW]

Hi,
I want to trigger excel macro TEST12.xlsm when received en email with subject title "Test", it work when the mail arrived in inbox folder, but not in subfolder.

Can anyone enlighten me or point me to the correct sub folder path? The subfolder name is "Biz Ops Report" and I am using below macro:

Thank you so much.

Private WithEvents Items As Outlook.Items


Private Sub Application_Startup()
  Dim olApp As Outlook.Application
  Dim objNS As Outlook.NameSpace
  Set olApp = Outlook.Application
  Set objNS = olApp.GetNamespace("MAPI")
  ' default local Inbox
  Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
'Set Items = Items.Folders("Biz Ops Report")
End Sub


Private Sub Items_ItemAdd(ByVal item As Object)


 On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
Dim xlApp As Object
Dim oxl As Excel.Application
Dim owb As Excel.Workbook
Dim wsheet As Excel.Worksheet
Dim asd As Object
Dim ExApp As Excel.Application
Dim ExWbk As Workbook
Dim myDestFolder As Outlook.Folder




 If TypeName(item) = "MailItem" Then


Set Msg = item


    If Msg.Subject = "Test" Then


        Set ExApp = New Excel.Application
        Set ExWbk = ExApp.Workbooks.Open("d:\TEST12.XLSM")
        ExApp.Visible = False


        ExWbk.Application.Run "Module1.SAY"


        Dim myNameSpace As Outlook.NameSpace
        Dim myInbox As Outlook.Folder


     '   Set myNameSpace = Application.GetNamespace("MAPI")
      '  Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
      '  Set myItems = myInbox.Items
      '   Set myDestFolder = myInbox.Folders("Test")


   '     **Msg.Move myDestFolder**
'Not working


     End If


End If
ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
  End Sub

 

[CHHIEW]

[SOLVED] Re: VBA Outlook Run Excel Macro When Received email in Outlook Subfolder

Hi

After many trial and error, i got it working by adding below line, yahoo.

Set Items = objNS.Folders("abc@abc.com").Folders("sub folder1").Folders("Biz Ops Report").Items