Email Worksheet selection without attachment

Posted on 2008-06-13
Last Modified: 2011-10-19
Hi, I have an excel file which contains to columns of text, about 5 rows, something like this:

Account   Amount
AEWGE   1000$
QQEGQ   3000$
QQERR   2500$

I need my excel to email these 2 columns of text NOT as attachment but as BODY of the message, much like if I would type these 2 columns by my self in Outlook.

Any ideas?

Question by:Gagik
  • 2
LVL 35

Accepted Solution

mvidas earned 125 total points
ID: 21782266
Hi Gagik,

Ron de Bruin has a method for doing just that, take a look here and don't hesitate to ask if you have any questions!

LVL 45

Expert Comment

ID: 21782441

A working example...

To send just the data and not the file you could use Ron de Bruin's code (see below) which send just an HTML result embedded in an email. It's in the attached file. To test is insert your own email where is says to do so in the VBA code (ALT+F8 to get to the editor, ALT+F8 to get back to the worksheet) and press the button.

Hope that helps

Sub Mail_Selection_Range_Outlook_Body()

' written by Ron de Bruin

' Working in Office 2000-2007

    Dim rng As Range

    Dim OutApp As Object

    Dim OutMail As Object

    Set rng = Nothing

    On Error Resume Next

    'Only the visible cells in the selection

    Set rng = Sheets("Sheet1").Range("A1:B5")

    'Selection.SpecialCells (xlCellTypeVisible)

    'You can also use a range if you want

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

    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 = "" 'recipient's email here

        .CC = ""

        .BCC = ""

        .Subject = "Hi Fred - Here's the monthly report. Regards, Jim"

        .HTMLBody = RangetoHTML(rng)

        .Send   'or use .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)

' Changed by Ron de Bruin 28-Oct-2006

' Working in Office 2000-2007

    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


    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


        Application.CutCopyMode = False

        On Error Resume Next

        .DrawingObjects.Visible = True


        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, _


        .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


    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

Open in new window


Author Closing Comment

ID: 31467032
This is amaizing!
Thanks a lot!!
LVL 35

Expert Comment

ID: 21782636
Glad to help! It does help to increase the width of the columns a little bit, as they're in the html (and likewise the email) in whatever width they are in excel. I often up the size a little bit to help the readability in emails.

Let us know if you need anything else!

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now