add table in .HTMLBODY
see sample below
'Look for the mail address in the MailInfo worksheet
mailAddress = ""
On Error Resume Next
mailAddress = Application.WorksheetFunction.VLookup(Cws.Cells(Rnum, 1).Value, Worksheets("Mailinfo").Range("A1:Z" & Worksheets("Mailinfo").Rows.Count), 2, False)
ccMailAddress = Application.WorksheetFunction.VLookup(Cws.Cells(Rnum, 1).Value, Worksheets("Mailinfo").Range("A1:Z" & Worksheets("Mailinfo").Rows.Count), 3, False)
strSubject = Application.WorksheetFunction.VLookup(Cws.Cells(Rnum, 1).Value, Worksheets("Mailinfo").Range("A1:Z" & Worksheets("Mailinfo").Rows.Count), 4, False)
strAccount = Application.WorksheetFunction.VLookup(Cws.Cells(Rnum, 1).Value, Worksheets("Mailinfo").Range("A1:Z" & Worksheets("Mailinfo").Rows.Count), 1, False)
On Error Resume Next
If mailAddress <> "" Then
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Set OutMail = OutApp.CreateItem(0)
Set myOlApp = CreateObject("Outlook.Application")
Set myMail = myOlApp.CreateItem(olMailItem)
On Error Resume Next
Dim CountAInv As Integer
Dim CountCash As Integer
Dim CountAcct As Integer
Dim CountBR As Integer
Dim strCsh As String
Dim strAct As String
Dim strNoInv As String
Dim strBR As String
strCsh = "DUTYTHCSH"
strAct = "569*"
strNoInv = ""
strBR = "BRKR"
'vlookup data between excel & body message for sum total in body
If strAccount = strSubject Then
SumOfD = WorksheetFunction.SumIf(Range("Sheet1!A5:A" & LastRow), strAccount, Range("Sheet1!E5:E" & LastRow))
End If
If strAccount = strSubject Then
CountAInv = WorksheetFunction.CountIfs(Range("Sheet1!A5:A" & LastRow), strAccount, Range("Sheet1!B5:B" & LastRow))
End If
If strAccount = strSubject And strCsh = "DUTYTHCSH" Then
CountCash = WorksheetFunction.CountIfs(Range("Sheet1!A5:A" & LastRow), strAccount, Range("Sheet1!D5:D" & LastRow), strCsh)
End If
If strAccount = strSubject And strAct = "569*" Then
CountAcct = WorksheetFunction.CountIfs(Range("Sheet1!A5:A" & LastRow), strAccount, Range("Sheet1!D5:D" & LastRow), strAct)
End If
If strAccount = strSubject And strNoInv = "" Then
CountNoInv = WorksheetFunction.CountIfs(Range("Sheet1!A5:A" & LastRow), strAccount, Range("Sheet1!D5:D" & LastRow), strNoInv)
End If
If strAccount = strSubject And strBR = "BRKR" Then
CountBR = WorksheetFunction.CountIfs(Range("Sheet1!A5:A" & LastRow), strAccount, Range("Sheet1!I5:I" & LastRow), strBR)
End If
With OutMail
.To = mailAddress
.CC = ccMailAddress
.Subject = ""
.HTMLBody = "<table width=""1100"" border=1 black;>" & RangetoHTML(rng) & _
" </tr></table>" & _
"<p><table style= background-color:#FFCC00; width=""1270"" border=1 black;><tr><td width=""900"">Total document Cash =" & CountCash & " Credit = " & CountAcct & " NoInvoice = " & CountNoInv & " Broker = " & CountBR & "</td>" & "<td width=""400"" align='center'><b>" & CountAInv & "</b></td></tr></table>