迁移至SharePoint后，VBA邮件附件文件名如何保留空格而非%20？

当使用SharePoint URL作为Outlook邮件附件路径时，空格会被自动编码为%20，导致附件名称显示异常。可以通过URL解码还原空格，并利用Attachments.Add的可选参数指定显示名称来解决这个问题。

修改后的完整代码 #

' 新增URL解码函数
Function URLDecode(ByVal strEncoded As String) As String
    Dim objHTML As Object
    Set objHTML = CreateObject("htmlfile")
    URLDecode = objHTML.parentWindow.unescape(strEncoded)
    Set objHTML = Nothing
End Function

Sub SendEmailsWithSharePointAttachments()
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim LastRow As Long
    Dim i As Integer
    Dim attachPath As String
    Dim displayName As String

    LastRow = ThisWorkbook.Worksheets("Reports (2)").Cells(Rows.Count, "A").End(xlUp).Row ' 修改工作表名称
    
    ' 只初始化一次Outlook应用，提升效率
    Set OutLookApp = CreateObject("Outlook.application")

    For i = 2 To LastRow
        Set OutLookMailItem = OutLookApp.CreateItem(0)

        With OutLookMailItem
            .SentOnBehalfOfName = "x@x.co.uk"
            .To = "x@x.co.uk"
            '.cc = "x@x.co.uk"
            .Subject = ThisWorkbook.Worksheets("Reports (2)").Cells(i, 10).Value
            .Body = ThisWorkbook.Worksheets("Reports (2)").Cells(i, 11).Value
            
            ' 处理第一个附件
            attachPath = ThisWorkbook.Worksheets("Reports (2)").Cells(i, 5).Value
            displayName = URLDecode(Split(attachPath, "/")(UBound(Split(attachPath, "/"))))
            .Attachments.Add attachPath, , , displayName
            
            ' 处理第二个附件
            attachPath = ThisWorkbook.Worksheets("Reports (2)").Cells(i, 6).Value
            displayName = URLDecode(Split(attachPath, "/")(UBound(Split(attachPath, "/"))))
            .Attachments.Add attachPath, , , displayName
            
            .Display
            'SendKeys "^{ENTER}"
        End With
    Next i
    
    ' 释放对象
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing
    
    'Call yes_no_msg
End Sub

关键修改点说明 #

1. 新增URL解码函数：利用htmlfile对象的unescape方法，将编码后的%20还原为空格。
2. 提取并解码文件名：通过Split函数从SharePoint URL中分离出文件名，再解码得到带空格的原始名称。
3. 指定附件显示名称：调用Attachments.Add时，第四个参数DisplayName设置为解码后的文件名，确保收件人看到的是带空格的正常名称。
4. 优化Outlook初始化：将Outlook应用的初始化移到循环外，避免重复创建对象，提升代码效率。
5. 明确工作表引用：修改Cells为带工作表对象的引用，避免因当前工作表切换导致的错误。

内容的提问来源于stack exchange，提问作者Classre