Upon receiving email in outlook, open one of the attachments in excel and run a macro

[emoandy05]

Thanks in advance for you help.

Title says it all.

I have an email I receive in outlook every day. It's called "Report 01/14/2016". The date changes daily.

Within the email are two excel attachments. The one I need is called "Detail 0114". The date changes daily.

I would like outlook to automatically open this attachment in excel and run a macro I have written.

Any ideas how to do this best?

Thank you.

 

[tonyyy]

The code below might get you started.

A few comments...
The code should be placed into a module in the Outlook <acronym title="visual basic for applications">VBA</acronym> Project window;
Set a reference to the Microsoft Excel 14.0 Object Library;
Set a reference to the Microsoft Outlook 14.0 Object Library;
To set the trigger:
[TABLE="class: cms_table, width: 716"]
<tbody>[TR]
[TD] 1. Click on Rules / Create Rule / Advanced Options…[/TD]
[/TR]
[TR]
[TD] 2. Checkmark "with specific words in the message header" [enter Report][/TD]
[/TR]
[TR]
[TD] 3. Checkmark "run a script" [select Project1.UponMailReceipt][/TD]
[/TR]
[TR]
[TD] 4. Finish[/TD]
[/TR]
</tbody>[/TABLE]

Sub UponMailReceipt(myItem As Outlook.MailItem)

'Set reference to Microsoft Outlook 14.0 Object Library
'Set reference to Microsoft Excel 14.0 Object Library
 
Dim myNameSpace As NameSpace
Dim myFolder As Folder
Dim myAttachment As Outlook.Attachment
Dim xlApp As Object
Dim xlWB As Object
Dim xlWB2 As Object
Const File_Path As String = "C:\Temp\"

Set myNameSpace = Application.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)

On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
On Error GoTo 0
xlApp.Visible = True
 
For Each myAttachment In myItem.Attachments
    If InStr(myAttachment.DisplayName, "Detail") > 0 Then
        myAttachment.SaveAsFile File_Path & myAttachment.FileName
        Set xlWB = xlApp.Workbooks.Open(File_Path & myAttachment.FileName)
    End If
Next myAttachment

On Error Resume Next
    Set xlWB2 = xlApp.Workbooks("yourfilename.xlsm") 'Replace yourfilename with the name of your macro workbook
    If xlWB2 Is Nothing Then Set xlWB2 = xlApp.Workbooks.Open("C:\yourpath\yourfilename.xlsm") 'Replace yourpath with the path to your macro workbook
On Error GoTo 0

End Sub

So, when you receive an email with the word Report in the subject, the macro will open Excel (if it's not already open), open the mail attachment that starts with the word Detail (if it exists), then open your macro workbook (if it's not already open). You'll need to add an event or trigger to start your macro (eg, Private Sub Workbook_Open()).

Cheers,

tonyyy

 

[tonyyy]

Regarding the trigger for your macro, you could try calling your macro directly...

xlWB2.Application.Run "ModuleName.yourmacro"

The above line would go just before the End Sub.

Cheers,

tonyyy

 

[emoandy05]

Tonyyy,

Thank you very much for your help with this. I do have a few questions.

Which part of the code identifies the email based on the subject? Better than "Report", "Prelim Confidential" would be better.

Also, how would I set the two references?

Thanks again. Code looks great. I'm just learning how to integrate excel and outlook so this is a great start.

 

[emoandy05]

"Which part of the code identifies the email based on the subject? Better than "Report", "Prelim Confidential" would be better."

Nevermind this!

 

[emoandy05]

One last thing, the macro exists as an Excel add-in located on a shared drive. Currently, I have it added as a button in each excel user's toolbar. Can the add-in be called to run from the shared drive via this code?