Excel VBA Macro: Paste Range (Table) As Image In Email Body
Excel VBA Macro: Paste Range (Table) As Image In Email Body. In this video, we go over how to automatically send an email with a table as a picture in the email message. We select a range of cells to create our table, copy and paste the range as an image, cut and then paste that image in our email body.
Code (KZread doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):
Sub send_email_with_table_as_pic()
Dim OutApp As Object
Dim OutMail As Object
Dim table As Range
Dim pic As Picture
Dim ws As Worksheet
Dim wordDoc
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'grab table, convert to image, and cut
Set ws = ThisWorkbook.Sheets("Population Data")
Set table = ws.Range("A1:C9")
ws.Activate
table.Copy
Set pic = ws.Pictures.Paste
pic.Cut
'create email message
On Error Resume Next
With OutMail
.to = "team@abc.com"
.CC = ""
.BCC = ""
.Subject = "Country Population Data " & Format(Date, "mm-dd-yy")
.Display
Set wordDoc = OutMail.GetInspector.WordEditor
With wordDoc.Range
.PasteandFormat wdChartPicture
.insertParagraphAfter
.insertParagraphAfter
.InsertAfter "Thank you,"
.insertParagraphAfter
.InsertAfter "Greg"
End With
.HTMLBody = "LT BODY style = font-size:11pt; font-family:Calibri GT" & _
"Hi Team, LTpGT Please see table below: LTpGT" & .HTMLBody
End With
On Error GoTo 0
Set OutApp = Nothing
Set OutMail = Nothing
End Sub
Data used in this video:
gsociology.ica...
#ExcelVBA #ExcelMacro
Пікірлер: 112
Thanks! Very helpful! I wanted to copy a table and paste the table in an email. I used the same code with minor changes. Code below for reference: del -->Set pic = ws.Pictures.Paste del -->pic.Cut change this line---> .PasteandFormat wdChartPicture to ---> .Paste
@greggowaffles
5 ай бұрын
So glad you figured it out! Thank you so much!!!
Thank you Sir! I spent 3 hours trying to write a code in VBA to send an email with embedded image in the email body, but external parties to my organization could not view the image. Now I came across your simple method and it works. Man I like the internet, thank you !!!
@greggowaffles
Жыл бұрын
So happy to hear that!! No problem!!
Thks, Saved my day!!!!! Great Job 👏
Been searching the web for a working code and yours is the only one that worked.
@greggowaffles
Жыл бұрын
Glad to hear that!!
This code really saved me! In conjunction with your other send email video as well. Keep up the good work, hope to see more from you soon!
@greggowaffles
2 жыл бұрын
Thanks for that! It really means a lot!!
Great Video! By the way how to adjust the pic size that we have been copy and cut in outlook?
Thanks you 😊. What about to show the default signature in outlook?
First time I've seen a video of yours, it was quite helpful. Thank you, Greg.
@greggowaffles
2 жыл бұрын
You’re welcome! Thank you for the feedback.
Loving your videos man! Cannot believe you don't have more subs! Keep it up.. you're a great teacher and it is so SO helpful to have you talk through what you are doing in each video! Thanks so much for your efforts in making them
@greggowaffles
2 жыл бұрын
Really appreciate that! Thank you so much!!
Thanks so much for this video!! You made my day!! How do I add another table from a different sheet? 🤔
I just fund a small problem: If you want to display the email, works fine, but, if you do not want to open the email, suppose you want the email to go out at the click of a button, the problem is that the picture will not be copied to the email, and the email will go out blank..
Just subscribed love the way you walk us through your codes in simple terms. Can you do a video on how to copy and paste a filtered dynamic table with hidden columns as an image into the email html body? Your help will be much appreciated.
How would we go about copying multiple ranges as images within the same email body?
This video made my entire day! Can you show how you would resize the chart?
@greggowaffles
Жыл бұрын
absolutely! apologies for the delay. hope this video helps: kzread.info/dash/bejne/f3mdk89ymJyyZZs.html
much appreciated, exactly what I was looking for, Thanks
@greggowaffles
2 жыл бұрын
Awesome! Thanks for watching!!
Hey greg, congrats for your videos. Its possible to resize the Image of the Range (table) in the email body? Thanks
@pessimistic5579
10 ай бұрын
simply resize the table being copied in excel itself.
Thank you VERY MUCH! 👍
@greggowaffles
Жыл бұрын
No problem! Thank you for watching!!
What if there are 2 table pictures that we need to attached from 2 different tabs? Thank you for your help in advance.
Does anyone know why I receive run-time error 1004: Microsoft Excel cannot paste the data. at the following line: Set pic = ws.Pictures.Paste? If I press again on running the code it prepares the email, but at first it stucks always.
thank you !! your code help me alot !!
@greggowaffles
Жыл бұрын
No problem! So glad to hear that!!
Thank you for the tutorial! May i ask how to loop the function again to insert another table ?
@greggowaffles
2 жыл бұрын
no prob! thanks for watching!! im still looking into looping this code, but i have another video that essentially does the same thing. its just that the tables arent converted into images before being added to the email body: kzread.info/dash/bejne/a6Vsybx_YLi1pJs.html
Hey! Good work. I have a question. When I run the macro The picture is pasted at the begining of the email and I need it at the bottom. How can i do it?
You man are epic!
@greggowaffles
2 жыл бұрын
Thank you!
Hi Greg, This works almost perfectly! Except the ".HTML" and ."PasteandFormat wdChartPicture" both clobber everything else previously entered. Any Ideas? The
This is great and works lovely, but i found that the code breaks when you replace .Display with .Send in the outmail with block. i want the email to send automatically without the outlook window popping up. is there a workaround?
amazing, bro! thanks
Is there a solution for doing this twice in one email?
Thank you a lot, it will help a lot :D
@greggowaffles
Жыл бұрын
I hope it does help! Thank you for watching!!
Hello sir. This absolutely works! Thank you! Although, when pasted in outlook, the image version of my selected range becomes small. Is there any way I could set the size of the image in outlook using vba? Thank you!
@timothyclayton2573
2 жыл бұрын
i noticed the same problem.
What if i have multiple work sheets and i only want to grab the raange of the work sheet that i am currently on and convert it to image?
Very very new to VBA, learning a lot from these videos.I keep getting a compile error: Method or data member not found? Seems to stop at Pic.Cut. Suggestions anyone?
Great video! Can you also make a video that shows how to pull distance info between 2 locations written in the cell from Bing Maps API into excel?
Great video! Is it possible to copy and paste multiple tables as picture format? I have watched your other video that uses RangetoHTML to paste tables, but unfortunately this work poorly when pasting PivotTables, it struggles with the formatting. The method of pasting the ranged as a picture solves this problem, now I just need to be able to paste multiple ranges into the same email. Thanks for the help so far!
great video, thank you.:)
@greggowaffles
2 жыл бұрын
Thanks for watching!!
Hey Greg Thank you so much for sharing these VBA tutorials. Helped me a lot to create one of my own. One help from you Instead of pasting in email, how can I export this image to a path
@greggowaffles
2 жыл бұрын
glad to hear that! thanks for watching! if you google SaveRangeAsPicture you should find some good stuff. ill make a video on this in the near future
@tekygenie
2 жыл бұрын
@@greggowaffles thank you again!!
@greggowaffles
2 жыл бұрын
No problem!
This is fantastic. I was struggling with how to do exactly this. Found other code but that code was WAY too complicated. Only thing I need to figure out is how to get my default signature to populate. If you have a suggestion that would be great. Gray.
@jaysekhon8014
2 жыл бұрын
Open up Outlook. Go to File, Options, Mail, then click Editor Options under Compose Messages. Then Click Advanced, scroll down to the Insert/Paste pictures as: and select Square. See if that fixes your problem (:
You are amazing!!!!
@greggowaffles
Жыл бұрын
Thank you!!
@greggowaffles how to Paste Range (Table) in email using different excel. I have multiple excel workbook, which have 1 table per customer. Is it possible to copy and paste from the workbook to the email body as range/table format?
Great Tutorial, Greg!!! thank you so much :) May I ask how would you increase the size of the pic on the email?
@greggowaffles
2 жыл бұрын
No prob! Thanks for watching! Hope this video helps: kzread.info/dash/bejne/k6eu0LCmp7KnhJs.html
@nathanwhite7268
2 жыл бұрын
@@greggowaffles hey this video is great. How can I increase the size of this pic in the body of the email (where you copy and cut it from excel). I have looked at your other video where you do this but with a png that is saved in a file location, however I can't seem to get it to work with the excel sheet image.
@denzelhd
2 жыл бұрын
asking the same qn! The vid is resizing png, doesn’t seem to work for excel image
@gurpreetkaur-gd8np
2 жыл бұрын
Same question! issue in the size of image
@jasmadsen
2 жыл бұрын
Has anybody ever found a solution to realize the table image in the body of the email?
Very nice video . I used the code but my image is not in the new email automatically . I do the Strg v to get the image in the email. What is my mistake ?
Thanks Greg. It works awesomely fine. Question though. Can we resize the attached range pic via "with pic"?
@greggowaffles
Жыл бұрын
yes! apologies for the delay. hope this video helps: kzread.info/dash/bejne/f3mdk89ymJyyZZs.html
thank you!🥰
@greggowaffles
Жыл бұрын
You’re welcome!! Thank you for watching!!
Hi, great tutorial! However, our version is probably different, I can't define something as Picture in VBA... I tried Image and Graphic instead, but it give me a type mistmatch error. Anyone have an idea what can I use instead?
@denzelhd
2 жыл бұрын
Same question here! Do let us know Greg!
This is a great example however I when I adapted my code to use the picture appears after my signature. My code previously would generate a pdf and then create the HTMLbody to have some text and my signature. Any ideas?
Hi Greg! What if I need to Paste Range AS Image in Mail Body but this range is not a Tablet but a mix of graphic?
what is the code to paste range as images for two tables in the same sheet? thanks
How to paste multiple range as picture ?
just awesome!
@greggowaffles
Жыл бұрын
Thank you!!
I tried the exact same codes but it only creates an email with To: and Subject: Why is the body blank?
do you have ways how to add a link to the image pasted in the email body?
Hi, I need to copy 2 differents grafics in 2 differents sheets, how can I do?
This pastes the image when you display the picture without sending but when you change .display to .send, it sends it without the image. Has anyone figure this one out yet?
Great video,but it doesn't paste the Pic...😅
Code debug at "set pic=ws.pictures.paste" with error Run time 1004. Can everyone help me?
This is amazing, Greg! Just one query, the image is overriding my Signature. Is there a way to fix it? Thanks
@jaysekhon8014
2 жыл бұрын
Open up Outlook. Go to File, Options, Mail, then click Editor Options under Compose Messages. Then Click Advanced, scroll down to the Insert/Paste pictures as: and select Square. See if that fixes your problem (:
This works great Except the pasted image is very small. Too small to read. Is there an easy way to enlarge this?
@greggowaffles
Жыл бұрын
yes! apologies for the delay. hope this video helps: kzread.info/dash/bejne/f3mdk89ymJyyZZs.html
I keep getting Run-time error '429': ActiveX component can't create object!!! Do you have any advice, I've been breaking my head trying to figure this out.
Can I paste the image into a different range?
Hi, how would you increase the size of the pic on the email?
Is there any way I can paste a range of cells that are non contiguous?
Man, thanks for the video. I’m having and issue and I don’t know how to solve. It pastes nothing on the email body when I run the code. But if I press Ctrl+V I can paste the range on email body. How can I solve this?
@jefflunsford1331
Жыл бұрын
Same here….
@JoaoVaralta
Жыл бұрын
@@jefflunsford1331 man I wish I could help you but I don’t remember how I solved it. Try copying and pasting your script on chat gpt explaining you error. Maybe it could help you.
newbie here, how to run this on a button?
pic.cut gives an error, has anyone figured it out?
Thanks!
@greggowaffles
2 жыл бұрын
Thank you so much! I really appreciate it!!
Can you automatically press send then move to the next worksheet?
@seanharper1314
2 жыл бұрын
And do this until you get to the last worksheet
Hi Greg, How can we add multiple tables to outlook?
@greggowaffles
Жыл бұрын
Hope this helps! kzread.info/dash/bejne/a6Vsybx_YLi1pJs.html
@asgerlarsen2083
Жыл бұрын
@@greggowaffles Is it possible to add multiple tables, but pasted as pictures?
Hi i've got an error 424 on line Set OutMail = OutApp.CreateItem(0)
@Alexkip2008
2 жыл бұрын
sorry my fault .. it works :)
This code does not work. It either uses methods that don't exist anymore or variables not declared.
when I tried to use this line of code at work, I had pic.cut launch a massive stack of code that took a few minutes to run. Does anyone else have this same problem?
@greggowaffles Great video, easy to follow. Do you know how to add the information as table instead of image?. Also, how can I add data from specific cell into the subject line?. This is the code that I have: Sub Send() Dim OutApp As Object Dim OutMail As Object Dim table As Range Dim pic As Picture Dim ws As Worksheet Dim wordDoc Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) 'grab table, convert to image, and cut Set ws = ThisWorkbook.Sheets("Form") Set table = ws.Range("B4:C21") ws.Activate table.Copy Set pic = ws.Pictures.Paste pic.Cut 'create email message On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Credit Review for" & "" & Range("C4") & "" & Range("C6") .Display Set wordDoc = OutMail.GetInspector.WordEditor With wordDoc.Range .PasteandFormat wdChartPicture .insertParagraphAfter .insertParagraphAfter .InsertAfter "Regards," .insertParagraphAfter End With .HTMLBody = "Hello Risk Management, Could you please run a credit review of this location? " & .HTMLBody End With On Error GoTo 0 Set OutApp = Nothing Set OutMail = Nothing End Sub
When I run the code, I get a Run-time error '9': "Subscript out of range" When I select debug, it highlights "Set ws = ThisWorkbook.Sheets("EOSReport")" Any insight? Thank you!!
@greggowaffles
Жыл бұрын
Maybe there’s a space “ “ in the tab name