Paste contents from multiple Excel sheets into Outlook body

Question

I need to copy contents from two sheets into Outlook mail body.

Sheet 1 contains only text with some merged cells.
Sheet2 contains charts.

My failed approach:

Copy contents from sheet1 and sheet2 into a temp sheet and then copy the complete content from temp sheet to mail body.
This approach messes the complete alignment once the content is pasted into Outlook mail body, even though it looks good in the temp sheet.

Below is the code snippet I use.

Sub copy_graph()
Dim outlookapp, outmail, worddoc As Object

Set outlookapp = CreateObject("outlook.application")
Set outmail = outlookapp.createitem(olmailitem)

outmail.display
Set worddoc = outmail.getinspector.wordeditor

ThisWorkbook.Sheets.Add.Name = "temp_mail"
ThisWorkbook.Worksheets("Tu_Mail").Range("a4:b18").Copy
ThisWorkbook.Worksheets("temp_mail").Range("a1").Select
ActiveSheet.Paste

ThisWorkbook.Worksheets("trend").Range("a1:x93").Copy
ThisWorkbook.Worksheets("temp_mail").Range("a19").Select
ActiveSheet.Paste

ThisWorkbook.Worksheets("temp_mail").Range("a1:x93").Copy
worddoc.Range.PasteExcelTable linkedtoexcel:=False, wordformatting:=False, RTF:=False
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("temp_mail").Delete

End Sub

Is there any way to paste content from two sheets one by one into the mail body instead of combining them into one sheet. i.e paste as two different tables so that the column alignment is intact.

Or

Is there any other better approach for this?

check out this very handy RangeToHTML function. I use it whenever I want to paste Excel ranges into email bodies. — Scott Holtzman
Thanks scott. I tried that, RangeToHTML is a common funtion i see in most of the answers in web. But it fails in my case, it doesnt copy charts. — Vivek
can you copy the chart as a picture and then paste that into the email body? — Scott Holtzman
I reserved that for last option because saving picture degrades the quality of chart thereby spoiling the neatness of my email. — Vivek

Vivek Vivek · Accepted Answer · 2015-11-26T14:51:04

Here is the approach i got working finally

Approach:

Copy sheet1 content to a temp sheet
Copy sheet2 (charts) content to temp sheet as picture.

Now, copying charts as picture was already known to me
I kept ignoring this method earlier as i was not able to export charts to a quality image without distorted text and graph lines.
In this approach I used CopyPicture function and this function is able to get the graph content as it is but in an image.

Below is the final code i have

Sub copy_graph()

Dim rgExp As Range
Set outlookapp = CreateObject("outlook.application")
Set OutMail = outlookapp.createitem(olmailitem)
OutMail.display
Set worddoc = OutMail.getinspector.wordeditor

ThisWorkbook.Sheets.Add
ActiveSheet.Name = "temp"
ThisWorkbook.Worksheets("temp").Range("a:z").Delete
ThisWorkbook.Worksheets("temp").Columns("a:a").ColumnWidth = 25.57
ThisWorkbook.Worksheets("temp").Columns("b:b").ColumnWidth = 89.57

'Copy contents from sheet1
ThisWorkbook.Worksheets("Mail").Range("a5:b18").Copy
ThisWorkbook.Worksheets("temp").Range("a1").Select
ThisWorkbook.Worksheets("temp").Paste

'Copy contents from sheet2 as picture
Set rgExp = ThisWorkbook.Worksheets("graph").Range("a1:x93")
rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
ThisWorkbook.Worksheets("temp").Range("a19").Select
ThisWorkbook.Worksheets("temp").Paste
ThisWorkbook.Worksheets("temp").Range("a1:t105").Copy
worddoc.Range.PasteExcelTable linkedtoexcel:=False, wordformatting:=False, RTF:=False

Application.DisplayAlerts = False

ThisWorkbook.Worksheets("temp").Delete

End Sub

Paste contents from multiple Excel sheets into Outlook body

1 Answers