I'm trying to send an email that includes a table from my excel file in the body of the email. I came across a rangetohtml function by Ron de Bruin, but I cannot get my email to display the table. The email shows where I tried to insert the table. All I did was copy and paste the VBA directly into one of the modules. I created the sub in module 1 and the functiontohtml in module 2. I've include the code below and some screenshots of the data.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
VBA Code:
Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")
Dim EItem As Object
Set EItem = EApp.CreateItem(0)
'**NEW STUFF ADDED 8/29
'declare varialbes: rows counts down and col counts across
Dim count_row, count_col As Integer
Dim revexp As Range 'table on that sheet based on the count row and count col variables
'**NEW STUFF ADDED 8/29
'row and column counts
count_row = WorksheetFunction.CountA(Range("A14", Range("A14").End(xlDown)))
count_col = WorksheetFunction.CountA(Range("A14", Range("A14").End(xlToRight)))
'**NEW STUFF ADDED 8/29
'set the table range for revexp sheet
Set revexp = Sheets("RevExp").Range(Cells(14, 14), Cells(count_row, count_col))
'****Display email and specify To, Subject, Body etc
With EItem
deptname = Range("J25")
.SentOnBehalfOfName = "abc@gmail.com"
.To = Range("J22")
.BCC = Range("J23")
.Subject = "FY24" & " " & "Budget Letter" & " " & "-" & " " & deptname & " " & "(" & Format(Date, "mm-d-yy") & ")"
'To break a single stmt into multiple lines use the underscore immediately preceded by a space and immediately followed by a line terminator like in the example below
.HTMLbody = "Dear Budget Holder,<br/><br/>We hope this email finds you well and in high spirits. We are pleased to inform you that the Team has received approved" _
& " budgets for <b>FY 2023 - 2024</b>. Below you will find your department's budget information for FY24." _
& " & RangetoHTML(revexp) & .HTMLbody" _
& "<br/><br/> <font color = red><b>Please review the attached budget letter as it includes vital information and key points pertaining to your department's budget." _
& "</font color></b><br/><br/>Warm regards,<br><img src='C:\Users\bbob11\OneDrive - Green Pages\Pictures\ABC Signature.jpg' 'height=200 width=300>" _
.Display
.Attachments.Add PDFFile
'Add CLFSC signature as an attachment and to hide the image attachment (set the position argument to O)
.Attachments.Add "C:\Users\bbob11\OneDrive - Green Pages\Pictures\ABC Signature.jpg", 1, 0
.Display
If DisplayEmail = False Then
.Send
End If
VBA Code:
Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
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"
'Copy the range and create a new workbook to past the data in
rng.Copy
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
End Function
