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 (KZread 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 = "abc@123.com"
.CC = "me@me.com"
.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