Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-01-2022, 01:18 AM
JamesMWood JamesMWood is offline Attach file VBA with SharePoint file Windows 10 Attach file VBA with SharePoint file Office 2021
Novice
Attach file VBA with SharePoint file
 
Join Date: May 2022
Posts: 25
JamesMWood is on a distinguished road
Question Attach file VBA with SharePoint file

Hiya



I have a VBA that automatically attaches files to a draft email, and it works fine. But when it comes to SharePoint files, it adds %20 into the file names.

Whereas if I attach the SharePoint file manually by going to Attach File > Browse this PC > and I put in the whole URL path to the file, it asks if I want to attach a copy, and I click that, and it adds it without %20 in the filename.

So I'm not sure why it adds %20 when it's via VBA? I've tried removing https: from the string for the path, but it just gets hung up unless I'm using ActiveX, which I'm trying to avoid now since it's gone defunct, so that doesn't work.

Let me know your thoughts!!

Thanks all
James
Reply With Quote
  #2  
Old 12-01-2022, 09:45 PM
gmayor's Avatar
gmayor gmayor is offline Attach file VBA with SharePoint file Windows 10 Attach file VBA with SharePoint file Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Have you thought of saving the SharePoint file locally, renaming it if necessary to replace %20 with a space, attaching it, then deleting the local file?
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 12-02-2022, 01:04 AM
JamesMWood JamesMWood is offline Attach file VBA with SharePoint file Windows 10 Attach file VBA with SharePoint file Office 2021
Novice
Attach file VBA with SharePoint file
 
Join Date: May 2022
Posts: 25
JamesMWood is on a distinguished road
Unhappy

Quote:
Originally Posted by gmayor View Post
Have you thought of saving the SharePoint file locally, renaming it if necessary to replace %20 with a space, attaching it, then deleting the local file?
Unfortunately yes, and even tried incorporating the Sync feature, but the issue is that there are files in many various areas on SharePoint, which get updated monthly by different teams (things like fact sheets, etc), so having local copies would mean having to download them all every month, and there's a lot of files... whereas currently, I just have notifications set up on SharePoint to notify me when changes have been made, and then I only need to update the strings (which the VBA grabs from a txt file), which is a lot quicker.

So I'm at a loss really! I might have to settle for the %20 ! I'm just not sure why if I do it manually through Outlook and 'attach as copy' it doesn't include the %20... oh well!
Reply With Quote
  #4  
Old 12-02-2022, 01:16 AM
gmayor's Avatar
gmayor gmayor is offline Attach file VBA with SharePoint file Windows 10 Attach file VBA with SharePoint file Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Given that you are apparently already using VBA to attach the files to the message, it does not seem too onerous to add a couple of lines of code to save the file to a temporary location, attach that file instead, then delete the temporary file. What am I missing?
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 12-02-2022, 05:10 AM
JamesMWood JamesMWood is offline Attach file VBA with SharePoint file Windows 10 Attach file VBA with SharePoint file Office 2021
Novice
Attach file VBA with SharePoint file
 
Join Date: May 2022
Posts: 25
JamesMWood is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
Given that you are apparently already using VBA to attach the files to the message, it does not seem too onerous to add a couple of lines of code to save the file to a temporary location, attach that file instead, then delete the temporary file. What am I missing?
I'll give it a try and see if that doesn't include the %20 when it downloads them! Thanks
Reply With Quote
  #6  
Old 12-02-2022, 10:11 PM
gmayor's Avatar
gmayor gmayor is offline Attach file VBA with SharePoint file Windows 10 Attach file VBA with SharePoint file Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

If it does, add an extra line to rename the file, replacing %20 with a space, before attaching it.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #7  
Old 12-08-2022, 05:16 AM
JamesMWood JamesMWood is offline Attach file VBA with SharePoint file Windows 10 Attach file VBA with SharePoint file Office 2021
Novice
Attach file VBA with SharePoint file
 
Join Date: May 2022
Posts: 25
JamesMWood is on a distinguished road
Unhappy

Hiya

I've been struggling to come up with a code that works for downloading the file temporarily (with the file path called from a string), renaming it to remove %20 and then attaching it to the email, then deleting the temp file... This is a bit of a side-gig for me so I'm not a pro like you by any means.

What you're suggesting sounds perfect, I'm just not sure how to achieve it sorry. If you have any time to suggest some code, I'd really appreciate it. No worries if not!

Thanks
James
Reply With Quote
  #8  
Old 12-08-2022, 07:28 AM
gmayor's Avatar
gmayor gmayor is offline Attach file VBA with SharePoint file Windows 10 Attach file VBA with SharePoint file Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Open the document in Word then run the following
Code:
Sub Rename_and_Send_As_Attachment()
'Graham Mayor - https://www.gmayor.com - Last updated - 08 Dec 2022
'Send the document as an attachment _
  in an Outlook Email message
'Requires the code from - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to either retrieve an open instance of Outlook or open Outlook if it is closed.
Dim bStarted As Boolean
Dim OlApp As Object
Dim oItem As Object
Dim oDoc As Document
Dim strDocName As String
Dim strName As String
Dim strPath As String
    Set oDoc = ActiveDocument
    On Error GoTo Err_Handler:
    
    strPath = Environ("TEMP") & "\"
    'Replace %20 in the filename with a space
    strName = Replace(oDoc.Name, "%20", " ")
    strDocName = strPath & strName
    oDoc.SaveAs2 strDocName
    
    'Now close the document without saving as we have finished with it
    oDoc.Close 0
    'Get Outlook if it's running
    Set OlApp = OutlookApp()
    On Error GoTo 0
    'Create a new mailitem
    Set oItem = OlApp.CreateItem(0)

    With oItem
        .Subject = strName
        .attachments.Add strDocName
        .Display
    End With

lbl_Exit:
    Set oItem = Nothing
    Set OlApp = Nothing
    'Delete the temporary file
    Kill strDocName
    Exit Sub
Err_Handler:
    Err.Clear
    GoTo lbl_Exit
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #9  
Old 12-08-2022, 07:35 AM
JamesMWood JamesMWood is offline Attach file VBA with SharePoint file Windows 10 Attach file VBA with SharePoint file Office 2021
Novice
Attach file VBA with SharePoint file
 
Join Date: May 2022
Posts: 25
JamesMWood is on a distinguished road
Default

Wow thanks so much, honestly appreciate it very much. I'll give this a whirl and let you know how I get on! Thanks again
Reply With Quote
Reply

Tags
attachment, sharepoint, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Attach file VBA with SharePoint file Attach .xlsm file to Outlook email from whatever folder it's been saved in kiwimtnbkr Excel Programming 1 04-02-2022 11:32 PM
Cannot find file to attach in email megabyte Outlook 0 09-23-2020 05:35 AM
Button with VBA to attach a file in a protected document wolfgrrl Word VBA 6 09-20-2019 07:38 AM
Attach file VBA with SharePoint file Having Touble Finding Word File When Trying to Attach to Email freetibet213 Word 3 12-28-2011 05:26 AM
Attach a Excel file with Power point gmalpani Office 0 11-27-2011 03:11 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:46 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft