Excel VBA Macro: Send Email with Table in the Body (Step-by-Step Tutorial)

Excel VBA Macro: Send Email with Table in the Body (Step-by-Step Tutorial). In this video we go over how to send an email with a table/range in the body of the message using the RangetoHTML function.

#ExcelVBA #ExcelMacro

[greggowaffles]

Thank you for informing me about the removal of the RangetoHTML function from the website. Please find the code below:

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 =
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

[joelabacherli]

This is what YouTube is made for. Casual but extremely informative. Just like a buddy sitting with you. Your vids rock, man. Much appreciated.

[jidelk]

Bro, I can't express with words how much I'm loving you right now xD
It's 2:15 in the morning and you just saved my life. THANK YOU!

[franksuarez]

This is one of the best videos I´ve ever seen about this process! Thanks a million for this amazing contribution. You deserve more thumbs up!!!

[kunalvora]

Just couldn't figure out how to paste multiple tables from excel to email and just came across this video
multi table pasting works now, thanks a

[charvezard]

This channel is wildly helpful. Thank you.

[RajeshAttada]

This content made my day.
This is what I am exactly looking for.. from couple of weeks.
Greg... Your explanation is like, friend explaining things...
Lots of love from me ...to you.

Thank You so much for this help, subscribed, liked, commented and will be looking forward to consume more knowledge from you.
All the best for your future endeavors.

[honeydheeflores]

You have made my work so easy! Thank you so much for sharing this video with your awesome knowledge. God bless you!

[maxfeeney]

this is absolutely brilliant! thank you for your videos - any chance you may be able to help with formatting? my automatic Outlook signature by degault appears between the table and the “thanks, Greg” part! weird

[dannydanny]

This was very helpful Greg ... thanks a lot !!

[chintu]

hey Gregg, your videos have been very helpful to me
They are simple and very easy to understand for a beginner like me
Thank You so much

[chaimaebouhdid]

Thank you so much! thanks to your video I will be saving a lot of my time

[androjxe]

thank you for this tutorial.. I really appreciate it

[alexanderv]

Great explanation. Greetings from Russia ✌

[badrm]

Appreciate you to prepare this amazing tutorial.

[viswanathmanikandan]

Thank you Greg, This will just save my day :)
Keep up the good work :)

[Bombfun]

Thanks! It works! U solved my problem!

[tiffchy]

hi greg! thanks so much for this tutorial, it is certainly very helpful. some of the content in my tables are full sentences and some words are cut off. is there any other way that i can refer to the cells so that texts in combined cells can be sent as well? thanks!

[jayeshs]

Thank you so much... Found exactly as I needed 👍🏻

[jackshearer]

Great Video, I have data that starts in Cell A18, do I need to make any changes to the code that you have used?