Back in 2013 when I returned to using Outlook as an email client (new job, prior job used Google Apps), I was sprucing up some old code. I have two problems with the code on that page; one I’m solving here and one I don’t know how to solve yet.
The first problem is when someone sends me two attachments. I want to open the first, but have no interest in the second. Most recently this problem manifests itself as an invoice and a packing list. I need the invoice, but I don’t need the packing list. Alt+3 (this macro is third on my QAT) opens the last attachment first, so I’m stuck opening the packing list, closing it, then opening the invoice. In practice, I open it the old fashioned way (Shft+Tab, Home, Ctrl+Shft+RightArrow, Menu, O). Go ahead and try it. You know you want to. The Menu key is the key between Alt and Ctrl on the right side of my keyboard. Even if I concentrate really hard on the first attachment, the code still opens them just like a programmed. I don’t have a solution for this.
The second problem is when someone sends me an attachment with no file extension or some bullshit file extension. I get a text file with a .success extension from a website telling me my upload worked. I’m not sure if they’re just being clever or if there is some other significance, but I do know that Windows, and more specifically WScript.Shell, doesn’t know how to open it. I had some code that checked for no extension and opened it in Notepad++, but recently changed it to handle any unknown file extension.
Public Sub DisplayAttachment(olAtt
As Attachment, sFile
As String, sPath
As String)
Dim oShell
As Object
Dim miNew
As MailItem
On Error GoTo ErrHandler
If olAtt.
Type = olEmbeddeditem
Then
Set miNew = Application.GetNamespace(
"MAPI").OpenSharedItem(sPath & sFile)
miNew.Display
Else
sFile = GetShortFileName(sPath & sFile)
Set oShell =
CreateObject(
"WScript.Shell")
oShell.Run sFile
End If
ErrExit:
Exit Sub
ErrHandler:
Select Case Err.Number
Case -2147023741
oShell.Run "C:PROGRA~1NOTEPA~1NOTEPA~1.EXE" & Space(1) & sFile
Case Else
MsgBox Err.Number & vbNewLine & Err.Description
End Select
Resume ErrExit
End Sub
Good ol’ error handling. If WScript.Shell can’t open the file, it throws error -2147023741, better known as Automation error. No application is associated with the specified file for this operation. When that happens, it opens the file in Notepad++. That may not always be the best choice, but usually is. Happy keyboarding.
