CODEWITHSUNDEEP
excelvbaoutlooksave
RogerT
RogerT

Reputation: 13

How do I save Excel attachments in new mail based on both time received and subject line?

I'm fairly new to coding. I would like to know how to save particular Excel attachments in Outlook inbox ("Morning Emails") using subject line keywords and the times received.

I receive five new emails each day, with Excel attachments to save to the same drive folder.

The time received can be either last night or early this morning.

The names of all five files and times received are different.

The inbox does not empty. I don't want to save what I saved yesterday or 2 weeks ago.

Sub SaveAttachments()

    Dim ol As Outlook.Application
    Dim ns As Outlook.Namespace
    Dim fol As Outlook.Folder
    Dim i As Object
    Dim mi As Outlook.MailItem
    Dim at As Outlook.Attachment

    Set ol = New Outlook.Application
    Set ns = ol.GetNamespace("MAPI")
    Set fol = ns.Folders(1).Folders("Morning Emails")

    For Each i In fol.Items

        If i.Class = olMail Then

            Set mi = i

            If mi.Attachments.Count > 0 Then
                For Each at In mi.Attachments
                    at.SaveAsFile "C:\Users\nader\OneDrive\Documents\" & _
                      at.Filename & Format(mi.ReceivedTime, " MM-DD-YYYY")
                Next at   
            End If

        End If

    Next i

End Sub

"Attempted operation failed; object could not be found" for line: Set fol = ns.Folders(1).Folders("Morning Emails"), even though I have created that sub-folder under my Outlook inbox.

Upvotes: 0

Views: 440

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

Set ol = New Outlook.Application

There is no need to create a new Outlook Application instance in the code. Use the Application property to get the host application instance in Outlook VBA macros.

I'd recommend handling the NewMailEx event of the Application class. 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.

The NewMailEx event fires when a new message arrives in the Inbox and before client rule processing occurs. You can use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item. 

Private Sub outApp_NewMailEx(ByVal EntryIDCollection As String)    
      Dim itm as Object
      Set itm = NS.GetItemFromID(EntryIDCollection)
      Debug.Print "mail received"
      If itm.Class = olMail Then
        Dim it as Outlook.MailItem
        Set it = itm       
        if it.Subject = "your subject" then 
          If it.Attachments.Count > 0 Then
            For Each at In mi.Attachments
                at.SaveAsFile "C:\Users\nader\OneDrive\Documents\" & _
                    at.Filename & Format(mi.ReceivedTime, " MM-DD-YYYY")
            Next at
          End If
        End If
      End If
End Sub

Upvotes: 1

Related Questions