Send email with hyperlink in body referenced from cell

excel321

New Member
Joined
Mar 7, 2019
Messages
1
Sub sendmail()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range

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

ReportDirectory = Range("rngReport")
ReportSavePath = ReportDirectory
RngEmailTo = Range("rngmailto")
Dim filepath As String
Dim strbody As String
strbody = Sheets("macro").Range("L3").Value
On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("macro").Range("rngmailto")
.CC = Range("rngmailCC")
.Subject = ThisWorkbook.Sheets("macro").Range("F3:F3")
.Body = ThisWorkbook.Sheets("macro").Range("F32:F32") & strbody
.filepath = ThisWorkbook.Sheets("macro").Range("I3:I3")
.Attachments.Add ReportSavePath
.Body = "Please click on the link below..." & vbCrLf & vbCrLf & Sheets("macro").Range("I3").Value
"Need help with body to reference cel value containing directory and put as hyperlink
.Display

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing


End Sub
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,114,567
Messages
5,548,820
Members
410,874
Latest member
sriramvangara
Top