[SOLVED] Attaching files from SharePoint uses %20 for spaces in filenames

Attaching files from SharePoint uses %20 for spaces in filenames

We moved files to SharePoint and are updating VBA code to reflect the new locations.

One of the VBA code emails files. The location is now a URL rather than a filepath so it is using %20 for spaces in filenames.

How do I keep spaces in the filenames?

Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim LastRow As Long
Dim MailDest As String
Dim subj As String

LastRow = ThisWorkbook.Worksheets("Reports (2)").Cells(Rows.Count, "A").End(xlUp).Row 'change worksheet

For i = 2 To LastRow

    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
    Set Attach = OutLookMailItem.Attachments

    With OutLookMailItem
    
        .SentOnBehalfOfName = "x@x.co.uk"
        .To = "x@x.co.uk"
        '.cc = "x@x.co.uk"
        .Subject = Cells(i, 10).Value
        .body = Cells(i, 11).Value
        Attach.Add Cells(i, 5).Value
        Attach.Add Cells(i, 6).Value
        
       .display
       'SendKeys "^{ENTER}"
      
    End With
'SendKeys "^{ENTER}"
Next
'Call yes_no_msg
End Sub

Solution

You can edit the attachment DisplayName to remove %20 and replace with spaces:

Set OutLookApp = CreateObject("Outlook.application")
With OutLookApp.CreateItem(0)
    .To = "x@x.co.uk"
    .Subject = "subject"
    .Body = "body"
    With .Attachments.Add("https://contoso.sharepoint.com/sites/XXX/TestLibrary/Code128 Barcodes.xlsm")
        .DisplayName = Replace(.DisplayName, "%20", " ") '<<<<<
    End With
    .Display
End With