Consulting

Results 1 to 3 of 3

Thread: Filename %20 when adding Workbook from SharePoint in Email

  1. #1
    VBAX Regular
    Joined
    Feb 2021
    Posts
    21
    Location

    Filename %20 when adding Workbook from SharePoint in Email

    Hi everyone

    I have a code to send an email, that I need every week for my companies weekly accounts.

    I have a workbook where I prepare my accounting data, where I have a specific range consisting of the weekly overview that I put into a new workbook that is saved in SharePoint.
    This new workbook is then send out to multiple recipients.

    The code works - however, I have an issue with the name of the attached file..

    SharePoint handles " " (spaces) as %20 in the link addresses (or what do we call the website address?), file names and more. But this is no issue when I am saving the new workbook into SharePoint.
    With my code the new Workbook name is saved as e.g. "SalgsDB - Week 20.xlsx" - which is what I want.

    The problem arrives when I need to send til file as an attachment. Excel converts the name into ""SalgsDB%20-%20Week%2020.xlsx" which, to be honest, is annoying me - and my co-workers have asked if we could go back to the old file names (that is, with just normal spaces) as before we started using SharePoint.

    I therefore hope, that someone have encountered this before AND have a solution of how to change the code so that the name of the attachment is the same as the saved file.

    My code:
    Sub SendWeeklies_NewCode()
    
    Dim OlApp As Object
    Dim NewMail As Object
    Dim NewWb As Workbook
    Dim NameOfFile As String
    Dim Recipient As String
    Dim CCRecipient As String
    Dim FilePath As String
    
    
    NameOfFile = Range("WeekliesName").Value
    Recipient = Range("Reciever").Value
    CCRecipient = Range("CCReciever").Value
    FilePath = Range("WAFilePath").Value
    
    
    
    
    Range("Weeklies").Copy
    Set NewWb = Workbooks.Add
    NewWb.Activate
    Range("B2").PasteSpecial xlPasteAll
    Range("B2").PasteSpecial xlPasteColumnWidths
    Range("B2").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FilePath & NameOfFile
    Application.DisplayAlerts = True
    
    
    
    
    Set OlApp = CreateObject("Outlook.application")
    Set NewMail = OlApp.createitem(0)
    
    
    With NewMail
        .To = Recipient
        .cc = CCRecipient
        .Subject = NameOfFile
        .attachments.Add (FilePath & NameOfFile & ".xlsx")
        .Body = "Hermed denne uges SalgsDB."
        .send
    End With
    
    
    Set NewMail = Nothing
    Set OlApp = Nothing
    
    
    NewWb.Close savechanges:=False
    
    
    End Sub
    Note: We a using the SharePoint online, and are therefore not using the synchronising function to our computers.

    Thank you in advance :-)

    Best regards,
    Rasmus
    Last edited by RAECH; 05-28-2021 at 01:30 AM.

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Please try the code below. In addition to saving to Sharepoint, the file is temporarily saved in the TEMP folder. After shipment, the temporary file is deleted.
    Sub SendWeeklies_NewCode_1()
    
        Dim OlApp       As Object
        Dim NewMail     As Object
        Dim NewWb       As Workbook
        Dim NameOfFile  As String
        Dim Recipient   As String
        Dim CCRecipient As String
        Dim FilePath    As String
    
    
    
    
        NameOfFile = Range("WeekliesName").Value
        If LCase(Right(NameOfFile, 5)) <> ".xlsx" Then
            NameOfFile = NameOfFile & ".xlsx"
        End If
        Recipient = Range("Reciever").Value
        CCRecipient = Range("CCReciever").Value
        FilePath = Range("WAFilePath").Value
    
    
    
    
        Range("Weeklies").Copy
    
    
        Set NewWb = Workbooks.Add(Template:=xlWBATWorksheet)
    
    
        With NewWb.Worksheets(1)
            .Range("B2").PasteSpecial xlPasteAll
            .Range("B2").PasteSpecial xlPasteColumnWidths
            .Range("B2").PasteSpecial xlPasteValues
    
    
            Application.CutCopyMode = False
            Application.DisplayAlerts = False
    
    
            'save into SharePoint...
            .Parent.SaveAs Filename:=FilePath & NameOfFile, FileFormat:=xlOpenXMLWorkbook
    
    
            '... and save in TEMP folder (locally)
            .Parent.SaveAs Filename:=Environ("TEMP") & Application.PathSeparator & NameOfFile, FileFormat:=xlOpenXMLWorkbook
    
    
            Application.DisplayAlerts = True
        End With
    
    
    
    
    
    
        Set OlApp = CreateObject("Outlook.Application")
        Set NewMail = OlApp.CreateItem(0)
    
    
    
    
        With NewMail
            .To = Recipient
            .CC = CCRecipient
            .Subject = Left(NameOfFile, Len(NameOfFile) - 5)
            'attach from TEMP
            .Attachments.Add NewWb.FullName    '(Environ("TEMP") & Application.PathSeparator & NameOfFile)
            .Body = "Hermed denne uges SalgsDB."
            .Display    'Send (After successful tests, replace with Send)
        End With
    
    
    
    
        Set NewMail = Nothing
        Set OlApp = Nothing
    
    
    
    
        NewWb.Close SaveChanges:=False
    
    
        'remove file from TEMP
        On Error Resume Next
        Kill Environ("TEMP") & Application.PathSeparator & NameOfFile
        On Error GoTo 0
    
    
    End Sub
    Artik

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    SalesDB_Week_20.xlsx
    NameOfFile = Range("WeekliesName").Value
    NameOfFile = Replace(NameOfFile, " - ", "_")
    NameOfFile= Replace(NameOfFile,  " ", "_")
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •