Email Attachment File Name

[O177812]

I have written the following:

ActiveWorkbook.SaveAs Filename:= _
"https://gc.sharepoint.com/sites/Adm...ustomer Pricing/Campari/1_Open Order Reports/" & "Campari Dashboard " & Format(Now, "m.dd.yy") & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

The file is saved correctly in the folder. However, when it is attached to an email using the below:

.Attachments.Add (ActiveWorkbook.FullName)

....it contains "%20" where the spaces should be in the filename.

Help is greatly appreciated!

 

[Scott Huish]

%20 is hex for the ASCII character for a space 32

 

[O177812]

%20 is hex for the ASCII character for a space 32

I don't understand. Can you elaborate?

 

[Scott Huish]

Here's an explanation of ASCII What Is ASCII & What Is ASCII Used For? (+ PDF Table) [2024]

I would suspect that Excel is using the mailto: Internet Protocol
i.e. mailto:john.smith@test.com

Web address can't contain spaces and other characters so these are converted to their ASCII equivalent but it does it in Hexadecimal numbers (Base 16) rather than decimal numbers (Base 10).
The ASCII code for a space is 32, which in hexadecimal is 20. The % sign indicates that it is a hexadecimal number that is what is coming next in the web address


Personally, I try not to use spaces in filenames that I plan on sending via the internet.

 

[Trevor G]

There is a couple of options I think and one might work for you. First the simple explanation. As you are using SharePoint either direct in the cloud or on a link basis possibly by a folder on your PC then your files are cloud based and that is why you get the symbols.

Solution one.
Create a folder on your PC that is not linked direct to SharePoint and place the workbook in that folder and then point your code to attach the workbook to that folder the attachment including the spaces will be attached.

Solution two.
You could look to use the Replace function when attaching the file something along the example below:

.Attachments.Add(ThisWorkbook.Path & "\" & "the name of the new file.xlsx") = Replace("the name of the new file.xlsx", "%20", " ")

This example would give you two attachments and with a little tweak you will come up with a solution that works for you.