Adding a range to body of email


New Member
Jan 5, 2018
Hi, I have this VBA that works great. It convert a specific range in active worksheet and attaches as PDF to email. Now I'm trying to also add the same range to the body of the email. Any advise?

Sub SendPDF()
' Create PDF and send as attachment.

Dim strPath As String, strFName As String
Dim OutApp As Object, OutMail As Object

'Create PDF
strPath = Environ$("temp") & "" 'Or any other path, but include trailing ""

strFName = ActiveWorkbook.Name
strFName = Left(strFName, InStrRev(strFName, ".") - 1) & "_" & ActiveSheet.Name & ".pdf"

ActiveSheet.PageSetup.PrintArea = "$a$1:$h$25"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strPath & strFName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

'Set up outlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Create message
On Error Resume Next
With OutMail
.to = ""
.CC = ""
.BCC = ""
.Subject = "" & ActiveSheet.Name
.Body = "Hello," & vbCr & " " & ActiveSheet.Name & "." & vbCr & "Best regards," & vbCr & vbCr & ""

.Attachments.Add strPath & strFName

End With
'Delete any temp files created
Kill strPath & strFName
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Some videos you may like

Excel Facts

Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...