VBA Outlook Run Excel Macro When Received email in Outlook Subfolder

CHHIEW

New Member
Joined
Jan 6, 2017
Messages
10
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.

Code:
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
[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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top