none
Save attached files RRS feed

  • Question

  • I receive a daily email that includes an attached Excel file. I then have to save that file to a directory on our local server and Delete 3 Rows form the spreadsheet and import it into MS Access to run an append query to get all new records in the spreadsheet imported to a table.  I have the import routine and the append query automated through MS Access but I would like to automate saving the file from the email anyone know how I can do this in Outlook?
    Wednesday, January 13, 2016 9:00 PM

Answers

  • Hello Ray,

    The Attachment class provides the SaveAsFile method which allows to save the attachment to the specified path.

    To process all incoming emails you can handle the NewMailEx event which is fired when one or more new items are received in the Inbox

    Anyway, I'd suggest starting from the Getting Started with VBA in Excel 2010 article in MSDN. 

    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:48 AM
    Wednesday, January 13, 2016 11:53 PM
  • >>>I have the import routine and the append query automated through MS Access but I would like to automate saving the file from the email anyone know how I can do this in Outlook?

    According to your description, Eugene Astafiev have gave your good suggestion, in addition you could refer to below code:

    Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
    
        Dim arr() As String
        Dim lngCnt As Long
        Dim olAtt As Attachment
        Dim strFolder As String
        Dim strFileName As String
        Dim strNewFolder
        Dim olns As Outlook.NameSpace
        Dim olItem As MailItem
        
        'Set working folder
        strFolder = "D:\Test"
        
        On Error Resume Next
        Set olns = Application.Session
        arr = Split(EntryIDCollection, ",")
        On Error GoTo 0
        
        For lngCnt = 0 To UBound(arr)
            Set olItem = olns.GetItemFromID(arr(lngCnt))
            'Check new item is a mail message
            If olItem.Class = olMail Then
                'Force code to count attachments
                DoEvents
                For Each olAtt In olItem.Attachments
                    'Check attachments have at least 5 characters before matching a ".xlsx" string
                    If Len(olAtt.FileName) >= 5 Then
                        If Right$(olAtt.FileName, 5) = ".xlsx" Then
                            strFileName = strFolder & "\" & olAtt.FileName
                            'Save xl attachemnt to working folder
                            olAtt.SaveAsFile strFileName
                        End If
                    End If
                Next
            End If
        Next
        Set olns = Nothing
        Set olItem = Nothing
    End Sub
    

    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:48 AM
    Thursday, January 14, 2016 1:51 AM

All replies

  • Hello Ray,

    The Attachment class provides the SaveAsFile method which allows to save the attachment to the specified path.

    To process all incoming emails you can handle the NewMailEx event which is fired when one or more new items are received in the Inbox

    Anyway, I'd suggest starting from the Getting Started with VBA in Excel 2010 article in MSDN. 

    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:48 AM
    Wednesday, January 13, 2016 11:53 PM
  • >>>I have the import routine and the append query automated through MS Access but I would like to automate saving the file from the email anyone know how I can do this in Outlook?

    According to your description, Eugene Astafiev have gave your good suggestion, in addition you could refer to below code:

    Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
    
        Dim arr() As String
        Dim lngCnt As Long
        Dim olAtt As Attachment
        Dim strFolder As String
        Dim strFileName As String
        Dim strNewFolder
        Dim olns As Outlook.NameSpace
        Dim olItem As MailItem
        
        'Set working folder
        strFolder = "D:\Test"
        
        On Error Resume Next
        Set olns = Application.Session
        arr = Split(EntryIDCollection, ",")
        On Error GoTo 0
        
        For lngCnt = 0 To UBound(arr)
            Set olItem = olns.GetItemFromID(arr(lngCnt))
            'Check new item is a mail message
            If olItem.Class = olMail Then
                'Force code to count attachments
                DoEvents
                For Each olAtt In olItem.Attachments
                    'Check attachments have at least 5 characters before matching a ".xlsx" string
                    If Len(olAtt.FileName) >= 5 Then
                        If Right$(olAtt.FileName, 5) = ".xlsx" Then
                            strFileName = strFolder & "\" & olAtt.FileName
                            'Save xl attachemnt to working folder
                            olAtt.SaveAsFile strFileName
                        End If
                    End If
                Next
            End If
        Next
        Set olns = Nothing
        Set olItem = Nothing
    End Sub
    

    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:48 AM
    Thursday, January 14, 2016 1:51 AM
  • I have added the above code but other than moving the email from the inbox to a specified subfolder within outlook it does nothing else.  I'll keep reading and trying thanks for all the help
    Monday, January 25, 2016 6:28 PM