Excel VBA Macro: Send Email With Table In Body (Dynamic Range) 

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

HTMLbody is not working in my outlook 16 client

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

Send Email With Multiple Tables In Body (Static and Dynamic Ranges): ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9q8gZF0UOt8.html

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

Thank you Greg for this video, this is what I was looking for. I tried to replicate the same but getting an error Error "Run-time error 1004, Application-defined or object-defined error" in the line Set pop = Sheets("Form").Range(Cells(1, 1), Cells(count_row, count_col)) is it possible to share this file please :)

Do you have a file with this VBA code that you could share with me, please? Ron took down his code.

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!

This works really well, thank you for sharing! One issue I have is that the code only works when the sheet is active, for example the Population sheet needs to be active for the code to work, if I select an another sheet or hide it I get a Run-time error '1004':. Do you know a way to fix this? Thank you once again.

Sub function denied

Any alternative links? Someone pissed off Ron and he scrubbed his site

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!

Hi, it works fine, thank you! The only issue I face is that it cuts off the last row of the table and the table is therefore incomplete within the email (I have only 4 rows in total and it is showing only 3 of them). Not sure what is the reason.

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.

This is Helpful. Can you help with a code which can be used to send multiple emails with different tables. Appreciate your help.

Site is no longer working, can someone please share the code huhu

Anyone know where I can find this code? The website has been changed and is no longer offering help. If possible, could someone copy / paste in a reply. Thanks!

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

Terrificc...thanks a ton!!! 🙏🙏

Does the Range to HTML function works in Excel 2019?

Great Tutorial. Question if anyone sees this. When I run it to test, it opens up the email, but then launches another instance of excel and displays the table there, not in the email body. Does anyone know what I may be doing wrong? Thx.

Hi Greeg, I need to insert the png image file into the mail body....I have tried it.The only a blank mail which I was getting..Even I am using .HTMLBody = "" or Folder path also given. Nothing is happend.. Can u plz help me on this?😇

Hi Greg, thanks for this useful tutorial. do you have a video showing how I can filter a table, and vlookup a table on another sheet with the corresponding email addresses? for example in your table, i want to send any rows with China to email address 1 and rows with India to email address 2.

I want to copy the excel range to send an email respect to the current date. Example: The table range is A1:D20 Column A for Date. Now I want to copy the range A5:D9 to send an email body, A5 to A9 cell value are current date.

Thanks but it is not working in my excel. it is 2019. do you have updated code?

Ron's site is no longer available... can you paste the code somewhere so I can copy it?

How to make this dynamic with To and Cc

What if I want to insert Country names in Subject by obtaining from Sheet? What if I want to send the same mail on more than 1 e-mail ID in "To" and "Cc"?

Can you post code for module 2 I cannot

Hello, does it work with office 365 plz? No error when running the code but no email generated

very useful.... can you also please suggest what code to use if the table has to be filtered and only the filtered rows to be displayed in the mail body??

Hello, I want to export the selected area as jpeg or png in Excel. Can you help me. Thank you.

I copy the exact same code as yours, but if shows run time error '424' object require.

Hi Greg, Thanks a lot for making this video. But I am facing a problem whenever my range is more than 45 rows. It seems like the function is copying the range in proper format till 45 rows. After that it is not working properly.Do you have any solution for that?

First this vidoe solve the problem of mine half way. May I know how do I send to multiple emails with table in body?

Thank you for this! Trying to figure out how to send emails thru excel vba containing static data but to a certain group that is static and could change, any idea??

RangetoHTML Function link not working

Hi,that was helpful. But while getting the data from Excel to outlook,the excel table is not appropriate,->the bottom line of the table is not coming in outlook.need to expand it manually,is there any way to rectify that....

I get the compile error: Sub or Function not defined... This is due to the function RangetoHTML(pop)... Can anyone help me out

Is there a way to trigger this email from a Button in another part of the spreadsheet? Currently unless I'm in the sheet with the data, it will not send. It just bugs.

HTMLbody is not working in my outlook 16 client

Hey greg, What i want to use filter and send mail to each recipient based on the filter till the last recipient?

Hi bro i need your help

Hey i have one doubt i want to send excel first row and one another row which will change according to the person name is there any code to specify that

do you have something that works with office 365

Hey great tutorial, very helpful. My Sheet consists of a Query with a Join out of 2 seperate Excel Sheets. I have a filter in this query for example filtering a country but when I want to send the E-Mail there is no filtering in the email. Can you help me fix this problem? I need to send a report to 10 countries for example and each email needs to have different receiver and different table based on the same data but with different filters. Thank you

Hello, What if the table starts from somewhere in between not from A1 cell. Then in this case what to do ?

Hi, this code was very helpful! Only hiccup was when I ran it, the formatting did not copy to the email, can you help with this?

Hey! This came really handy. Thanks for the video. I have one question - Is it possible to copy a specific range from a pivot table where the data I want starts from A4 (it's a dynamic range) and the rows above it are 2 report filters and one blank row. I tried the below code count_row = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlDown))) count_col = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlToRight))) Set Pmtstat = Sheets("PIVOT").Range(Cells(4, 1), Cells(count_row, count_col)) I got an output that did not display the last 3 rows of the range that I wanted. Please help!

Hi- what code if you want some of the words in body of email to be in bold letters? thank you

Can instead of a static table what if 8 want to have it pull all lines in a range that meet a criteria? Is that possible?

Thanks this is really helpful… is there a way we can copy specific info from the table and put it in the Subject line? If not can we put a msg box before user hits send an email within same macro?

Hi, new sub here. Your videos are amazing. By any chance, do you have one in google sheets?

Hi Gregg! Question: Is it possible to send an email that has Pdf attachments and a table in the body?

Hi , i find your videos really helpful , i was able to do this macro with your instructions , i had and image in the top row of the table but it did not show in the email , checking your videos i found send email with table as image but i was wondering if that is the only way to have the image shown in the email on the top row of the table or if i can avoid making the table a picture.

SOMEbody write this down, it gives me an error afetr &_ HELPPPPPPPPPP

Hello Greg, This video was great.. Infact I was looking for such video on RU-vid since last 2 days.. Finally I found yours's the correct one. However I had one query, how to create space between the two strings? Cuz when I tried it from my end, the str2 is touching the table which I had. How can we create a space between str2 and the table which is inserted? Thanks

Thank you very much, it works! Do you know how to send the email without having to use the ".Display" code? I'm trying to send it replacing it with ".Send" but it doesn't work.

Hi is it possible to use the same code but send only a specific row like say from C5 to D8 which has 3 rows in it and columns of 2 ? Is that possible

Glad, I found this! Thank you

Hi Greg! This video is amazing, but I do have a problem with the macro: when I launch it, a compile error related to "RangetoHTML(pop)" pops up. It says that I have to define the range! How can I fix?

Hey,is it possible to send emails if there is conditions! Exp: if range ("A4")= "true" then Range ("b4:f6"). select Selection.copy So the .body="good morning" For the above code it's working fine if I change the condition to false it's showing the same good morning I want change that to good evening if range ("A4")= "false" then Range ("b4:f6"). select Selection.copy .body= "good evening"

This is great thank you! I have a question, I would like to do this with if and statements. I.e if column A has a certain value in column B then include in the email.

what if i have two mire tables each in a diferent sheet but all three tables need to go on the same email? HELP CRYING

you're a pretty dope dude

Hey , If I want to select particular columns to attach..Where should I have to change..could you please guide me

thank you for this tutorial.. I really appreciate it

msg.body = sh.Range("D" & i).Value how to give range in this .. range with variable

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

Hi, I get project not found error. It highlights me the Environ$("temp"). Do you have any suggestion? Thanks

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

If you please, this content didn't work for me and I don't know why it might help

Thanks! It works! U solved my problem!

Hi, I have data in 2 sheets (names as Day 1 & Day 2) in tabular format. day 1 sheet has certain details of X & Y person in 2 rows. day 2 sheet has other data of person X, Y, Z. If i want to send X person details from both sheets via email. How can this be done. In same way i want to send mails for each person listed in both the sheets with their data.

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

Thanks! This worked for me, but how can I turn this into a command button that I can press on the excel sheet that will initiate the email instead of pressing green arrow in the VBA menu?

Greg, your videos are super helpful, and I definitely appreciate them! I am having an issue with the not all the cells ending up in the email. Is there a way to set a defined area, like from A1 to F20?

Hi! Can you help me fix my error? I am using Office 365 and tried to replicate the formula you created. I am having Compile Error: Sub or Function not defined and it highlighted RangetoHTML(pop) in the line of str1 & RangetoHTML(pop) & str 2. How do i make it work?

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

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

Hi Greg, Thank you for the great video. When I run this code, I receive "Save" prompts for the Temp Workbook. I've tried the usual Application.Display Alerts = False and then after Application.Display Alerts = True method. However, I'm still having the same issue of a new sheet each time opening up. Any idea why this could be happening or the solution to this? Thank you, I await your reply.

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

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 lot!!!!!!!!!!!!

Firstly massive thank you for sharing the code but could you add the code into the description text to save me having to pause, type, pause, type,delete retype ? Saves my poor old eyes :( - not sure if I have miss typed something I must be doing something wrong as I am seeing an error in the :- .HTMLBody = str1 & RangetoHTML(pop) & str2 & .HTMLBody the debug stops on the "RangetoHTML(pop)" text A error message shows "compile error expected variable or procedure not module

Good effort, kindly make the coding txt words bigger as i am unable to see it clearly.

Where did you get the RangetoHTML(pop)?

pls help me i want tis for all my excel i.e without specifying the doc name is it possible

Great explanation. Greetings from Russia ✌

Can RangeToHTML function, don't copy and paste the hidden column. This is because I noticed when I run the function it will also send the hidden column in the email body Thank you.

Superrrrrbbbbbbbbbbbbbbbbbbbbbb

It's sad to know this doesn't work in a Macbook Air excel :(

Hi, just wondering if in one of the strings that i use for the email body, is a value of a cell; am I able to have the value with the format in the email body as well? Such as the underline/bold etc.

Can we send all employee performance data of the email body in the form of a picture with its name and performance number

amazing

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

Very cool video. My question would be how to have the email setup section To: Cc: Bcc: Subject: Instead of simple strings in quotes, how do you reference cells with the data? For example a cell with a string of email addresses separated by semicolon?

This channel is wildly helpful. Thank you.

Hi Greg, great video! I have a question.. i have to send out 50+ emails and I need to include a table in that email but the data in the table has to be filtered with data that can only be shared with that person. How can I adjust this code to do that?

How do i add multiple sheets not just one and more emails addresses

Do i have to start at cell A1 ? I am trying to go from cell I16 to K17