• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Paste Excel Range into Body of Email

Status
Not open for further replies.
T

t33p33

New Member
I am stuck.

I am trying to create a macro that will copy an excel range into the body of an email. I would prefer to use HTML to retain the formatting.

Range is C5:E5 xldown

I can send the email with the workbook as an attachement (which I want), I just can't seem to get the range in the body.

The below version sent the email and attachement, but "-1" as the message body. Definitely now what I had wanted! I have left in my commented out lines that I used as I was trying to figure things out in case I was close with them.

Code: 
Sub Email()
 
Dim dataRange As Excel.Range
 
'xlWorkSheet = Sheets("Total Hours by Manager").Select
Sheets("Total Hours by Manager").Select
dataRange = Range("C5:E5").End(xlDown).Select
 
 
'Range(Selection, Selection.End(xlDown)).Select
 
 
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
FName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
 
Dim myOutlok As Object
Dim myMailItm As Object
 
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
FName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
 
With otlNewMail
.To = [EMAIL]xxxxx@xxxxx.com[/EMAIL]
.Body = "Attached is this pay period's Report." & Chr(13)
 
 
.Attachments.Add FName
.Send
 
End With
 
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
 
 
 
End Sub
 
Here is version 2, which also pastes "-1" into the message body.

Code: 
Sub sendMail()
 
Sheets("Total Hours by Manager").Select
Dim objOutlook, objMsg, objNameSpace, objFolder, strOutput, strSubject, StrMsg
StrMsg = ActiveSheet.Range("C5:E150").Copy
 
StrSbj = "Bi Weekly Time Reporting"
Const olMailItem = 0
Set objOutlook = CreateObject("Outlook.application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
Set objMsg = objOutlook.CreateItem(olMailItem)
objMsg.To = "xxxxx@xxxxx.com"
objMsg.Display
objMsg.HTMLBody = StrMsg
objMsg.Subject = StrSbj
Set objFolder = Nothing
Set objNameSpace = Nothing
Set objOutlook = Nothing
Set objMsg = Nothing
Set Sendrng = Worksheets("Total Hours by Manager").Range("C5:E150")
Set ss = CreateObject("WScript.Shell")
 
End Sub

I will be using Outlook 2010 or 2013 to send this.
 
I want code for sending excel sheet1 data in mail body along with picture and data, and another sheet2 enclosed as attachment.
 
Status
Not open for further replies.
Back
Top