of course
Sub send_hyperlink_with_variable()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim name As String
Dim link As String
Dim email As String
Dim area As String
Dim sh As Worksheet
Dim dtDate As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set sh = ThisWorkbook.Sheets("Royal London")
'name = sh.Cells(1, 2).Text
link = sh.Cells(1, 3).Text
email = sh.Cells(1, 4).Text
area = sh.Cells(1, 5).Text
dtDate = Format(Now() + 7, "dd mmmm yyyy")
'MsgBox dtToday
strbody = "<BODY style = font-size:12pt; font-family:Arial>" & _
"Hello " & name & ", <p> Your Health, Safety & Wellbeing Inspection of the following area needs completing - " & area & "! " & _
"Please complete this by " & dtDate & " " & _
"<a href=""" & link & """>click here for your blank inspection form.</a><P>" 'Use 3No. speech marks for the full link, 1No. will only give you the until the first space
On Error Resume Next
With OutMail
.to = email
.CC = "
Benjamin.Pollard@skanska.co.uk "
.BCC = ""
.Subject = "Health, Safety & Wellbeing Inspection Due"
.Attachments.Add "P:\02.0 - Health & Safety\2.5 H&S Monitoring\Royal London Hospital\1. RLH Inspection Rota 2021\RLH Inspection Rota October 2021 - March 2022.xlsx"
.Display
.HTMLBody = strbody & .HTMLBody
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
