Perhaps this resource will help ? UNTESTED
http://stackoverflow.com/questions/3...o-outlook-body
You could also approach this in a different method: Create a pdf file of the worksheet, save it, then have your code refer to that saved file as an attachment.
Option Explicit
Sub EmailWithOutlook()
Dim oApp As Object
Dim oMail As Object
Dim WB As Workbook
Dim FileName As String
Dim wSht As Worksheet
Dim shtName As String
Dim ThisFile As String
ThisFile = "C:\Users\My\Desktop\Report.pdf"
Application.ScreenUpdating = False
With Range("A1:H65")
' Make a copy of the active worksheet
' and save it to a temporary file
'I need this to only select rows A to H on the current sheet
Sheets("Sheet1").Range("A1:H65").Copy '<--- Change Range to copy HERE
Set WB = ActiveWorkbook
End With
'Create and show the Outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
'I need this to pull the "to" from Settings:A1
.To = Sheets("Sheet1").Range("K1").Value '<--- Settings Sheet name here is Sheet1. Change as needed
'Uncomment the line below to hard code a subject
'I need this to pull the "Subject" from Settings:A2
.Subject = Sheets("Sheet1").Range("K2").Value '<--- Settings Sheet name here is Sheet1. Change as needed
'Uncomment the lines below to hard code a body
'I need this to pull the "body" from Settings:A3
.Body = Sheets("Sheet1").Range("K3").Value '<--- Settings Sheet name here is Sheet1. Change as needed
.Attachments.Add ThisFile
.Display
End With
'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
Sub CreatPDF()
Dim Report As String
Sheets("Sheet1").Range("A1:H65").ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="C:\Users\My\Desktop\Report.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Call EmailWithOutlook
End Sub
Bookmarks