Excel VBA Macro: Send Email with Hyperlink (that has Spaces in the File Name)

Excel VBA Macro: Send Email with Hyperlink (that has Spaces in the File Name). In this video, we go over how to ensure that a hyperlink with spaces in the filename does not get cut off after the first space.

Code (YouTube doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):

Sub send_hyperlink_with_spaces()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim link As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

link = Replace(ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Text, " ", "%20")
MsgBox link

strbody = "LT BODY style = font-size:14pt; font-family:Arial GT" & _
"Hello Team, LT p GT Please see the link for today: " & _
"LT a href= " & link & "GT here LT / GT LTp GT" & _
"Best regards, LT br GT Greg"

On Error Resume Next
With OutMail
.to = "[email protected]"
.CC = "[email protected]"
.BBC = ""
.Subject = "Morning Link " & Format(Date, "mm-dd-yyyy")
.display
.HTMLBody = strbody & .HTMLBody

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

#ExcelVBA #ExcelMacro