Processing multiple emails

In Highlight And Move Multiple Emails, I demonstrated a technique for processing several emails at once and moving them to another folder. Here is another example that shows how you can save attachments from several selected emails, then delete them.

Sub SaveEmailAttachments()

Dim Msg As Outlook.MailItem
Dim MsgColl As Object
Dim MsgAttach As Outlook.Attachments
Dim i As Long
Dim FileN As String
Dim lAttach As Long

On Error Resume Next
Set MsgColl = ActiveExplorer.Selection
On Error GoTo 0

If Not MsgColl Is Nothing Then
  For i = 1 To MsgColl.Count

    ' loop through selected items and save all attachments from each of them
    Set Msg = MsgColl.Item(i)
    Set MsgAttach = Msg.Attachments

    For lAttach = 1 To MsgAttach.Count
      FileN = MsgAttach.Item(lAttach).DisplayName
      MsgAttach.Item(lAttach).SaveAsFile "C:\My Folder\" & FileN
    Next lAttach

  Next i

    If MsgBox("Would you like to delete the selected emails now?", _
     vbInformation + vbYesNo) = vbYes Then
        For i = 1 To MsgColl.Count
          Set Msg = MsgColl.Item(i)
          Msg.Delete
        Next i
    End If
Else
  GoTo ExitProc
End If

ExitProc:
Set MsgAttach = Nothing
Set Msg = Nothing
Set MsgColl = Nothing
End Sub

Other things you can do involve incorporating other email properties, such as received time. This will save the filename with date and time pre-pended to the display name:

MsgAttach.Item(lAttach).SaveAsFile "C:\My Folder\" & Format(Msg.ReceivedTime, "mmddyyyy hhmm") & " " & FileN

And this will save the filename as just the received time, with the proper file extension:

MsgAttach.Item(lAttach).SaveAsFile "C:\My Folder\" & Format(Msg.ReceivedTime, "mmddyyyy hhmm") & Right$(FileN, 4)

You can also use the Subject property:

MsgAttach.Item(lAttach).SaveAsFile "C:\My Folder\" & Msg.Subject & " " & Format(Msg.ReceivedTime, "mmddyyyy hhmm") & Right$(FileN, 4)

This will set the attachment filename to Subject and Received time.

Enjoy,
JP

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 1 Comment(s) on Processing multiple emails:

  1. JP, thanks so much for this, it worked perfectly!
    Saved me so much time.

    Keep up the great work.
    Tim

This article is closed to any future comments.
learn excel dashboards