Using Excel to Mass E-mail (Outlook) with links in the body

[MixedUpExcel]

Hi all,

I have a list of customers (over 100). I don't want to use a mail merge or database etc. Just simply have the list of e-mail addresses, subject, body of text in a spreadsheet.

The below vba code works perfectly EXCEPT, someone has just asked me to include a hyperlink within the body of the text.

Issue 1. How do you put a hyperlink in the middle of a body of text in a single cell in Excel?
- eg. This is the hyperlink to click here and not the remainder of the text
Only the text in blue is the clickable link, not the rest of the text

Issue 2. If I can find a way to sort out issue 1, would this be transferred to the e-mail as part of the body text in the vba code?

I've thought about referencing another cell to build up the body text cell using formula's but not sure if this would work as I have another hyperlink I also have to include further down the body of text.

Any suggestions would be great. Thanks. Simon

Here's the code which works - it's just the hyperlink issue I'm stuck with.

Sub MailList()

Dim LastRow As String

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = 4 To LastRow ' this is how many rows to check for e-mail addresses

On Error Resume Next

    Dim OutApp As Variant
    Dim OutMail As Variant
    
Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next

    With OutMail
        .To = Cells(i, 2)
        .Cc = Cells(i, 3)
        .BCC = ""
        .Subject = Cells(i, 4)
        .Body = Cells(i, 5)
        .SentOnBehalfOfName = Cells(4, 1)
        On Error Resume Next

        On Error GoTo 0
         .Save
        .Display
        .Send '<<<<< if you want to send direct remove "'" to be .send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    On Error Resume Next

Next i

End Sub

 

[Ruddles]

You have to use HtmlBody:-

.HtmlBody = "Some text..." & "<a href=""http://google.com"">hyperlnk to click here</a>" & "...some more text"

Any good?

 

[MixedUpExcel]

You have to use HtmlBody:-

.HtmlBody = "Some text..." & "<a href=""http://google.com"">hyperlnk to click here</a>" & "...some more text"

Any good?

Hi Ruddles,

Thank you for such a quick response.

Let me test it and get back to you.

As there are multiple paragraphs within the text, looking at your suggestion, I'm going to have to put all of the text in the vba rather than reference text in a cell, I'm going to have to put line breaks in as well.

I'll play around with it and let you know but thank you again.

Simon

 

[Ruddles]

You can get additional text from the worksheet or hard-code it, whichever's more convenient/flexible. Just build the entire email in .HtmlBody like this:-

.HtmlBody = "Dear " & Cells(i, 2) & "," & vbcrlf & vbcrlf _
& "Some text..." & Cells(i, 5) & "... some more text..." & vbcrlf & vbcrlf _
& "Loads more text for the email..." & vbcrlf & vbcrlf _
& "Regards " &  Cells(4, 1)

 

[MixedUpExcel]

Thanks Ruddles,

I've done a bit of testing. I didn't know about the htmlbody part of the e-mail structure but that and the code you gave me for the link worked perfectly.

Did have problems with the bulk of the text, it was putting it all in a single paragraph, so after a bit of digging, found that <p></p> around them to give me a line break worked well ( like building a website ).

Thank you for the extra bit of code you've provided, I'll try that later but for now, what you initially provided works.

Thanks.

Simon