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

 

[LockeGarmin]

You're welcome! Merry Christmas and a Happy New Year to you as well! I'm glad you were able to get most of the code you were looking for.

I saw your response and got inspired to see if there were any classes that shipped in windows for building HTML content. I did end up finding the Microsoft HTML Object Library and figured out how to use late binding with it! The code below might not be as "efficient" in terms of speed to what I posted earlier, but I find it to be a lot cleaner then trying to stitch the HTML Together myself. The actual HTML from the two functions will be different, but they will have the same effect in an Outlook message.

Anyway I thought I'd share since you were interested in the code for an HTML Table too.

Function TableFromArray(TwoDimArray As Variant, Optional HasHeader As Boolean) As String

Dim HtmlDoc As Object 'MSHTML.HTMLDocument '
Dim Table As Object 'MSHTML.HTMLTable '
Dim Row As Object 'MSHTML.HTMLTableRow '

Dim x As Long
Dim y As Long

  Set HtmlDoc = CreateObject("HTMLFile")
  Set Table = HtmlDoc.createElement("Table")
  Table.Border = 2
  
  For y = LBound(TwoDimArray, 1) To UBound(TwoDimArray, 1)
    Set Row = Table.insertRow()
    For x = LBound(TwoDimArray, 2) To UBound(TwoDimArray, 2)
      Row.insertCell().innerText = TwoDimArray(y, x)
    Next x
  Next y
  
  If HasHeader Then
    Set Row = Table.Rows(0)
    Row.Align = "Center"
    Row.Style.fontWeight = "bold"
  End If
  
  HtmlTableFromArray = Table.outerHTML
    
End Function

 

[BigX84]

Thank you very much LockeGarmin! I will take a look at this later and see if I should alter the code a bit! Thanks for the extra work helping me!