How can I use HTML code (bold text; insert table etc) in an outlook mail send from excel via macro

[BigX84]

Hi everyone,

I have to admit I am totally new to developing macro's in excel and I am trying to get a macro going that will make my work (and that of my colleagues) a lot easier/ more efficient.
I have used a macro that I found online and I am trying to customize it to fit my needs. In the past I have done some coding in PHP / HTML but this is totally new to me.
What I am trying to do is let excel send myself an email when a certain date is almost due in the excel sheet. So far so good (since I used the macro I found) but the problem is that I want to customize the email that is send to my outlook. Some parts I want in bold, I want to insert a table and some hyperlinks. Usually I will find my way by searching online for similar problems and using common sense, but this is giving me more headache's then it needs to. i put the code below.

Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Email = Cells(ActiveCell.Row, 10)
        
    Subj = Cells(ActiveCell.Row, 4)
    Msg = ""
    Msg = Msg & "Dear Joost," & vbCrLf & vbCrLf & "The last Site management Call (SMC) was performed on " & Cells(ActiveCell.Row, 5) & ", so according to the Monitor Plan the next SMC should be planned around " & Cells(ActiveCell.Row, 7) & ". " & vbCrLf & vbCrLf & Cells(ActiveCell.Row, 13) & vbCrLf & vbCrLf & " And here is some more precanned text in the macro AFTER the Body stuff."
    
    'Replace spaces with %20 (hex)
    Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
    Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
    
    'Replace carriage returns with %0D%0A (hex)
    Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
    
    'Create the URL
    URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
    
    'Execute the URL (start the email client)
    ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
    
    'Wait two seconds before sending keystrokes
    'Application.Wait (Now + TimeValue("0:00:02"))
    'Application.SendKeys "%s"
End Sub

As you can see, this is far from finished...there is even some prewritten text left from the macro I 'borrowed'.
So in this example I only got one real sentence in the email, but I need to know some basic stuff to continue. This is the sentence as it shows in the email:

The last Site management Call (SMC) was performed on 25-11-2016, so according to the Monitor Plan the next SMC should be planned around 25-12-2016.

- How can I make these dates (they are pulled from the excel sheet) bold? I found something online that people use .HTMLbody and then HTML code can be used, but I have now .body in this macro so I didn't know how to integrate it. Can someone alter / explain this?
- How could I integrate a hyperlink in the email? Let's take www.mrexcel.com as an example.
- How would I insert a table and play around with the dimensions/contents?

I hope it is clear what I am trying to do here and someone of you is willing to help me out. I know newcomers on many forums are told to search first before asking but yesterday I searched for a few hours to solve these issues, but when I tried to incorporate some possible solutions, the macro crashed immediately or gave a very different output! I will note again that I am no excel specialist of any kind, nor do I aspire to be, it's just that I am trying to proactively solve some issues I am encountering in day to day work.

Thanks very much in advance!

Kind Regards

 

[BigX84]

Anyone? When I saw the amount of replies on this page I thought this would be a piece of cake for all the excel veterans but maybe not?

 

[BigX84]

Tried a few other things but still no luck! Any ideas guys?

 

[Sektor]

You need to use Outlook's object model.

 

[BigX84]

Thanks for the reply man! Any chance you can elaborate a bit on that?

 

[BigX84]

You need to use Outlook's object model.

I did find this:

Change the Body line
If you want to add more text to the body then instead of .Body = "Hi there" use this.
Dim strbody As String
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
Or use this if you want to use cell values from a range
Dim cell As Range
Dim strbody As String
For Each cell In ThisWorkbook.Sheets("Sheet1").Range("C1:C60")
strbody = strbody & cell.Value & vbNewLine
Next
Or this
Dim strbody As String
With ThisWorkbook.Sheets("Sheet1")
strbody = "Hi there" & vbNewLine & vbNewLine & _
.Range("A1") & vbNewLine & _
.Range("A2") & vbNewLine & _
.Range("A3") & vbNewLine & _
.Range("A4")
End With
Change the Body line to .Body = strbody to use the string in the body.

Use HTMLBody instead of Body
Formatted text (HTML)

If you want to create emails that are formatted you can use HTMLBody (Office 2000 and up) instead of Body.You can find a lot of WebPages on the internet with HTML tags examples.
strbody = "<H3><B>Dear Customer</B></H3>" & _
"Please visit this website to download the new version.<br>" & _
"Let me know if you have problems.<br>" & _
"<A HREF=""http://www.rondebruin.nl/"">Ron's Excel Page</A>" & _
"<br><br><B>Thank you</B>"
Use .HTMLBody = strbody then instead of .Body = strbody
If you want to send a range or worksheet as HTML see this two examples pages.

-------------------------

But in my code I cannot find anything like .Body or strbody...how do I incorporate this. I believe this should be so simple but I have just no clue... If I can get to use html code then I should be back up to speed!

 

[BigX84]

Anyone please?

 

[BigX84]

desperate bump...

 

[LockeGarmin]

I think the lack of response is that your question has a pretty large scope. You're asking help that deals with Excel, connecting Excel to Outlook with the Outlook Library, the subtle nuances with editing the MailItem.HTMLBody with HTML and just knowing HTML for bolding, hyperlinking and tables (as you'll see getting the table to work is pretty large). I'm not trying to make you feel bad, I totally get that many times we don't know what we don't know, but that hopefully sheds some light on why no one was answering.

Anyway, most of this code was pre-written in my own library. When you are ready to have this send automatically just uncomment the last lines. I'm not sure you would have still wanted to two second delay but I kept it anyway from your other code example.

Finally, Please read this paragraph if you don't read anything else I've written besides the code: You may notice that in the VBA Code I posted below all of the HTML Tags have an extra space in front of the '<' character. This is because If I don't do that this forum's message system will translate them to HTML and in most cases cause the text to disappear. When you put this in your code you must remove each space in front of each of the '<' character. You can use a find and replace to do this as that is what I did to post it here.

Hope this helps!

Sub CreateEmail()

Dim OutApp As Object 'Outlook.Application
Dim Email As Object 'Outlook.MailItem
Dim Msg As String

Const olMailItem As Long = 0

  'Building Email Message
  Msg = "Dear Joost," & HtmlLineBreak(2)
  Msg = Msg & "The last Site management Call (SMC) was performed on " & HtmlBold(Cells(ActiveCell.Row, 5).Value2) & _
              ", so according to the Monitor Plan the next SMC should be planned around " & HtmlBold(Cells(ActiveCell.Row, 7).Value2) & ". " & HtmlLineBreak(2)
  
  Msg = Msg & Cells(ActiveCell.Row, 13).Value2 & HtmlLineBreak(2)
  
  Msg = Msg & HtmlHyperLink("http://www.mrexcel.com", "Mr. Excel") & HtmlLineBreak(2)
  
  Msg = Msg & HtmlTable(Range("A1:D10").Value2, IncludeHeader:=True)

  'Setting up outlook objects
  Set OutApp = GetOutlookApplication
  Set Email = OutApp.CreateItem(olMailItem)
  
  Email.Display 'Do not delete this line even if you plan on sending. Doing so will can mess up the formatting
  
  Email.To = Cells(ActiveCell.Row, 10).Value2
  Email.Subject = Cells(ActiveCell.Row, 4).Value2
  Email.HTMLBody = "< p style='font-family:Calibri;font-size:14.5;display:inline'>" & Msg & Email.HTMLBody & "< /font>< /p>"
  
  'Wait two seconds before sending email
  Application.Wait (Now + TimeValue("0:00:02"))
  'Email.Send
  
End Sub

Private Function GetOutlookApplication() As Object

  On Error GoTo OpenApplication
  Set GetOutlookApplication = GetObject(, "Outlook.Application")
  
Exit Function

OpenApplication:
  Set GetOutlookApplication = CreateObject("Outlook.Application")
  Err.Clear
  
End Function

Private Function HtmlBold(Text As String) As String
  HtmlBold = "< b>" & Text & "< /b>"
End Function

Private Function HtmlHyperLink(Address As String, Optional DisplayText As String) As String
  HtmlHyperLink = "< a href=""" & Address & """>" & IIf(DisplayText = vbNullString, Address, DisplayText) & "< /a>"
End Function

Private Function HtmlLineBreak(Optional Num As Long = 1) As String

  HtmlLineBreak = VBA.Replace(VBA.String$(Num, vbNullChar), vbNullChar, "< br>")
  
End Function

Private Function HtmlTable(TwoDimArr As Variant, Optional IncludeHeader As Boolean) As String

Dim lRecord As Long
Dim lField As Long

Dim sTable As String

  'Beginning Table
  sTable = sTable & "< table border = ""2"">"
  
  'Table Header
  If IncludeHeader Then
    lField = LBound(TwoDimArr, 1)
    For lRecord = LBound(TwoDimArr, 2) To UBound(TwoDimArr, 2)
      sTable = sTable & "< th>" & TwoDimArr(lField, lRecord) & "< /th>"
    Next lRecord
  End If
  
  'Table Body
  For lField = LBound(TwoDimArr, 1) + IIf(IncludeHeader, 1, 0) To UBound(TwoDimArr, 1)
    sTable = sTable & "< tr>"
      For lRecord = LBound(TwoDimArr, 2) To UBound(TwoDimArr, 2)
        sTable = sTable & "< td>" & TwoDimArr(lField, lRecord) & "< /td>"
      Next lRecord
    sTable = sTable & "< /tr>"
  Next lField
  
  'End Table
  sTable = sTable & "< /table>"
  
  HtmlTable = sTable

End Function

 

[BigX84]

Thank you very much LockeGarmin! I really appreciate the help and the code you provided! I believe you are absolutely right in thinking the lack of response came from the scope of my question, but like you predicted, I had absolutely no idea that the scope was that large! =) For us non-programmers some things seem pretty 'simple' when it in fact is way more complicated. That was the case this time. Yesterday I spend a few hours reading and trying an extensive tutorial about Excel object model, Outlook object model, early and late binding and the .HTMLbody vs .body. It was then that I realized that the whole code I had needed to be rewritten and I also understood why there wasn't a short question to my question on this forum.

Like I said, I got a new code that is working (I might post it later but on mobile now) with HTML formatting but I didn't incorporate the table yet! I will try to use your code for this so it is very useful! I am sure I can learn much from it anyway and it is probably more efficient than what I did anyway! =)

Thanks again for your time and to everyone that reads this...Merry Christmas!