Hello! I have a macro that generates an email and it works fine. The thing that I am unable to do is create a hyperlink within the email that comes from a specific cell in the worksheet. I'm still relatively new to VBA, and I assume it's a simple fix. Any help is appreciated. Please see my code below. Thanks!
The code that I currently have for the hyperlink is 21 lines down and highlighted in blue.
For Each cell In Range("M32:M499")
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim Legal As String
Legal = John & " " & Smith & "," & Keith & " " & Lowe & "," & Melody & " " & Griffith
If target.Address = cell.Address And cell.Offset(0, -4).Value = "Yes" And cell.Offset(0, -3).Value <> Legal Then
If cell.Offset(0, -1).Value = "Sent" Then Exit Sub
strSubject = "Request Review"
strFrom = "[email protected]"
strTo = cell.Offset(0, -2).Value
strCc = "[email protected]"
strBcc = ""
strBody = "Please review document ID " & cell.Offset(0, -11).Value & "." & vbNewLine & vbNewLine & _
<a href=""" & cell.Offset(0, -5).Value & """>Docs</a>
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo error_handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "outlook.hq.abcco.int"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send
error_handling:
If Err.Description <> "" Then MsgBox Err.Description
cell.Offset(0, -1).Value = "Sent"
cell.Offset(0, 9).Value = Date
End If
Next cell
Bookmarks