jasonfish11
Board Regular
- Joined
- May 14, 2015
- Messages
- 56
Hi,
I have a macro that sends an email to a group of people with a hyperlink back to the excel file.
My company recently switched from SharePoint to SharePoint Online (SPO) and now this hyperlink won't allow them to open the file from SPO without saving a copy of it. I'd much prefer to have them go directly into the actual workbook, and do NOT want them saving copies to other locations (or worse saving duplicates on SPO).
The reasoning for this is the hyperlink in the email is as follows...
Departure2 (TEST) - 06.30.2022.xlsm
The file name in red has spaces in it. Which seems to be causing the issue when someone clicks directly onto the hyperlink. When I copy the hyperlink from the email and paste it directly into the browser the hyperlink automatically changes to...
Departure2%20(TEST)%20-%2006.30.2022.xlsm
When the spaces are replaced with "%20" things work exactly like I want.
Is there a way to get this formatting directly into the email so that people can click the hyperlink and go straight to the workbook without being asked to save a copy?
I have a macro that sends an email to a group of people with a hyperlink back to the excel file.
My company recently switched from SharePoint to SharePoint Online (SPO) and now this hyperlink won't allow them to open the file from SPO without saving a copy of it. I'd much prefer to have them go directly into the actual workbook, and do NOT want them saving copies to other locations (or worse saving duplicates on SPO).
The reasoning for this is the hyperlink in the email is as follows...
Departure2 (TEST) - 06.30.2022.xlsm
The file name in red has spaces in it. Which seems to be causing the issue when someone clicks directly onto the hyperlink. When I copy the hyperlink from the email and paste it directly into the browser the hyperlink automatically changes to...
Departure2%20(TEST)%20-%2006.30.2022.xlsm
When the spaces are replaced with "%20" things work exactly like I want.
Is there a way to get this formatting directly into the email so that people can click the hyperlink and go straight to the workbook without being asked to save a copy?
VBA Code:
[LIST]
[*]Dim OutApp As Object
[*]Dim OutMail As Object
[*]Dim Strg As String
[*]Dim SeatLoc As String
[*]Dim Title As String
[*]Dim Ext As String
[*]
[*]
[*]'Build Distribution List
[*]
[*]Call BuildEmailDistroList
[*]
[*]'Set email distribution
[*]
[*]With Sheets("Email Distributions").Range("B2:B500")
[*]Strg = Replace(Application.Trim(Join(Application.Transpose(.Value), " ")), " ", ";")
[*]End With
[*]
[*]'EMAIL
[*]If Sheets("Dashboard").Range("G2").Value = True And Sheets("Dashboard").Range("G3").Value <> True Then
[*]Set OutApp = CreateObject("Outlook.Application")
[*]Set OutMail = OutApp.CreateItem(0)
[*]
[*]With OutMail
[*].To = Strg
[*].CC = ""
[*].BCC = ""
[*].Subject = Sheets("Email").Range("C7").Value & " - " & Sheets("Dashboard").Range("B12").Value
[*].HTMLBody = "<BODY style=" & Chr(34) & "font-family:AvenirNext LT Com Regular" & Chr(34) & ">" & _
[*]A bunch of coding & "<br>" & "<br>" & _
[*]"<a href= """ & ThisWorkbook.FullName & """ >Departure Checklist</a>" & "<br>" & "<br>" & "Thank you," & "<br>" & Sheets("Dashboard").Range("B8").Value
[*].Display
[*]End With
[/LIST]