So I have written a spreadsheet that basically moves to each person after they have completed their section, it attaches a link to the SharePoint site and sends an email to the next person in the chain with that link that opens the actual file on SharePoint.
The file was written in 32 Bit and worked fine, but now I have been upgraded to 64 Bit the link opens up as read only and the spaces in the filename have been replaced by %20
Can anyone help with this please??
Here is the Code
Sub YES_BUTTON_PROJECT_LEADER()
'
' YES_BUTTON_PROJECT_LEADER Macro
'
'
ActiveSheet.Shapes("YES").Select
ActiveSheet.Shapes("YES").Fill.ForeColor.RGB = RGB(0, 255, 0) ' fill: dark green color
ActiveSheet.Shapes("YES").Line.BackColor.RGB = RGB(198, 217, 241) ' border: light blue color
ActiveSheet.Shapes("YES").TextFrame.Characters.Font.Color = RGB(0, 0, 0) ' text: white color
Range("A1").Formula = 14.28571 'fills cell with button value
' nonactive
ActiveSheet.Shapes("no").Select
ActiveSheet.Shapes("no").Fill.ForeColor.RGB = RGB(255, 0, 0) ' fill: light blue color
ActiveSheet.Shapes("no").Line.BackColor.RGB = RGB(198, 217, 241) ' border: light blue color
ActiveSheet.Shapes("no").TextFrame.Characters.Font.Color = RGB(0, 0, 0) ' text: dark blue color
'ActiveWindow.SelectedSheets.Visible = True
Range("A31").Select
Application.Goto Reference:="R1C1"
ActiveWorkbook.SaveAs Filename:= _
"https://gbconnect.sharepoint.com/sites/BUKGBGATEPROCESS/MDF/MAIDSTONE COMPLETED MDF FORMS 2021/" & ActiveWorkbook.Name
ActiveWorkbook.AutoSaveOn = True
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
Set OutApp = CreateObject("Outlook.Application")
mbody = "Hello" & vbNewLine & _
"We have a new MDF Brief In, The Project Leaders Section is complete" & vbNewLine & _
"NPD is first in the flow so please complete your section" & vbNewLine & _
" " & vbNewLine & _
""
sbody = "Many Thanks" & vbNewLine & _
"Kind Regards" & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
""
With OutlookMail
.To = "email.address.com"
.CC = ""
.BCC = ""
.Subject = "Please be aware that a new MDF for " & Sheets("Main Menu").Range("I10").Value & " " & Sheets("Main Menu").Range("I11 ").Value & " " & Sheets("Main Menu").Range("I12 ").Value & " " & Sheets("Main Menu").Range("I13 ").Value & " " & Sheets("Main Menu").Range("I14 ").Value & " " & Sheets("Main Menu").Range("I15 ").Value & " " & Sheets("Main Menu").Range("I16 ").Value & " " & Sheets("Main Menu").Range("I17 ").Value
.Body = mbody & sbody & Sheets("Project Leader").Range("d14").Value
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub
many Thanks
Richard
Try encoding the url:
url=Application.WorksheetFunction.EncodeUrl("https://gbconnect.sharepoint.com/sites/BUKGBGATEPROCESS/MDF/MAIDSTONE COMPLETED MDF FORMS 2021/" & ActiveWorkbook.Name)
ActiveWorkbook.SaveAs Filename:=url
Hi Catalin,
That gave the following runtime error 1004
Microsoft Excel cannot access the file
C:WINDOWSsystem3278741A00
When I debug it stops at the following line
ActiveWorkbook.SaveAs Filename:=Url
Url is as you stated Url = Application.WorksheetFunction.EncodeURL("https://gbconnect.sharepoint.com/sites/BUKGBGATEPROCESS/MDF/MAIDSTONE COMPLETED MDF FORMS 2021/" & ActiveWorkbook.Name)
I have no idea why its trying to look up C: drive
Even more confused
Kind Regards
Richard
Try recording a macro while saving the file manually to the address you mentioned:
https://gbconnect.sharepoint.com/sites/BUKGBGATEPROCESS/MDF/MAIDSTONE COMPLETED MDF FORMS 2021
Once you put that path in file explorer address, if you're not logged in you should see a popup asking to log in.
I assume you have the proper credentials to save there.
Another way is to go to that site in browser and sync that documents folder. (there should be a Sync button in that site documents page)
This will sync to a local folder and you can save locally, the sync agent will send the file to cloud.
You can also map a sharepoint folder:
Good Morning Catalin,
1. Will not work as I need the filename to change each time, the folder address is correct
2. I do have the proper credentials
3. The folder is already synced
4. When I try to Map the Sharepoint folder I get the error "The Folder you entered does not appear to be valid. Please choose another
This is proving to be a real challange
Kind Regards
Richard
If you're saying that the folder is already synced to your computer, why don't you save on the local synced folder?
Hi
Thanks for your continued support on this issue.
I dont actually want to attach the file to the email just a link to the file, I think my code is adding an attachment and changing the name due to spaces in the filename.
I need the person to click a button in the program which saves the file in the location and attaches a link that gets sent onto the next person by email, when they click the link it opens the actual file on SharePoint.
What I currently have is an attachment with the wrong name and read only
I really hope that makes sense
