+ Reply to Thread
Results 1 to 3 of 3

Need help with including a hyperlink in an email from a specific cell

  1. #1
    Registered User
    Join Date
    12-19-2011
    Location
    Wilmington, OH
    MS-Off Ver
    Excel 2010
    Posts
    3

    Need help with including a hyperlink in an email from a specific cell

    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

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Need help with including a hyperlink in an email from a specific cell

    strBody would need to contain html tags,... try switching the strBody string to contain html tags for new lines and then use the htmlBody property instead of TextBody

    Please Login or Register  to view this content.
    and then:

    Please Login or Register  to view this content.
    P.S. Please review the forum rules, especially # 3 about using code tags.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    12-19-2011
    Location
    Wilmington, OH
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need help with including a hyperlink in an email from a specific cell

    That works Arkadi! Thank you very much! And I'll be sure to review all the rules and use code tags for future threads. And just in case anyone ever uses this thread as reference, I ended up using
    Please Login or Register  to view this content.
    for my cell reference. Thanks again, Arkadi!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA to send email using data from the EXCEL including email address, subject and body text
    By ec4excel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2016, 11:07 AM
  2. Creating Email & Including Specific Corresponding Data from Range
    By Pauly723 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-30-2014, 03:37 PM
  3. How to send email from excel using VBA with Cell Range (Including Images) as Email Body
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2014, 05:06 AM
  4. [SOLVED] Hyperlink a Cell in an email
    By Will_iam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2013, 05:51 AM
  5. Including an Hyperlink on email
    By Andrew.Trevayne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-02-2013, 12:19 AM
  6. [SOLVED] insert a hyperlink into an excel cell to refer to a specific email in outlook
    By Joe McLoughlin in forum Excel General
    Replies: 3
    Last Post: 08-03-2012, 05:25 PM
  7. Replies: 9
    Last Post: 10-12-2010, 12:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1