Saving attachments in Outlook

People write to me sometimes and ask how they can do things with email attachments in Outlook. They want to print them, open them, copy or move them. Dammit, they want to use them! Unfortunately, there's no way to do so without saving the file first.

In other words, you can't print or open a workbook attached to an email without first saving it. Here are some methods to do so.

The Attachments Collection

This collection exists for the following Outlook objects:

AppointmentItem
ContactItem
DocumentItem
MailItem
MeetingItem
PostItem
ReportItem
TaskItem
TaskRequestAcceptItem
TaskRequestDeclineItem
TaskRequestItem
TaskRequestUpdateItem

(from Attachments Collection)

Here's a procedure (along with a series of helper functions) that returns the Attachments collection from any of these items:

Return Attachments Collection for any supported Object

Function GetAttachmentsColl(itm As Object) As Outlook.Attachments
  Set GetAttachmentsColl = itm.Attachments
End Function

Function GetCurrentItem() As Object
' returns reference to current item, either the one
' selected (Explorer), or the one currently open (Inspector)

  Select Case True
  Case IsExplorer(Application.ActiveWindow)
      Set GetCurrentItem = ActiveExplorer.Selection.Item(1)
  Case IsInspector(Application.ActiveWindow)
      Set GetCurrentItem = ActiveInspector.CurrentItem
  End Select

End Function

Function IsExplorer(itm As Object) As Boolean
  IsExplorer = (TypeName(itm) = "Explorer")
End Function

Function IsInspector(itm As Object) As Boolean
  IsInspector = (TypeName(itm) = "Inspector")
End Function

The GetAttachmentsColl function takes an item and returns its Attachments Collection. But how do we get that item? What I've done is write a function that returns the currently selected or open item (GetCurrentItem), but you could pass any object to the function. For example, an item you created using the CreateItem Method, or incoming items you're tracking using the ItemAdd or NewMail Events. But it needs to be one of the above items only.

Several of the items cannot be user-created, which is why I declared certain input and return objects As Object. For example, if I declared the GetAttachmentsColl function like this

Function GetAttachmentsColl(itm As olItemType) As Outlook.Attachments

(instead of As Object) then I could only check items from that class, and not all of them support an Attachments Collection.

What we should probably do is check the item class to make sure it's one of the above items, regardless of how the function is called.

Function GetAttachmentsColl(itm As Object) As Outlook.Attachments
  Select Case itm.Class
  Case olAppointment, olContact, olDocument, olMail, _
       olMeetingRequest, olPost, olReport, olTask, olTaskRequestAccept, _
       olTaskRequestDecline, olTaskRequest, olTaskRequestUpdate
    Set GetAttachmentsColl = itm.Attachments
  End Select
End Function

Sample usage

Sub TestAttachments()
Dim attachs As Outlook.Attachments
Dim attach As Outlook.Attachment

Set attachs = GetAttachmentsColl(GetCurrentItem)

For Each attach In attachs
  Debug.Print attach.fileName
Next attach

End Sub

Saving Attachments

Now on to the point of this post, which was saving (and eventually opening) attachments.

Outlook currently provides one method for saving attachments: the SaveAsFile Method. So far this has remained unchanged in Outlook 2003 through 2010.

The SaveAsFile Method belongs to the Attachment Object, so once we have our Attachments collection (courtesy of GetAttachmentsColl) we'll iterate through it and call the SaveAsFile method on each Attachment Object. Note that you must pass the full path and filename to the SaveAsFile method.

Sub TestAttachments()
Dim attachs As Outlook.Attachments
Dim attach As Outlook.Attachment

Set attachs = GetAttachmentsColl(GetCurrentItem)

For Each attach In attachs
  Debug.Print attach.FileName
  attach.SaveAsFile "C:\" & attach.FileName
Next attach

End Sub

Now what?