Excel VBA Macro: Resize Range (Table) Pasted As Image In Email Body
Excel VBA Macro: Resize Range (Table) Pasted As Image In Email Body
💥Subscribe: / @greggowaffles
Code:
Sub send_email_with_table_and_resize()
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:C11")
ws.Activate
table.Copy
Set pic = ws.Pictures.Paste
pic.Select
With Selection
.ShapeRange.LockAspectRatio = msoTrue
'.ShapeRange.Height = 200
'.ShapeRange.Width = 200
End With
pic.Cut
'create email message
On Error Resume Next
With OutMail
.to = "team@123.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 = "＜ BODY style = font-size:11pt; font-family:Arial ＞ " & _
"Hi Team, ＜ p ＞ Please see table below: ＜ p ＞" & .HTMLBody
End With
On Error GoTo 0
Set OutApp = Nothing
Set OutMail = Nothing
End Sub
#ExcelVBA #ExcelMacro
Пікірлер: 33
hello! check out this video if your'e running into issues when using large ranges: kzread.info/dash/bejne/pIWEyNmhpdW_YdY.html
This has proven very useful for a project I'm working on. Could you possibly explain how to add more than one table as separate images into the email body?
Great tutorial on this email + copy image macro... thank you.
@greggowaffles
Жыл бұрын
Thanks for watching!!
Great stuff as per usual!
@greggowaffles
Жыл бұрын
Appreciate you!!
Awesome, thanks for the video!
@greggowaffles
Жыл бұрын
Thanks so much for watching!!
nice !!!!
@greggowaffles
Жыл бұрын
Thanks!!
great tutorial. by the way, it seems like unable to run this when window's lock. will error debug on "Set pic = ws.Pictures.Paste" any possible workaround on that? thanks!
Wondering if you can do a tutorial where you can delete certain outlook email or move outlook email to subfolder using VBA Macro... thank you.
@Kakarot_Son_Goku
Жыл бұрын
Great idea.
@greggowaffles
Жыл бұрын
I’ll look into this! Thanks for sharing this idea!!
I keep running into issues with the Set pic = ws.Pictures.Paste line, gives me Run-time error '1004': Unable to get the Paste property of the Pictures Class. Any ideas? Thanks!
Awsome video! I wonder if there s a posibility to use windows mail not outlook mail? Thank you.
Can you do a tutorial if I wanted to insert multiple pictures into an email with verbiage in between (hoping to reference cells for the verbiage)?
Do you have solution for dynamic range because table keep adding new rows? Appreciate your support in advance 🙏
.Send is not working
Great video helps me a lot. i did run into an issue with an important file of mine, where if my table range is too big (A1:AH37), im not able to resize it bigger than shaperange 500. is there a way around the size limiter? because there does not seem to be a Outlook problem, since im able to manualy resize as big as i want, once in the email body. if not is there a way to attach it to the email instead of the email body?
@HalbeEllgr
Жыл бұрын
Same issue here, can resize within outlook but seems to hit a limit when using the .ShapeRange.Width
@amc_dl
Жыл бұрын
Still having the problem? I'm having the same problem too.
@oromax93
Жыл бұрын
I had the same problem using format name but it worked as i used value. Try: .Pasteandformat Type:=13
@mikeguli7223
8 ай бұрын
What is the syntax you used? I'm trying '.PasteAndFormat Type:=13 wdChartPicture' but it won't let me.@@oromax93
@greggowaffles
3 ай бұрын
apologies for the late reply, but hopefully this video can help yall out!! kzread.info/dash/bejne/pIWEyNmhpdW_YdY.html
Is it posible replace a keyword in email body by the image?
how can i call my default signature in outlook instead of creating it thru an html body?
I Greg. Thank you. How we can add cell values in outlook mail body instead of pasting it as a picture? Please
@greggowaffles
Жыл бұрын
Here you go! Excel VBA Macro: Send Email With Table In Body (Dynamic Range) kzread.info/dash/bejne/ha6jqa2wnavOfs4.html
@nullhas
Жыл бұрын
@@greggowaffles Thank You
@greggowaffles
Жыл бұрын
No problem!
@nullhas
Жыл бұрын
Hi Greg Thank you for your help. In your previous video where you used same table to convert as a picture, I put .send after .display in coding. I could sent the mail, but could not paste table as picture. Please help