Needing workbook full name in html format

J

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?
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]
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Incorporate this function to convert a string to URL encoding. Courtesy of How can I URL encode a string in Excel VBA?


(By the way you marked your code as a "list" which really messes with the formatting)

Where you reference ThisWorkbook.FullName use URLEncode(ThisWorkbook.FullName)

VBA Code: 
Public Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function
 
Upvote 0
Solution
6StringJazzer said:
Incorporate this function to convert a string to URL encoding. Courtesy of How can I URL encode a string in Excel VBA?


(By the way you marked your code as a "list" which really messes with the formatting)

Where you reference ThisWorkbook.FullName use URLEncode(ThisWorkbook.FullName)

VBA Code: 
Public Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function
Click to expand...
Thank you this was helpful and got me to what I need.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Workbook in attachement of Email - specific Xls format . How to ?
Replies
1
Views
292
Fluff
Fluff
G
  • Question
Email from Excel query
Replies
0
Views
167
gmazza76
G
G
  • Solved
Dynamic Button for Email Macro
Replies
4
Views
215
GarnesGambit
G
V
  • Question
Email Trigger
Replies
2
Views
247
vmjan02
V
B
  • Solved
Change existing code to attach range of cells to email
Replies
2
Views
219
bh24524
B

Forum statistics

Threads
1,214,895
Messages
6,122,128
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top