VBA: interaction Outlook/Excel, save mail in a folder and trigger a macro

[Nelson78]

Hello everybody.

Every hour I receive a mail from the address example@example.com (no others mail are received from this address).

I need two things:
1) save the attachment in a specified folder (received in csv.7z, saved in xls);
2) trigger a macro from vba Excel that is supposed to elaborate the data in the saved file.

What I've performed until now is what follows (receive the e-mail and alert with a popup).



Reported in ThisOutlookSession:

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
End Sub
Private Sub Items_ItemAdd(ByVal item As Object)

  On Error GoTo ErrorHandler
  Dim Msg As Outlook.MailItem
  If TypeName(item) = "MailItem" Then
    Set Msg = item

If item.SenderEmailAddress = "example@example.com" Then MsgBox "MAIL RECEIVED"

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

How can I proceed further?

 

[Nelson78]

Any idea?

 

[Nelson78]

In this way the msgbox is correctly shown, then Error 438"Object Doesn't Support This Property or Method"

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
End Sub
Private Sub Items_ItemAdd(ByVal item As Object)


  On Error GoTo ErrorHandler
  Dim Msg As Outlook.MailItem
            Dim Atmt As Attachment
            Dim FileName As String
  
  If TypeName(item) = "MailItem" Then
    Set Msg = item
     
    If item.SenderEmailAddress = "example@example.com" Then
        MsgBox "MAIL RECEIVED"
        If item.Atmt.Count = 1 Then
            FileName = "C:\Users\john.smith\Desktop\" & Atmt.FileName
            Atmt.SaveAsFile FileName
        End If
    End If
       
 
  
  End If
ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

 

[John_w]

Use the Msg variable:

    If Msg.SenderEmailAddress = "xxxx" Then
        If Msg.Attachments.Count = 1 Then
            Filename = "C:\Users\john.smith\Desktop\" & Msg.Attachments.Item(1).Filename
            Msg.Attachments.Item(1).SaveAsFile Filename
        End If
    End If

 

[Nelson78]

Use the Msg variable:

    If Msg.SenderEmailAddress = "xxxx" Then
        If Msg.Attachments.Count = 1 Then
            Filename = "C:\Users\john.smith\Desktop\" & Msg.Attachments.Item(1).Filename
            Msg.Attachments.Item(1).SaveAsFile Filename
        End If
    End If

Yes, well, thank's.

The file is downloaded: now how can I trigger the Excel Macro to elaborate the contained data?

Maybe it's better to do a step backward: I'm starting the process from Outlook VBE, is it right?
Or should I set all the job in Excel?

I mean: how can I automatically connect Outlook to Excel?

 

[John_w]

The file is downloaded: now how can I trigger the Excel Macro to elaborate the contained data?

Is the macro in the saved attachment? If so, try this:

    Dim ExcelApp As Excel.Application, ExcelWb As Excel.Workbook
    
    On Error Resume Next
    Set ExcelApp = GetObject(, "Excel.Application")
    If err.Number <> 0 Then
        Set ExcelApp = New Excel.Application
    End If
    On Error GoTo 0
    
    Set ExcelWb = ExcelApp.Workbooks.Open(FileName)
    ExcelWb.Activate
    ExcelApp.Visible = True
    ExCelApp.Run "Excel_Macro"
    ExcelWb.Close True  'close and save changes

Excel_Macro is the name of the macro in the attachment workbook, and you must set a reference to Microsoft Excel Object Library in the Outlook project.

I think the way you're doing it is correct if you want the process to be automatic, otherwise running it all from Excel would require the code to poll the Outlook inbox every few minutes to see if the email has arrived.

 

[Nelson78]

Is the macro in the saved attachment? If so, try this:

    Dim ExcelApp As Excel.Application, ExcelWb As Excel.Workbook
    
    On Error Resume Next
    Set ExcelApp = GetObject(, "Excel.Application")
    If err.Number <> 0 Then
        Set ExcelApp = New Excel.Application
    End If
    On Error GoTo 0
    
    Set ExcelWb = ExcelApp.Workbooks.Open(FileName)
    ExcelWb.Activate
    ExcelApp.Visible = True
    ExCelApp.Run "Excel_Macro"
    ExcelWb.Close True  'close and save changes

Excel_Macro is the name of the macro in the attachment workbook, and you must set a reference to Microsoft Excel Object Library in the Outlook project.

I think the way you're doing it is correct if you want the process to be automatic, otherwise running it all from Excel would require the code to poll the Outlook inbox every few minutes to see if the email has arrived.

No, unfortunately it is more complicated: the macro is in a workbook saved on my pc, this macro should grasp data from the attached workbook and then produce a report.

 

[Nelson78]

No, unfortunately it is more complicated: the macro is in a workbook saved on my pc, this macro should grasp data from the attached workbook and then produce a report.

At the next delivery, I'm going to try this in Outlook.
Than, in opening an Excel workbook, I believe I can trigger the macro.
(I'll keep you up to date).

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
End Sub
Private Sub Items_ItemAdd(ByVal item As Object)


  On Error GoTo ErrorHandler
  Dim Msg As Outlook.MailItem
            Dim Atmt As Attachment
            Dim FileName As String
  
  If TypeName(item) = "MailItem" Then
    Set Msg = item
     
    If Msg.SenderEmailAddress = "xxxx" Then
        If Msg.Attachments.Count = 1 Then
            Filename = "C:\Users\john.smith\Desktop\" & Msg.Attachments.Item(1).Filename
            Msg.Attachments.Item(1).SaveAsFile Filename
        End If
    

'**************************************************************************
'**************************************************************************
'***********OPEN WOORKBOOK WITH OUTLOOK****************
'**************************************************************************
'**************************************************************************
        
Dim xlApp As Object
Dim sourceWB As Workbook
Dim sourceWS As Worksheet

Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
.EnableEvents = False
End With



strFile = ""C:\Users\john.smith\Desktop\Report.xlsm"

Set sourceWB = Workbooks.Open(strFile, , False, , , , , , , True)
Set sourceWH = sourceWB.Worksheets("yyyyy")
sourceWB.Activate     'may be can I avoid this "Activate"?

'**************************************************************************
'**************************************************************************
'**************************************************************************
'**************************************************************************
'**************************************************************************



End If   'referred to If Msg.SenderEmailAddress = "xxxx" Then
       
 
  
  End If
ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

 

[John_w]

At the next delivery, I'm going to try this in Outlook.

Instead of waiting for the next email to arrive before testing the Outlook code, you could create a rule in Outlook to run a script and test it by running against existing emails.

With such a script you don't need the Application_Startup and Items_Add procedures, just a procedure which receives a MailItem as its argument, like this:

Public Sub Save_Attachment_Run_Report_Macro(Msg As Outlook.MailItem)

'Code here

End Sub

The rule could even specify 'if from sender xxxx and has an attachment'.