Public Function SheetToHTML(sh As Worksheet)
      Dim TempFile As String
    Dim fso As Object
    Dim ts As Object
      sh.Copy
    TempFile = sh.Parent.Path & "\TempHtml.htm"
      With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
    TempFile, "Sheet1", "A1:F30", xlHtmlStatic, "333_8568" _
    , "")
    .Publish (True)
    .AutoRepublish = False
    End With
      ActiveWorkbook.Close False
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
      SheetToHTML = ts.ReadAll
   ts.Close
    Set ts = Nothing
    Set fso = Nothing
    Kill TempFile
    End Function

Declare Function FindWindowByClass Lib "user32" _ Alias "FindWindowA" (ByVal lpClassName As String, _ ByVal lpWindowName As Long) As Long

 Sub CommandButton1_Click()
    Dim mailApp As Outlook.Application
    Dim dfg As Object

'поиск окна Microsoft Outlook
    lngRetVal = FindWindowByClass("rctrl_renwnd32", 0&;)
        If lngRetVal <> 0 Then
    Set mailApp = GetObject(, "Outlook.Application")
     Else
    Set mailApp = CreateObject("Outlook.Application")
    End If

    Set objMail = mailApp.CreateItem(olMailItem)
    Set dfg = objMail.Recipients.Add("test@test.com")
    dfg.Type = olTo

With objMail
    .Importance = olImportanceHigh
    .Subject = "Your Subject"
    .BodyFormat = olFormatHTML 'формат HTML
    .HTMLBody = SheetToHTML(ThisWorkbook.Worksheets("tasks"))
End With


'Предварительный просмотр письма 
'objMail.Display

'Отправка письма
objMail.Send


Set objMail = Nothing
Set mailApp = Nothing


End Sub