%20 issue on pdf attachment

[godreg]

Hi!

I'm pretty new with VBA and trying to take some initiative with fixing some of the broken macros. Currently the file I'm using correctly converts an excel tab into a pdf document and saves it into the correct folder, with the file name having some spaces. I'm also able to attach the pdf file to the email but the file name now has %20 between spaces for e.g. Daily Summary 20210409 is now Daily%20Summary%2020210409. I've unsuccessfully tried using Application.WorksheetFunction.Substitute( X, " ", "%20"). Any help is much appreciated:

>>> use code - tags <<<

    Dim OutApp As Object
    Dim OutMail As Object
    Dim rng As Range
    Dim StrBody1, StrBody2 As String
    Dim LastRow As Long
    Dim i As Integer

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
   
    'Set filepath for attachments
    Let Path = Application.ActiveWorkbook.Path
    Let Report_Date = Format(Sheets("Report").Range("Report_Date"), "YYYYMMDD")
    Let Path_Perf = Path & "\Daily Summary " & (Report_Date) & ".pdf"
    .
    .
    .
    .  
    'Compose email
    On Error Resume Next
    With OutMail
        .Display
        .To = Sheets("Email Report").Range("RecipientsTo")
        .CC = Sheets("Email Report").Range("RecipientsCC")
        .BCC = ""
        .Subject = Sheets("Email Report").Range("Email_Subject")
        .HTMLBody = StrBody1 & _
            RangetoHTML(rng) & _
            StrBody2 & "<br>" & _
            .HTMLBody
        .Attachments.Add Path_Perf
        '.Send
    End With
    On Error GoTo 0
   
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[Kenneth Hobson]

I can not duplicate that problem.

Your 2nd .HTMLBody would cause an error if you Debug > Compile.

Substitute function replaces " " with "%20". Was that the intent? Normally, Replace() is used for such. You should not need it though.

FWIW, Let is not needed.

 

[godreg]

Thanks for the response Kenneth. I'm not sure why this keeps happening for my team, I've searched multiple boards and haven't seen this issue. I was looking to remove the "%20" from the file name and replacing it with " ". Currently we're removing the attachment and adding it back manually every time

You're also right the Let isn't needed, I just had it in so I keep track of my thought process. I did have it like this prior:

.Attachments.Add Application.ActiveWorkbook.Path & "\Daily Summary " & (Format(Sheets("Report").Range("Report_Date"), "YYYYMMDD")) & ".pdf"

 

[Kenneth Hobson]

My guess is that you have some other code that we don't see. Maybe if we saw what ActiveWorkbool.Path resolved to, that would make a difference but I am doubtful.

I guess you can try Replace(). e.g.

.Attachments.Add Replace(Path_Perf, "%20", " ")

 

[godreg]

Its all saved under our OneDrive, the ActiveWorkbook.Path leads to C:\O365Sync\Company\General - Documents\Reporting\Daily\Client\2021\04 2021\20210407\

I also just tried saving the file under My Documents and using the direct path with .Attachments.Add , and it worked!
So I guess I should try and create a dynamic path and use that instead of ActiveWorkbook.Path?

 

[Kenneth Hobson]

Both of those are paths to your local folders. I can show you how to put the file in a subfolder of the user's MyDocuments or Desktop if you like. If a shared network, a virtual drive is usually used for such things.

ThisWorkbook.Path is probably better than ActiveWorkbook.Path to avoid any mixup.

 

[godreg]

Hi Kenneth. Sorry took longer to reply. Finally fixed the issue! I've completely removed ActiveWorkbook.Path from the macro and entered the path into a Cell (named "Attachments") and used the following

 Let Path_Perf = Sheets("Email Report").Range("Attachments").Text
.
.
.Attachments.Add Path_Perf

The attachment in outlook no longer has %20 in the file name. Thank again for your help!