Excel VBA Macro: Resize Large Table Ranges After Pasting (As Image) In Email Body

Excel VBA Macro: Resize Large Table Ranges After Pasting (As Image) In Email Body
💥Subscribe: / @greggowaffles
Original Video: • Excel VBA Macro: Resiz...
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
Dim row_count As Long
Dim col_count As Long
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
row_count = ws.Cells(Rows.Count, 1).End(xlUp).Row
col_count = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Set table = ws.Range(Cells(1, 1), Cells(row_count, col_count))
table.Copy
Set pic = ws.Pictures.Paste
pic.Select
' With Selection
' .ShapeRange.LockAspectRatio = msoTrue
' '.ShapeRange.Height = 200
' .ShapeRange.Width = 500
' End With
pic.Cut
'create email message
On Error Resume Next
With OutMail
.to = "[e-mail]"
.CC = ""
.BCC = ""
.Subject = "Country Population Data " & Format(Date, "mm-dd-yy")
.display
Set wordDoc = OutMail.GetInspector.WordEditor
With wordDoc.Range
.pasteandformat wdChartPicture
With wordDoc.inlineshapes(1)
.LockAspectRatio = msoTrue
'.Height = 500
.Width = 2500
End With
.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

[@makubexho]

great solution and coding in tackling the self-inflicted predicament by that poster - the intention of including such large amount of databody from Excel into the email body is beyond me - Do you not find it more appropriate and secure to store the data in an Excel file and attach it in the email for distribution? If I was the recipient of that email with a large table of content in the email body I would be like "WTH.."

[@makubexho]

use outlook and emails for its purposes that are originally designed best for - do not stretch its application to fit some "eye-catching" stunts some people are blindly searching for

[@greggowaffles]

Thanks for the feedback!! Gotta give the people what they want 😎

[@Dexter101x]

Can't VBA use anything other than Outlook? I find it very limiting, since I already have an email manager