vbaemailoutlookemail-attachments

Automatically save attached Excel file as I receive an email


I would like, as I receive an email from a certain person the attachment of this email is automatically saved in a folder "TEST" and the email is marked as read and then filed.

Here is what I found online. I have no error message but no record either.

Sub extrait_PJ_vers_rep(strID As Outlook.MailItem)

    Dim olNS As Outlook.namespace
    Dim MyMail As Outlook.MailItem
    Dim expediteur

    Set olNS = Application.GetNamespace("MAPI")
    Set MyMail = olNS.GetItemFromID(strID.EntryID)
    
    If MyMail.Attachments.Count > 0 Then
    
        expediteur = MyMail.SenderEmailAddress
        Repertoire = "c:\TEST" & "\"
    
        If Repertoire <> "" Then
            If "" = Dir(Repertoire, vbDirectory) Then
                MkDir Repertoire
            End If
        End If
    
        Dim PJ, typeatt
    
        For Each PJ In MyMail.Attachments
    
            typeatt = Isembedded(strID, PJ.Index)
            If typeatt = "" Then
    
                If "" <> Dir(Repertoire & PJ.FileName, vbNormal) Then
    
                    MsgBox Repertoire & PJ.FileName & " Done before"
    
                    If "" = Dir(Repertoire & "old", vbDirectory) Then
                        MkDir Repertoire & "old"
                    End If
    
                    FileCopy Repertoire & PJ.FileName, Repertoire & "old\" & PJ.FileName
                End If
                    PJ.SaveAsFile Repertoire & PJ.FileName
             End If
        Next PJ
    
        MyMail.UnRead = False
        MyMail.Save
    
        Dim myDestFolder As Outlook.MAPIFolder
        Set myDestFolder = MyMail.Parent.Folders("test")
        MyMail.Move myDestFolder
    
    End If
    
    Set MyMail = Nothing
    Set olNS = Nothing

End Sub

Solution

  • The code does exactly what you need, you just need to hook up the NewMailEx event of the Outlook Application class which is fired when a new message arrives in the Inbox and before client rule processing occurs. This event fires once for every received item that is processed by Microsoft Outlook. The item can be one of several different item types, for example, MailItem, MeetingItem, or SharingItem. The EntryIDsCollection string contains the Entry ID that corresponds to that item. Use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item, like you do in the code.

    Go to the VBA environment in Outlook, select the Application on the lef-hand side dropdown list like shown on the screenshot:

    enter image description here

    Then you can add the NewMailEx event handler:

    enter image description here

    Viola! The event handler will be added, you just need to paste your code here.