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
 

CHHIEW

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

Forum statistics

Threads
1,078,437
Messages
5,340,271
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top