Code For Excel And Outlook Blog

Open Any Email Attachment From Outlook

May 29, 2008 – 1:21 am by JP

---

I was inspired to write some VBA code that lets you open any attachment in its native application by a recent newsgroup post from Outlook MVP Sue Mosher. The truth is I was bored, and it was easy to code because I simply reused code I had just written for the Save Incoming Attachments post. Hopefully the value of a stock code library will now be apparent to you.

This code uses the Windows Script Host Object Model to let Windows choose what program to use to open an attachment. Actually, it uses whatever file associations that were created when the program was installed. For example, .doc files usually open in Microsoft Word (if you have it installed).

So instead of instantiating the object model for every possible attachment type (very tedious), this method opens any attachment (as long as Windows knows what program to use). I haven’t tested this with unknown attachments, so if anyone would like to try and let me know what happens (hopefully the file association dialog box appears), I’d be glad to hear it.

Sub OpenAttachmentInNativeApp()
' based on code posted by Sue Mosher
' http://tinyurl.com/684zg4

Dim myShell As Object
Dim MyItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Dim i As Long
Dim Att As String

On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
    Case "Explorer"
        Set MyItem = ActiveExplorer.Selection.Item(1)
    Case "Inspector"
        Set MyItem = ActiveInspector.CurrentItem
    Case Else
End Select
On Error GoTo 0

If MyItem Is Nothing Then
    GoTo ExitProc
End If

Set myAttachments = MyItem.Attachments

If myAttachments.Count > 0 Then
    For i = 1 To myAttachments.Count
        Att = myAttachments.Item(i).DisplayName
        ' delete just in case it exists from before
        On Error Resume Next
        Kill "C:\" & Att
        On Error GoTo 0

        myAttachments.Item(i).SaveAsFile "C:\" & Att
    Next i
End If

' Windows Script Host Object
Set myShell = CreateObject("WScript.Shell")
myShell.Run "C:\" & Att

ExitProc:
Set myAttachments = Nothing
Set MyItem = Nothing
Set myShell = Nothing
End Sub

And here’s a link to the newsgroup thread:

Open an attachment in a new window using its native application

Enjoy,
JP

 Print This Post  |   Email This Post  |   Subscribe to Posts Feed  |   Subscribe to Comments

Filed Under: Outlook, VBA, automation
Tags: attachments, native, Outlook, VBA, Windows Script Host