Email current sheet in body, not as attachment

bpgolferguy

Active Member
Joined
Mar 1, 2009
Messages
469
Hi, I'm having a problem with a code I'm entering to email the active sheet in my workbook. I want the sheet to be in the body of the email, not as an attachement. But at the same time, it's adding a lot of unused space to the body of the email. Is there anyway to specify the cell range I want copied? Here's the code I have currently...it's all tied to a button:

Code:
Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
 
    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    'You can also use a sheet name
    'Set rng = Sheets("YourSheet").UsedRange
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
    With OutMail
        .To = "me@me.com"
        .CC = ""
        .BCC = ""
        .Subject = "CD Renewal " & Range("$L$13").Value & """"
        .HTMLBody = RangetoHTML(rng)
        .Display   'or use .Display
    End With
    On Error GoTo 0
 
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End If
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
    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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
This is the exact same question I was about to post...
How to put a "range" into body of email.

Here is the code I am using. It works great with Outlook.
If you look at the line for <b>strbody = </b> the last line I specify the range <b>rngData</b>, but it doesn't go into the email, so this is where I am hung up...

<quote>
Sub CDO_Mail_Small_Text()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant
Dim EmpN$

SomeVariable = "Variable Data Here"


Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
= "mail.bentimagelab.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With

strbody = "First Line of text in body" _
& vbNewLine & "Sent on: " & Format(Date, "dd/mmm/yy") _
& vbNewLine & rngData

With iMsg
Set .Configuration = iConf
.To = "someone@e-address.com"
.CC = ""
.BCC = ""
.From = """MyName"" <myemail@address.com>"
.Subject = "My Subject"
.TextBody = strbody
.Send
End With
End Sub
</Quote>
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
I think the <b>.HTMLBody = </b>" only sends as text string much like a "MsgBox =" would in code for an procedure. It's one cell value or a group of cell's string values tied together with the "&".

My next thought is inserting a range as a table? Different than a string, right? When I manually copy and paste Excel ranges into Outlook email I find that the range copies as a table in Outlooks formatting. So some solution might lie in this path of logic?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,606
Messages
5,597,134
Members
414,128
Latest member
Jorglo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top