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
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!!!!!!!!!!!!
Do you know why the numbers in the tables pasted in outlook don't have any space between them and the border of the table? If you check your own video the space between the numbers and the border of the table in Excel is much higher than that in Outlook
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
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.
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.
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.
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.
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?
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??
thanks. you can filter, and copy the visible values to another sheet, and then use that sheet to pass through the RangeToHTML function. I show how to copy filtered values to another sheet in this video: ua-cam.com/video/dEAozqfZFHE/v-deo.html
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.
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.
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??
I love the content. Mine did not work although i follow the instructions. Not sure why. When i run it, error message " ActiveX Component Can't create object" Pops up.
Hi, just a FYI the count_row is now variant instead of integer. str1 as well. Always add the variable type for each variable bacause this could lead to weird error's in some cases
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 Greg, This video was great.. Infact I was looking for such video on UA-cam 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
How do you reference an email recipient based on the selected cell on the work sheet? I really love how clean this tutorial is, I happen to work in a big company I am working on a form where I can email multiple people or CC related departments to it.
@@miggi4665 so you want to copy any new entries on to a new sheet for tracking? I think this should help: ua-cam.com/video/FQyfiKTBKIg/v-deo.html it copies and pastes filtered data on to a new sheet, and if there's data on that sheet already, it will paste the new data underneath the existing data
If you know where the tables are you can just set range = Sheets("Sheet Name").Range(Cells(a,b),Cells(c,d)) where a and b are the location of the upper leftmost cell and c and d are the location of the bottom rightmost cell. So set = range = Sheets("Raw Data").Range(Cells(3,2),Cells(10,8)) would be a range on the Raw Data tab that spans B3 to H10
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?
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?
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?
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,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....
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.
This is awesome. What if we had one more table in another sheet and it has to be below the first table in mail body. What are the changes I should make in code?
I really appreciate this teaching and it really helped me but only one thing bothers me 😢, I need to know what I should write if I need to send only part of the table in my email ? Let's say the range is from A1 to K11 but I need to send from A1 to H and down ?
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?
Thanks! Say the cell you want to reference is on a sheet named "Contacts" and the name is on cell B4. You would do .to = ThisWorkbook.Sheets("Contacts").Cells(4,2).Text. I go over it in more detail in this video: ua-cam.com/video/dMumM47cm2s/v-deo.html
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!
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
Hi Greg, thanks so much for this video. It looks like if I use the CountA for my count_row and count_column it does not include the empty cells (which makes sense since it's an excel function). How do I select the entire table even if some cells are empty? Thank you!
hey! no problem! you can use count_row = Activesheet.Cells(Rows.count, "A").End(xlUp).Row to count from the bottom up. as you long you dont have any blank headers, you should be fine. but you can use (xltoLeft) if you do have blank headers
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.
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! 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?
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 :)
thanks! you can have a pop1 and pop2 with different ranges, and then in the .HTML line have RangetoHTML(pop1) & "" & RangetoHTML(pop2). apologies for the late reply
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.
Hi. I know that can use and before the value and and after the value in your code, in order to underline/bold that value in the email body. I've never been able to get underline/bold formatting directly from the cell, unless I put the HTML code directly in the cell
another question I have..If I want to add the default signature, where should i add the "signature" variable? since it is a HTML body I;ve added it before the final .HTMLBody tag, but it is not loaded.. And second: if you have more than one email addresses, can you add a code to use the default(or second) email address when sending emails? Great work!!
I've managed to fix the 2nd issue with .SentOnBehalfOfName =" mycompany@address.com" , but i still can't get the signature working in this .bodyHTML... please advise. thank you
@@greggowaffles if you don't include a text in the body to act as signature, the script it wouln't generate any.. is there a way to "call" the default outlook singature by VBA?
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?
thanks! you can combine code from these these videos to make that work: (1) send bulk emails: ua-cam.com/video/7Chuqz5M65U/v-deo.html (2) autofilter based on cell value and copy to new sheet: ua-cam.com/video/dEAozqfZFHE/v-deo.html you can have your macro go through each line in your list (1) and reference a cell used to filter and copy that data to a new sheet (2) to be used as a table in (1); repeating the process as you move down the list of 50+ entries
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"?
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.
@@mehulgorasia try removing the Application.Display Alerts = True, and only use Application.Display Alerts = False at the beginning of your code. let me know if that does anything
@@greggowaffles tried that didn’t work either. For only one of my colleagues, the exact same code works on their excel (with no save prompts) and straight produces the email. However, our excel settings seem the same. Any thoughts?
Hi this is fantastic, very helpful thank you so much for your hard work in helping us Just a questions, when I use it on a pivot table its taking the range from the top cell to the beginning of the table, So Im doing (count_row + count_row * 2) and (count_col+count_col * 2) then its showing the full table Would you know a possible reason why it does that, it takes the range as mentioned in a normal table though
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.
hi! you can select individual columns by hiding the columns you dont want, and then copying and pasting the visible cells to another sheet, and then set pop = to the that range
@@greggowaffles Hi, Greg. I'm having the same issue. I have multiple columns, but it's stopping short when pasting into the email. i.e pasting 5 columns worth of data but there are 11 columns total.
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.
I looked online and it says this happens when you've "created a function or other routine called format. Put the cursor on the word Format and press Shift+F2". Hope that helps. Its from this site stackoverflow.com/questions/43096639/rangetohtml-no-longer-working
here you go! 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
Dear Friend, It is such an excellent video. However could you share how to attach only specific data from table instead whole data. Like in your case if I want to show only Asia Data in email body. Looking forward to hear from you.
You use the code in this video to autofilter and copy to a new sheet and then use the range from that new sheet for your email: ua-cam.com/video/dEAozqfZFHE/v-deo.html
@@greggowaffles Dear Greg, The scenario is Let say we have three columns of table data and one of the column holds value(In progress and Completed and Not Started). I want to include the data for which as values completed and In progress to email body using Excel macro could you please assist with sample code am unable to get this out failing with errors.
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"
hey, you can have a text variable called greeting and have .body = greeting. in your if statement, make greeting = "good morning" or "good evening", so that when you assign it to .body, it will change
Hi Greg, thank you so much for the video! I currently get “Compile Error: Automation Type not Supported in Visual Basic”. Do you know how do I work around that?
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
This is what UA-cam is made for. Casual but extremely informative. Just like a buddy sitting with you. Your vids rock, man. Much appreciated.
Appreciate the feedback. Thank you!
@greggowaffles can you post the function for this? Looks like he removed all of his code from his website back in October
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!
No problem! So glad to hear that!!
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!!!
I really appreciate that feedback! Thank you so much!!
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!!!!!!!!!!!!
So glad to hear that!!
Do you know why the numbers in the tables pasted in outlook don't have any space between them and the border of the table?
If you check your own video the space between the numbers and the border of the table in Excel is much higher than that in Outlook
This channel is wildly helpful. Thank you.
So happy to hear that! Appreciate the feedback!!
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
You have made my work so easy! Thank you so much for sharing this video with your awesome knowledge. God bless you!
Happy to help!!
@honeydeeflores6812 Do you have the code you can share with me?
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
No problem! So glad to hear that!!
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.
Thank you! I'm so glad to hear that! I'm looking forward to providing more content for you
This is Helpful. Can you help with a code which can be used to send multiple emails with different tables. Appreciate your help.
thanks! yes, I'll make a video on this soon
This was very helpful Greg ... thanks a lot !!
No prob! So glad to hear that!! Thanks for watching!
Great explanation. Greetings from Russia ✌
Thank you for watching!!! ✌️
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.
Thank you so much! thanks to your video I will be saving a lot of my time
So glad to hear that! Thanks for watching!!
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.
Appreciate you to prepare this amazing tutorial.
Thank you so much!!
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.
Did you ever find a solution to this problem? I have the same issue.
@@Rolod Maybe.. create a new dynamic "" manually with "FOR" and "Count Rows / Cols"
thank you for this tutorial.. I really appreciate it
Thanks! It works! U solved my problem!
So glad to hear that!!
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?
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??
thanks. you can filter, and copy the visible values to another sheet, and then use that sheet to pass through the RangeToHTML function. I show how to copy filtered values to another sheet in this video: ua-cam.com/video/dEAozqfZFHE/v-deo.html
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.
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.
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??
Great Video, I have data that starts in Cell A18, do I need to make any changes to the code that you have used?
Thanks! Yeah, for the count_row and count_col lines, you’ll need to change where it says “A1” to “A18”
Thank you so much... Found exactly as I needed 👍🏻
Glad to hear that! Thank you for watching!!
Send Email With Multiple Tables In Body (Static and Dynamic Ranges): ua-cam.com/video/9q8gZF0UOt8/v-deo.html
I love the content. Mine did not work although i follow the instructions. Not sure why. When i run it, error message " ActiveX Component Can't create object" Pops up.
Hi this really worked out well,but while the excel data is copied to the outlook, the table borders of the excel data is not appropriate...
error RangetoHTML(pop) not defined - excel 2013
Hi, just a FYI the count_row is now variant instead of integer. str1 as well. Always add the variable type for each variable bacause this could lead to weird error's in some cases
Thank you
thsi is what i was trying to buld
No problem! Thank you for watching!
Thank you Greg, This will just save my day :)
Keep up the good work :)
so glad to hear that! thank you so much!!
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 Greg, This video was great.. Infact I was looking for such video on UA-cam 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
Glad to hear that! You can just add “ “ & in between the & and str2 to add a space between str2 and the table. Hope that helps!!
Glad, I found this! Thank you
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?
How do you reference an email recipient based on the selected cell on the work sheet? I really love how clean this tutorial is, I happen to work in a big company I am working on a form where I can email multiple people or CC related departments to it.
Thanks! You can use this video for that: ua-cam.com/video/dMumM47cm2s/v-deo.html it sends emails to recipients based on information in a cell
@@greggowaffles You are fantastic!
@@miggi4665 so you want to copy any new entries on to a new sheet for tracking? I think this should help: ua-cam.com/video/FQyfiKTBKIg/v-deo.html it copies and pastes filtered data on to a new sheet, and if there's data on that sheet already, it will paste the new data underneath the existing data
@@greggowaffles Please, could tell me how can I specify the col and row on the code for the table to be included on the email?
If you know where the tables are you can just set range = Sheets("Sheet Name").Range(Cells(a,b),Cells(c,d)) where a and b are the location of the upper leftmost cell and c and d are the location of the bottom rightmost cell. So set = range = Sheets("Raw Data").Range(Cells(3,2),Cells(10,8)) would be a range on the Raw Data tab that spans B3 to H10
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?
no problem! im not seeing that happen when i try to recreate that scenario. what format differences are you seeing?
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?
i, you can change:
Set Pop = Sheets("Planilha padrao").Range(Cells(1, 1), Cells(20, 6))
Hey greg,
What i want to use filter and send mail to each recipient based on the filter till the last recipient?
You helped me a lot, you are a genius, thanks man !!
So glad to hear that! No problem!
Very sweet tutorial
Thanks!
Does the Range to HTML function works in Excel 2019?
My Greg is back!! 😂
🙌🙌🙌
@@greggowaffles Yup!!!
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?
You only need to put a figure on the excel and rigth click and "assign macro"
That is all
@@EdgarPachecoSolano Thanks!
First this vidoe solve the problem of mine half way. May I know how do I send to multiple emails with table in body?
Do you have a file with this VBA code that you could share with me, please? Ron took down his code.
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.
glad to hear that! there are couple workarounds here: stackoverflow.com/questions/41724452/send-not-working-vba-but-display-is
hope it helps!
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....
Thank you so much for this. Helps a lot!
No problem! So glad to hear that!!
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, 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?
This is awesome. What if we had one more table in another sheet and it has to be below the first table in mail body. What are the changes I should make in code?
thanks! you can have a pop1 and pop2 with different ranges, and then in the .HTML line have RangetoHTML(pop1) & "" & RangetoHTML(pop2)
Thank you gregoowaffles. It is really helpful 😊
No problem!
Hi gregoowaffles after the table gets copied on the mail body, if I do autofit to contents it does not work properly. Is there any solution for that?
Google RangetoHTMLFlexWidth. That code should help
I really appreciate this teaching and it really helped me but only one thing bothers me 😢, I need to know what I should write if I need to send only part of the table in my email ? Let's say the range is from A1 to K11 but I need to send from A1 to H and down ?
You would just add “- 3” to the column count line. Hope that helps!!
@@greggowaffles you mean : after .End(xlToRight))) ? I tried and it's not working 😢
Ohhhh forget it, I found where... thank you so much
No prob!
Terrificc...thanks a ton!!! 🙏🙏
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?
Thanks! Say the cell you want to reference is on a sheet named "Contacts" and the name is on cell B4. You would do .to = ThisWorkbook.Sheets("Contacts").Cells(4,2).Text. I go over it in more detail in this video: ua-cam.com/video/dMumM47cm2s/v-deo.html
@@greggowaffles Thanks a bunch.
I'll tune in.
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! just add "+3" to count_row and your problem will be solved :-)
count_row = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlDown))) + 3
@@greggowaffles It worked! Thanks :)
@@prajinoo no prob!
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
Hi Greg, thanks so much for this video. It looks like if I use the CountA for my count_row and count_column it does not include the empty cells (which makes sense since it's an excel function). How do I select the entire table even if some cells are empty? Thank you!
hey! no problem! you can use count_row = Activesheet.Cells(Rows.count, "A").End(xlUp).Row to count from the bottom up. as you long you dont have any blank headers, you should be fine. but you can use (xltoLeft) if you do have blank headers
@@greggowaffles thanks so much!!
Hello, does it work with office 365 plz? No error when running the code but no email generated
Hello, I want to export the selected area as jpeg or png in Excel. Can you help me. Thank you.
yoyu are a productivity wizard
Thank you!!
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.
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! 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?
Hello,
What if the table starts from somewhere in between not from A1 cell.
Then in this case what to do ?
Awesome video
Thanks for watching!!
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 :)
I copy the exact same code as yours, but if shows run time error '424' object require.
Great technique!
can you help to fetch more than two ranges in the sheet?
thanks! you can have a pop1 and pop2 with different ranges, and then in the .HTML line have RangetoHTML(pop1) & "" & RangetoHTML(pop2). apologies for the late reply
Thanks a ton
Thanks but it is not working in my excel. it is 2019. do you have updated code?
Hello,
What if the table starts from somewhere in between not from A1 cell.
Then in this case what to do ?
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.
Hi. I know that can use and before the value and and after the value in your code, in order to underline/bold that value in the email body. I've never been able to get underline/bold formatting directly from the cell, unless I put the HTML code directly in the cell
another question I have..If I want to add the default signature, where should i add the "signature" variable? since it is a HTML body I;ve added it before the final .HTMLBody tag, but it is not loaded..
And second: if you have more than one email addresses, can you add a code to use the default(or second) email address when sending emails? Great work!!
I've managed to fix the 2nd issue with .SentOnBehalfOfName =" mycompany@address.com" , but i still can't get the signature working in this .bodyHTML... please advise. thank you
I've never added a signature variable in my code. I always have my signature come directly from Outlook
@@greggowaffles if you don't include a text in the body to act as signature, the script it wouln't generate any.. is there a way to "call" the default outlook singature by VBA?
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?
thanks! you can combine code from these these videos to make that work:
(1) send bulk emails: ua-cam.com/video/7Chuqz5M65U/v-deo.html
(2) autofilter based on cell value and copy to new sheet: ua-cam.com/video/dEAozqfZFHE/v-deo.html
you can have your macro go through each line in your list (1) and reference a cell used to filter and copy that data to a new sheet (2) to be used as a table in (1); repeating the process as you move down the list of 50+ entries
Hi- what code if you want some of the words in body of email to be in bold letters? thank you
You can check this video out: ua-cam.com/video/7yGBjNBydEY/v-deo.html
Thank you ❤️ this is a big help 🤗
@@ronniel.jacinto no problem! Glad to hear that!! ☺️
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"?
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.
thanks! are you using mac or pc?
Hi Greg, thanks for the reply. PC
@@mehulgorasia try removing the Application.Display Alerts = True, and only use Application.Display Alerts = False at the beginning of your code. let me know if that does anything
@@greggowaffles tried that didn’t work either. For only one of my colleagues, the exact same code works on their excel (with no save prompts) and straight produces the email. However, our excel settings seem the same. Any thoughts?
If you have anything that starts with "on error..." in your code, comment that out and see if the code breaks. Thats probably where the answer is
Thanks Greg
No prob!
Hi this is fantastic, very helpful thank you so much for your hard work in helping us
Just a questions, when I use it on a pivot table its taking the range from the top cell to the beginning of the table,
So Im doing (count_row + count_row * 2) and (count_col+count_col * 2)
then its showing the full table
Would you know a possible reason why it does that, it takes the range as mentioned in a normal table though
Thanks! Yeah, that's weird. Not sure why thats happening. I'd have to the file/code you're working with to test it out
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
Hi! Yes, you can specify that in the code. You can set row count and column count equal to actual numbers
Any alternative links? Someone pissed off Ron and he scrubbed his site
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.
You are awesome bro!
appreciate the love brotha! 😎
Hey , If I want to select particular columns to attach..Where should I have to change..could you please guide me
hi! you can select individual columns by hiding the columns you dont want, and then copying and pasting the visible cells to another sheet, and then set pop = to the that range
Superb it’s work 👌🏻
Thank you!
do you have something that works with office 365
great video, but when I run the code it is only sending first 2 columns I have around 10 columns in each row.
thanks! did you ever figure it out?
@@greggowaffles Hi, Greg. I'm having the same issue. I have multiple columns, but it's stopping short when pasting into the email. i.e pasting 5 columns worth of data but there are 11 columns total.
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.
hi. hope this helps: ua-cam.com/video/9q8gZF0UOt8/v-deo.html
How to make this dynamic with To and Cc
Muy bueno! gracias
De nada
Hi, I get project not found error. It highlights me the Environ$("temp"). Do you have any suggestion? Thanks
I looked online and it says this happens when you've "created a function or other routine called format. Put the cursor on the word Format and press Shift+F2". Hope that helps. Its from this site stackoverflow.com/questions/43096639/rangetohtml-no-longer-working
Ron's site is no longer available... can you paste the code somewhere so I can copy it?
here you go!
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
Fantastic....
Thanks!
Dear Friend,
It is such an excellent video. However could you share how to attach only specific data from table instead whole data. Like in your case if I want to show only Asia Data in email body.
Looking forward to hear from you.
Thanks! You can filter the data, copy and paste visible values to another location, and then use that new range when adding to the email
@@greggowaffles Thank you could you help me with sample code plz
You use the code in this video to autofilter and copy to a new sheet and then use the range from that new sheet for your email: ua-cam.com/video/dEAozqfZFHE/v-deo.html
@@greggowaffles
Dear Greg,
The scenario is
Let say we have three columns of table data and one of the column holds value(In progress and Completed and Not Started). I want to include the data for which as values completed and In progress to email body using Excel macro could you please assist with sample code am unable to get this out failing with errors.
Dear Greg,
Kindly assist with this request.. appreciate an sample code.
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"
hey, you can have a text variable called greeting and have .body = greeting. in your if statement, make greeting = "good morning" or "good evening", so that when you assign it to .body, it will change
Where did you get the RangetoHTML(pop)?
Check out the link in the description. The code for the RangetoHTML function is there
Thank you very much
You're welcome!
Can we send all employee performance data of the email body in the form of a picture with its name and performance number
Most definitely
Hi Greg, thank you so much for the video! I currently get “Compile Error: Automation Type not Supported in Visual Basic”. Do you know how do I work around that?
Do i have to start at cell A1 ? I am trying to go from cell I16 to K17
nope. you can start anywhere. i go over it in this video for multiple ranges: ua-cam.com/video/9q8gZF0UOt8/v-deo.html