Saving a file attachment in an Outlook mail item with the VBA method Attachment.SaveAsFile() call produces the expected result (file saved with same filename on the filesystem), even for file names with non-ASCII characters.
However, VBA apparently stores the file name in a 16-bit composite format String where accented letters are stored as a (letter, accent) pair. I can't find a way to output the string inside the message body with accented letters showing up as one glyph ("é") instead of two ("e´").
Concretely, the attachment is properly saved under the correct file name on disk when using the following code:
' Save the Outlook attachment
oAttachment.SaveAsFile (sTempFileLocation)
This results in a file being written to the folder specified in sTempFileLocation and the file name complies with the way it appears in the Outlook message (accents, non-ASCII characters etc).
However, when retrieving and manipulating the file name, it appears that a 16-bit composite internal representation of special characters is used. This means that the file name "à présent.txt" is displayed as "a` pre´sent.txt" (accented characters are represented with the character + the accent in 2 consecutive bytes).
For instance:
sAttachmentName = fso.getfilename(sTempFileLocation)
Debug.Print ("Attachment name = [" & sAttachmentName & "]")
will result in:
Attachment name = [a` pre´sent.txt]
There is little information available on this matter, all I found so far was this MSDN link describing the MultiByteToWideChar() function. From there it appears that the 16-bit internal VBA rendering happens implcitly and is even computer dependent (depending on code page and locale in use).
Here follows a self-contained minimalistic example that tries to save the email attachments of the first selected message to your My Documents folder unless it already exists:
Sub SaveMessageAttachments()
Dim objApp As Outlook.Application
Dim oSelection As Outlook.Selection
Dim aMail As Outlook.MailItem
Dim fso As Object
On Error Resume Next
' Instantiate an Outlook Application object.
Set objApp = CreateObject("Outlook.Application")
' Get the collection of selected objects.
Set oSelection = objApp.ActiveExplorer.Selection
If oSelection Is Nothing Then
Exit Sub
End If
' Select the 1st mail item in the current selection
Set aMail = oSelection.item(1)
Dim sAttachmentFolder As String
' Get the path to your "My Documents" folder
sAttachmentFolder = CreateObject("WScript.Shell").SpecialFolders(16)
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oAttachments As Outlook.Attachments
Dim lItemAttachmentCount As Long
Set oAttachments = aMail.Attachments
lItemAttachmentCount = oAttachments.Count
If (lItemAttachmentCount > 0) Then
Dim lAttachmentIndex As Long
For lAttachmentIndex = 1 To lItemAttachmentCount
Dim oAttachment As Outlook.attachment
Set oAttachment = oAttachments.item(lAttachmentIndex)
Dim sFileName As String
sFileName = oAttachment.FileName
If LenB(sFileName) > 0 Then
Dim sFilePath As String
sFilePath = sAttachmentFolder & "\" & sFileName
If fso.fileexists(sFilePath) Then
MsgBox "Cannot save attachment " & lAttachmentIndex & vbCr _
& "File already exists: " & vbCr _
& sFilePath, vbExclamation + vbOKOnly
Else
If MsgBox("Saving atachment " & lAttachmentIndex & "?" & vbCr _
& "Save location: " & vbCr & sFilePath, _
vbQuestion + vbOKCancel) = vbOK Then
' Save the attachment to the temporary folder
oAttachment.SaveAsFile (sFilePath)
Dim sAttachmentName As String
sAttachmentName = fso.getfilename(sFilePath)
Dim lAttachmentLength As Long
lAttachmentLength = fso.getfile(sFilePath).size
Dim sURL As String
sURL = "file://" & Replace(sFilePath, "\", "/")
MsgBox "Attachment " & lAttachmentIndex _
& " saved as: " & sAttachmentName & vbCr _
& "Size: " & lAttachmentLength & vbCr _
& "URL = " & sURL, _
vbInformation + vbOKOnly
End If
End If
End If
Next lAttachmentIndex
End If
End Sub
As you will see, the SaveMessageAttachments() subroutine correctly saves the file to the filesystem, with the proper file name. However, Outlook dialogs (as well as when trying to write the attachment file name or URL to the message body in VBA) will always render the file names having accents differently. Please give it a try with an Outlook message having an attachment named e.g. "à présent.txt").
What is strange, however, is that if I try to paste sURL in the message body, although the URL is incorrectly written (2 character decomposition of accented letters) Outlook seems to find and open the file.
How can I transform this accented string (sAttachmentName) with VBA in order to correctly paste it ("à présent.txt" instead of "a` pre´sent.txt") into the message body?
