CODEWITHSUNDEEP
excelvbaoutlookexcel-2010outlook-2010
NanaB
NanaB

Reputation: 31

Open Excel attachment using Outlook 2010

I am trying to open an Excel attachment using an Outlook rule. I am using the below VBA to do this: 

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)

Dim objAtt As Outlook.Attachment
Dim xlApp As Object
Dim FileName As String

For Each objAtt In itm.Attachments
    If InStr(objAtt.DisplayName, ".xlsx") Then
        FileName = "S:\Projects\" & objAtt
        objAtt.SaveAsFile FileName
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Application.Visible = True
        xlApp.Application.EnableEvents = False
        x1App.Workbooks.Open FileName
    End If

    Set objAtt = Nothing
Next
MsgBox ("DONE")

End Sub

It opens the Excel application, however on the open filename line, it shuts the Excel application and jumps out of the code.

Also please let me know if I am doing this in an overly complicated way!

Upvotes: 2

Views: 399

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49397

It looks like you pass a wrong filepath to the Open method:

 FileName = "S:\Projects\" & objAtt

Try to use the DisplayName property of the Attachment class.

 FileName = "S:\Projects\" & objAtt.DisplayName

Also make sure that you have sufficient privileges for writing on the S drive and can access it any time. Try to specify any local drive.

Upvotes: 2

Related Questions