By Gilbert Jacob


2013-09-06 17:14:36 8 Comments

I am working on an excel report that I want to automate, however, the range of the cells are not being pasted in Outlook.

Here is my code:

Sub Mail_Selection_Range_Outlook_Body()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
' Only send the visible cells in the selection.
Set rng = Selection.SpecialCells(xlCellTypeVisible)
Set rng = Sheets("Sheet1").RangeToHtml("D4:D12").SpecialCells(xlCellTypeVisible, xlTextValues)
On Error GoTo 0

If rng Is Nothing Then
    MsgBox "The selection is not a range or the sheet is protected. " & _
           vbNewLine & "Please correct and try again.", vbOKOnly
    Exit Sub
End If

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
    .To = ThisWorkbook.Sheets("Sheet2").Range("C1").Value
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .HTMLBody = RangeToHtml.rng
    ' In place of the following statement, you can use ".Display" to
    ' display the e-mail message.
    .Display
End With
On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I am not getting any error, it's just that it does not paste range in outlook. Thank you.

2 comments

@David Zemens 2019-10-22 17:26:20

Often this question is asked in the context of Ron de Bruin's RangeToHTML function, which creates an HTML PublishObject from an Excel.Range, extracts that via FSO, and inserts the resulting stream HTML in to the email's HTMLBody. In doing so, this removes the default signature (the RangeToHTML function has a helper function GetBoiler which attempts to insert the default signature).

Unfortunately, the poorly-documented Application.CommandBars method is not available via Outlook:

wdDoc.Application.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"

It will raise a runtime 6158:

enter image description here

But we can still leverage the Word.Document which is accessible via the MailItem.GetInspector method, we can do something like this to copy & paste the selection from Excel to the Outlook email body, preserving your default signature (if there is one).

Dim rng as Range
Set rng = Range("A1:F10") 'Modify as needed

With OutMail
    .To = "[email protected]"
    .BCC = ""
    .Subject = "Subject"
    .Display
    Dim wdDoc As Object     '## Word.Document
    Dim wdRange As Object   '## Word.Range
    Set wdDoc = OutMail.GetInspector.WordEditor
    Set wdRange = wdDoc.Range(0, 0)
    wdRange.InsertAfter vbCrLf & vbCrLf
    'Copy the range in-place
    rng.Copy
    wdRange.Paste
End With

Note that in some cases this may not perfectly preserve the column widths or in some instances the row heights, and while it will also copy shapes and other objects in the Excel range, this may also cause some funky alignment issues, but for simple tables and Excel ranges, it is very good:

enter image description here

@Paul-Jan 2013-09-08 12:20:01

First off, RangeToHTML. The script calls it like a method, but it isn't. It's a popular function by MVP Ron de Bruin. Coincidentally, that links points to the exact source of the script you posted, before those few lines got b̶u̶t̶c̶h̶e̶r̶e̶d̶ modified.

On with Range.SpecialCells. This method operates on a range and returns only those cells that match the given criteria. In your case, you seem to be only interested in the visible text cells. Importantly, it operates on a Range, not on HTML text.

For completeness sake, I'll post a working version of the script below. I'd certainly advise to disregard it and revisit the excellent original by Ron the Bruin.

Sub Mail_Selection_Range_Outlook_Body()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
' Only send the visible cells in the selection.

Set rng = Sheets("Sheet1").Range("D4:D12").SpecialCells(xlCellTypeVisible)

If rng Is Nothing Then
    MsgBox "The selection is not a range or the sheet is protected. " & _
           vbNewLine & "Please correct and try again.", vbOKOnly
    Exit Sub
End If

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


With OutMail
    .To = ThisWorkbook.Sheets("Sheet2").Range("C1").Value
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .HTMLBody = RangetoHTML(rng)
    ' In place of the following statement, you can use ".Display" to
    ' display the e-mail message.
    .Display
End With
On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' By Ron de Bruin.
    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

@Vinod 2015-06-01 10:04:53

I have used your code for my requirement and its working fine, was wondering if I could add some Email body as well... like some text. I tried .HTMLBody="Hi, ..." & .HTMLBody=RangetoHTML(rng) but it didnt work.

@Vinod 2015-06-01 10:18:31

Got the answer in the Ron the Bruin post itself, thanks

@synthaxe 2016-02-13 23:19:00

@Vinod Were you able to add some additional text to the body of the email besides the one that was pasted by the macro?

@synthaxe 2016-02-13 23:26:51

Also, is there a way for it to copy images as well?

@Vinod 2016-02-14 06:23:47

@synthaxe: you can get an idea how to add additional text to email body by following Ron the Bruin's post... Am not sure how to add image to email besides I now use java to send emails

@JCKE 2019-02-23 02:11:58

I'd like to add that this method unfortunately doesn't copy color themes over as I found out, see this question for methods to keep the theme intact

@shadoe2020 2019-08-01 15:36:26

why does .To have a value of ThisWorkbook.Sheets("Sheet2").Range("C1").Value

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1037988 View
  • 1790 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

2 Answered Questions

[SOLVED] How to add excel range as a picture to outlook message body

0 Answered Questions

VBA Excel runtime error with Outlook GetNamespace("MAPI")

  • 2019-02-23 13:34:47
  • Ian Henry
  • 244 View
  • 0 Score
  • 0 Answer
  • Tags:   excel outlook

3 Answered Questions

[SOLVED] Excel VBA macro to send emails to unique users in range

  • 2018-07-02 17:22:03
  • HunterTTP
  • 1055 View
  • 2 Score
  • 3 Answer
  • Tags:   excel vba excel-vba

0 Answered Questions

Getting Error "ActiveX component can't create object while connecting with CMS supervisor

  • 2018-05-22 03:50:50
  • Parveen Saroha
  • 332 View
  • 0 Score
  • 0 Answer
  • Tags:   vba excel-vba excel

1 Answered Questions

Missing hyperlinks when exporting from excel to email

1 Answered Questions

1 Answered Questions

VBA Code to send worksheet in email body

1 Answered Questions

[SOLVED] Copy data from worksheet to html file to mail

  • 2016-01-19 11:18:16
  • Shubham Agarwal
  • 768 View
  • -1 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

Sponsored Content