VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email

D

Damien Hartzell

New Member
Joined
Jun 6, 2024
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi! I'm trying to utilize VBA to push data on an active sheet to an Outlook Email. The email populates perfectly with the appropriate text in the Body, however, the Table I'm expecting it to paste into the email returns as HTML code. How do I ensure the Table is displayed, as opposed to the HTML? What am I missing?

Thank you in advance, and apologies for the long details below.

VBA CODE:

VBA Code: 
Sub CreateEmailFromExcel()



On Error GoTo Err



Dim rng As Range

Dim OutApp As Outlook.Application

Dim OutMail As MailItem



Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)



Tenure = Range("H5").Text

Un = Range("H7").Text

weekend = Range("H3").Text



NewHireOccurrences = Range("C7").Value



NHCA1 = Range("B9").Text

NHWritten = Range("C9").Text



NHCA2 = Range("B10").Text

NHFinal = Range("C10").Text



TotalOccurrenceHours = Range("H10").Value



TenCA1 = Range("B15").Text

TenDocCoaching = Range("C15").Text

TenDocCoachingHoursSince = Range("F15").Value



TenCA2 = Range("B16").Text

TenWritten = Range("C16").Text

TenWrittenHoursSince = Range("F16").Value



TenCA3 = Range("B17").Text

TenFinal = Range("C17").Text

TenFinalHoursSince = Range("F17").Value



UnionOccurrences = Range("C20").Value



UnCA1 = Range("B22").Text

UnDocCoaching = Range("C22").Text

UnDocCoachingOccurrencesSince = Range("F22").Text



UnCA2 = Range("B23").Text

UnVerbal = Range("C23").Text

UnVerbalOccurrencesSince = Range("F23").Text



UnCA3 = Range("B24").Text

Un1stWritten = Range("C24").Text

Un1stWrittenOccurrencesSince = Range("F24").Text



UnCA4 = Range("B25").Text

Un2ndWritten = Range("C25").Text

Un2ndWrittenOccurrencesSince = Range("F25").Text



UnCA5 = Range("B26").Text

UnFinal = Range("C26").Text

UnFinalOccurrencesSince = Range("F26").Text





FMLAHours = Range("C29").Value



With OutMail

If Tenure = "New Hire" Then

.To = ""

.Subject = "Your Unscheduled and/or FMLA Time"

.Body = "Hello." & vbNewLine & vbNewLine & _

"Attendance is a vital aspect of employment. It's integral to supporting the larger team and, ultimately, the Members seeking important medical care on the other side of the work we do." & vbNewLine & vbNewLine & _

"You currently have " & NewHireOccurrences & " occurrences." & vbNewLine & vbNewLine & _

"Please, ensure all absences are scheduled and approved in NICE WebStation, as well as that you have the time to cover those absences in Workday." & vbNewLine & vbNewLine & _

"Reminders: a) Under 40 Unprotected Paid Unsch includes all unscheduled, paid Time Off Types (including FMLA), b) Over 40 Unprotected includes unscheduled paid time exceeding that 40 hours, as well as Unpaid and Blackout time accrued, and c) any time that indicates Protected is not applying toward 40-hour grace period or to disciplinary action steps." & vbNewLine & vbNewLine & _

"Thank you!" & vbNewLine & vbNewLine & _

RangetoHTML(rng)

.Display

End If



If Un = "Yes" Then

.To = ""

.Subject = "Your Unscheduled and/or FMLA Time"

.Body = "Hello." & vbNewLine & vbNewLine & _

"Attendance is a vital aspect of employment. It's integral to supporting the larger team and, ultimately, the Members seeking important medical care on the other side of the work we do." & vbNewLine & vbNewLine & _

"You currently have " & UnionOccurrences & " occurrences of unscheduled time." & vbNewLine & vbNewLine & _

"Your FMLA usage for the past 12 months is currently " & FMLAHours & " hours." & vbNewLine & vbNewLine & _

"Please, ensure all absences are scheduled and approved in NICE WebStation, as well as that you have the time to cover those absences in Workday." & vbNewLine & vbNewLine & _

"Thank you!" & vbNewLine & vbNewLine & _

RangetoHTML(rng)

.Display

End If



If Tenure = "Tenure" Then

.To = ""

.Subject = "Your Unscheduled and/or FMLA Time"

.Body = "Hello." & vbNewLine & vbNewLine & _

"Attendance is a vital aspect of employment. It's integral to supporting the larger team and, ultimately, the Members seeking important medical care on the other side of the work we do." & vbNewLine & vbNewLine & _

"You currently have " & TotalOccurrenceHours & " hours of unscheduled time off over your 40-hour grace period." & vbNewLine & vbNewLine & _

"Your FMLA usage for the past 12 months is currently " & FMLAHours & " hours." & vbNewLine & vbNewLine & _

"Please, ensure all absences are scheduled and approved in NICE WebStation, as well as that you have the time to cover those absences in Workday." & vbNewLine & vbNewLine & _

"Reminders: a) Under 40 Unprotected Paid Unsch includes all unscheduled, paid Time Off Types (including FMLA), b) Over 40 Unprotected includes unscheduled paid time exceeding that 40 hours, as well as Unpaid and Blackout time accrued, and c) any time that indicates Protected is not applying toward 40-hour grace period or to disciplinary action steps." & vbNewLine & vbNewLine & _

"Thank you!" & vbNewLine & vbNewLine & _

RangetoHTML(rng)

.Display

End If

End With



Set OutMail = Nothing



Err:

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.StatusBar = False

Application.DisplayStatusBar = oldStatusBar

Application.EnableEvents = True



End Sub



Function RangetoHTML(rng As Range)



Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook



TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"



Dim ActiveTable As String

Range("H15").Select

ActiveTable = ActiveCell.ListObject.Name

With Range(ActiveTable).ListObject



''

Range("H15:M15").Select

Range(Selection, Selection.End(xlDown)).Select



''

Selection.Copy

End With



'

Set Cell = Nothing



'Copy the range and create a new workbook to paste the data in

'''rng.Copy

On Error GoTo Err

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

'.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With



'''''''''''''''''''''''





'''''''''''''''''





'Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With



'Read all data from the htm file into RangetoHTML

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.readall

ts.Close

RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

"align=left x:publishsource=")



'Close TempWB

TempWB.Close savechanges:=False



'Delete the htm file we used in this function

Kill TempFile



Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing



Err:

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.StatusBar = False

Application.DisplayStatusBar = oldStatusBar

Application.EnableEvents = True



End Function



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

EMAIL CREATION RESULT:
HTML: 
Hello.

Attendance is a vital aspect of employment. It's integral to supporting the larger team and, ultimately, the Members seeking important medical care on the other side of the work we do.

You currently have 119 hours of unscheduled time off over your 40-hour grace period.

Your FMLA usage for the past 12 months is currently 70.3 hours.

Please, ensure all absences are scheduled and approved in NICE WebStation, as well as that you have the time to cover those absences in Workday.

Reminders: a) Under 40 Unprotected Paid Unsch includes all unscheduled, paid Time Off Types (including FMLA), b) Over 40 Unprotected includes unscheduled paid time exceeding that 40 hours, as well as Unpaid and Blackout time accrued, and c) any time that indicates Protected is not applying toward 40-hour grace period or to disciplinary action steps.

Thank you!

<html xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"
xmlns="[URL='http://www.w3.org/TR/REC-html40']HTML 4.01 Specification[/URL]">

<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 15">
<link rel=File-List href="06-06-24%2015-29-06_files/filelist.xml">
<style id="Sheet5_13818_Styles">
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
.xl1513818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl6313818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:white;
font-size:11.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:"Short Date";
text-align:center;
vertical-align:middle;
border-top:none;
border-right:.5pt solid white;
border-bottom:1.5pt solid white;
border-left:none;
background:#305496;
mso-pattern:black none;
white-space:nowrap;}
.xl6413818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:white;
font-size:11.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
border-top:none;
border-right:.5pt solid white;
border-bottom:1.5pt solid white;
border-left:.5pt solid white;
background:#305496;
mso-pattern:black none;
white-space:nowrap;}
.xl6513818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:white;
font-size:11.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:Fixed;
text-align:center;
vertical-align:middle;
border-top:none;
border-right:.5pt solid white;
border-bottom:1.5pt solid white;
border-left:.5pt solid white;
background:#305496;
mso-pattern:black none;
white-space:nowrap;}
.xl6613818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:white;
font-size:11.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
border-top:none;
border-right:none;
border-bottom:1.5pt solid white;
border-left:.5pt solid white;
background:#305496;
mso-pattern:black none;
white-space:nowrap;}
.xl6713818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:"Short Date";
text-align:center;
vertical-align:middle;
border-top:.5pt solid white;
border-right:.5pt solid white;
border-bottom:.5pt solid white;
border-left:none;
background:#B4C6E7;
mso-pattern:black none;
white-space:nowrap;}
.xl6813818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
border:.5pt solid white;
background:#B4C6E7;
mso-pattern:black none;
white-space:nowrap;}
.xl6913818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:Fixed;
text-align:center;
vertical-align:middle;
border:.5pt solid white;
background:#B4C6E7;
mso-pattern:black none;
white-space:nowrap;}
.xl7013818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
border-top:.5pt solid white;
border-right:none;
border-bottom:.5pt solid white;
border-left:.5pt solid white;
background:#B4C6E7;
mso-pattern:black none;
white-space:nowrap;}
.xl7113818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:"Short Date";
text-align:center;
vertical-align:middle;
border-top:.5pt solid white;
border-right:.5pt solid white;
border-bottom:none;
border-left:none;
background:#B4C6E7;
mso-pattern:black none;
white-space:nowrap;}
.xl7213818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
border-top:.5pt solid white;
border-right:.5pt solid white;
border-bottom:none;
border-left:.5pt solid white;
background:#B4C6E7;
mso-pattern:black none;
white-space:nowrap;}
.xl7313818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:Fixed;
text-align:center;
vertical-align:middle;
border-top:.5pt solid white;
border-right:.5pt solid white;
border-bottom:none;
border-left:.5pt solid white;
background:#B4C6E7;
mso-pattern:black none;
white-space:nowrap;}
.xl7413818
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:"Calisto MT", serif;
mso-font-charset:0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
border-top:.5pt solid white;
border-right:none;
border-bottom:none;
border-left:.5pt solid white;
background:#B4C6E7;
mso-pattern:black none;
white-space:nowrap;}
-->
</style>
</head>

<body>
<!--[if !excel]>&nbsp;&nbsp;<![endif]-->
<!--The following information was generated by Microsoft Excel's Publish as Web
Page wizard.-->
<!--If the same item is republished from Excel, all information between the DIV
tags will be replaced.-->
<!----------------------------->
<!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD -->
<!----------------------------->

<div id="Sheet5_13818" align=left x:publishsource="Excel">

<table border=0 cellpadding=0 cellspacing=0 width=384 style='border-collapse:
collapse;table-layout:fixed;width:288pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl6313818 width=64 style='height:15.0pt;width:48pt'>Date</td>
<td class=xl6413818 width=64 style='border-left:none;width:48pt'>Worker</td>
<td class=xl6413818 width=64 style='border-left:none;width:48pt'>Time Off
Type</td>
<td class=xl6413818 width=64 style='border-left:none;width:48pt'>Comment</td>
<td class=xl6513818 width=64 style='border-left:none;width:48pt'>Units</td>
<td class=xl6613818 width=64 style='border-left:none;width:48pt'>Occurrence
Type</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl6713818 style='height:15.0pt'>#####</td>
<td class=xl6813818 style='border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-left:none'>7.00</td>
<td class=xl7013818 style='border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>9.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>1.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Tardy</td>
<td class=xl6913818 style='border-top:none;border-left:none'>2.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>3.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>4.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>5.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>1.10</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>1.25</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>2.50</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>tardy</td>
<td class=xl6913818 style='border-top:none;border-left:none'>3.75</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Protected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>4.33</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Protected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Blackout</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>5.75</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Blackout</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unpaid</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Employee was
tardy</td>
<td class=xl6913818 style='border-top:none;border-left:none'>6.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unpaid</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unpaid</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>7.50</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unpaid</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.45</td>
<td class=xl7013818 style='border-top:none;border-left:none'>FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>New Hire</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>9.67</td>
<td class=xl7013818 style='border-top:none;border-left:none'>New Hire</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>New Hire</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>1.55</td>
<td class=xl7013818 style='border-top:none;border-left:none'>New Hire</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Protected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Protected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Protected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Protected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unpaid</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unpaid</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unpaid</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unpaid</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unprotected</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unprotected</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl6713818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl6813818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
<td class=xl6813818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl6913818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7013818 style='border-top:none;border-left:none'>Unsch Paid FMLA</td>
</tr>
<tr height=19 style='height:14.5pt'>
<td height=19 class=xl7113818 style='height:14.5pt;border-top:none'>#####</td>
<td class=xl7213818 style='border-top:none;border-left:none'>Scooby Doo</td>
<td class=xl7213818 style='border-top:none;border-left:none'>FMLA</td>
<td class=xl7213818 style='border-top:none;border-left:none'>&nbsp;</td>
<td class=xl7313818 style='border-top:none;border-left:none'>8.00</td>
<td class=xl7413818 style='border-top:none;border-left:none'>FMLA</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

</div>



<!----------------------------->
<!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD-->
<!----------------------------->
</body>

</html>
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
You have to use HTMLBody, not Body for html or rtf formatted emails. Try that instead.
If you want to increase your chances of getting help with code like that, use VBA code tags (VBA button on forum post toolbar) to maintain proper indentation (hopefully the code has that). I looked for .Body line and when I found it, stopped reading. I for one won't even read code like that - I'm too old and cranky! ;)
The double/triple line spacing is a buzz kill too. Besides, look what the forum did to your code with all those emojis.
 
Upvote 0
Solution
Welcome to the forum.

The body of the email would need to be HTMLBody rather than Body. You would also need to use HTML Tags for line spacing etc. Rather than VbNewLine as an example. If you look at a website called W3School it's a good place to get the additional HTML codes and examples. A link to the website is indicated below:

www.w3schools.com

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.
www.w3schools.com www.w3schools.com

As suggested by Micron please look to use the vb tags on the toolbar to help readers see the vb code.
 
Upvote 1
Micron said:
You have to use HTMLBody, not Body for html or rtf formatted emails. Try that instead.
If you want to increase your chances of getting help with code like that, use VBA code tags (VBA button on forum post toolbar) to maintain proper indentation (hopefully the code has that). I looked for .Body line and when I found it, stopped reading. I for one won't even read code like that - I'm too old and cranky! ;)
The double/triple line spacing is a buzz kill too. Besides, look what the forum did to your code with all those emojis.
Click to expand...
Wow! I completely missed that. Thank you, Micron! That did the trick. I'll be sure to utilize the VBA code tags and refrain from my code turning into emojis in the future lol
 
Upvote 0
Trevor G said:
Welcome to the forum.

The body of the email would need to be HTMLBody rather than Body. You would also need to use HTML Tags for line spacing etc. Rather than VbNewLine as an example. If you look at a website called W3School it's a good place to get the additional HTML codes and examples. A link to the website is indicated below:

www.w3schools.com

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.
www.w3schools.com www.w3schools.com

As suggested by Micron please look to use the vb tags on the toolbar to help readers see the vb code.
Click to expand...
Thanks, Trevor! I substituted the VbNewLine with "<br>" which did the trick. Thank you for the reference on the HTML codes and examples.
 
Upvote 0
Pleased to read you have a solution and thank you for letting us know.
 
Upvote 1
Damien Hartzell said:
Wow! I completely missed that. Thank you, Micron! That did the trick. I'll be sure to utilize the VBA code tags and refrain from my code turning into emojis in the future lol
Click to expand...
Welcome to the MrExcel Message Board!

And glad to hear your problem has been solved quickly. I just marked the answer post that you also pointed as the solution (and it is). In your future question, that would be awesome if you could mark the answer post when you receive a working answer, even if you posted your own solution to your question (it happens). If you see other posts that lead to the answer, then you can always Like multiple posts to show your reaction. This way, it will surely help the future readers.

More info about Mark as Solution.

I also edited your original post to wrap the code lines by using BB code tags as mentioned my @Micron and @Trevor G. It is surely more readable and portable this way.

Finally, once again, Welcome to the Board! :)
 
Upvote 1
smozgur said:
Welcome to the MrExcel Message Board!

And glad to hear your problem has been solved quickly. I just marked the answer post that you also pointed as the solution (and it is). In your future question, that would be awesome if you could mark the answer post when you receive a working answer, even if you posted your own solution to your question (it happens). If you see other posts that lead to the answer, then you can always Like multiple posts to show your reaction. This way, it will surely help the future readers.

More info about Mark as Solution.

I also edited your original post to wrap the code lines by using BB code tags as mentioned my @Micron and @Trevor G. It is surely more readable and portable this way.

Finally, once again, Welcome to the Board! :)
Click to expand...
Thanks for editing that, Smozgur! Duly noted on the Mark as Solution. Until next time!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
592
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
D
  • Solved
VBA Code: Table Column Formatting for Outlook Email
Replies
2
Views
523
Damien Hartzell
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,798
Messages
6,181,038
Members
453,013
Latest member
Shubashish_Nandy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top