Microsoft Office Forums - View Single Post - copy data from outlook attached excel file into a local one

gmayor · 09-17-2014, 10:58 PM

That would be fairly straightforward. You would need to set an Outlook rule to identify the incoming messages and run a script from that rule. The script would save then open the attachment in Excel, then open the target workbook and do whatever processing you require before closing the attachment and optionally deleting it from the filing system. Something like the following:

Code:

Sub ProcessAttachment(Item As Outlook.MailItem)
Dim olAtt As Attachment
Dim strFilename As String
Dim xlApp As Object
Dim xlWB As Object
Dim xlTarget As Object
Const strTarget As String = "C:\Path\TargetBookName.xlsx"
    If Item.Attachments.Count > 0 Then
        For Each olAtt In Item.Attachments
            If Right(olAtt.Filename, 4) = "xlsx" Then
                strFilename = "C:\Path\" & olAtt.Filename
                olAtt.SaveAsFile strFilename
                Set xlApp = GetObject(, "Excel.Application")
                If Err <> 0 Then
                    Set xlApp = CreateObject("Excel.Application")
                End If
                On Error GoTo 0
                Set xlWB = xlApp.workbooks.Open(strFilename)
                Set xlTarget = xlApp.workbooks.Open(strTarget)
                '+++++++++++++++++++++++++++++
                'Do stuff with the 2 workbooks
                '+++++++++++++++++++++++++++++
                xlWB.Close 0        'Close attachment without saving
                'Kill strFilename 'Optional delete the saved attachment
                Set xlWB = Nothing
                Set xlTarget = Nothing
                Set xlApp = Nothing
                Exit For
            End If
        Next olAtt
    End If
End Sub