Code to add ranged cells from an Excel Spreadsheet in an email body

I have the code below that works great when I want to insert cells from an excel worksheet in the body of an email.  However what if I want a range like in my code I have

MsgBody1 = Sheets("data").Range("M2")

How can I modify correctly to show cells in I1:J12 for example.  I have tables in my data I want to include in the body of an email.

Here is my code:
Function DistributeDailyConcept()
    'Declare variables
    Dim MyFilePath As String
    Dim SavePDFas As String
    Dim OutlookApp As Outlook.Application
    Dim MItem As Outlook.MailItem '<-- Early binding
    Dim EmailAddress As String
    Dim EmailSubject As String
    Dim MsgBody1 As String
    Dim MsgBody2 As String
    Dim ShipDate As String
    Dim wkb As Workbook
    'Dim xlApp As Excel.Application

    Set xlApp = CreateObject("Excel.Application")

    xlApp.Visible = True

    xlApp.Workbooks.Open "C:\Share\Daily Sales\Daily Sales Report by Concept JOM.xlsx", True, False
    Excel.Application.ScreenUpdating = False
    Excel.Application.DisplayAlerts = False
    xlApp.DisplayAlerts = False
    'Create Outlook object
        Set OutlookApp = New Outlook.Application
    'Specify email address and email subject
        EmailAddress = ""
        'EmailAddress = ""
        EmailSubject = "Daily Sales Report by Concept JOM"
    'Build parts of name of PDF file
        MyFilePath = "C:\Share\Daily Sales"
        strFile = Dir(MyFilePath & "\Daily Sales Report by Concept JOM.xlsx")
        'MyFileName = strFile
       Set wkb = Excel.Workbooks.Open(MyFilePath & "\" & strFile)
    'Specify email message
        MsgIntro = "Please find the Daily Sales Report by Concept JOM. "
        MsgBody1 = Sheets("data").Range("M2")
        MsgBody2 = Sheets("data").Range("M3")
        MsgFile = "File with all account detail is also saved at C:\Share\Daily Sales\Daily Sales Report by Concept JOM.xlsx"
        MsgEnd = "If you have any questions, please let us know...Kind Regards, NAM DTC Finance"
        ShipDate = Sheets("Data").Range("GR")
        SavePDFas = "C:\Share\Daily Sales\Daily Sales Report by Concept JOM " & ShipDate & ".pdf"
    'process the data in the workbook here
    'Save Transaction Form as PDF file in same directory as this
    'Excel workbook
        wkb.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            SavePDFas, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    'Send out the email
        Set MItem = OutlookApp.CreateItem(olMailItem)
        With MItem
            .To = EmailAddress
            .Subject = EmailSubject & " " & ShipDate
            .Body = MsgIntro & Chr(10) & Chr(10) & MsgBody1 & Chr(10) & Chr(10) & MsgBody2 & Chr(10) & Chr(10) & MsgFile & Chr(10) & Chr(10) & MsgEnd
            .Attachments.Add SavePDFas
        End With
    'House cleaning
    Set OutlookApp = Nothing
    Set xlApp = Nothing
    Set XLWbk = Nothing
    Set XLSht = Nothing

End Function

Open in new window

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
...or, (if the range will not change), you could "build" the range manually:
Something roughly like this worked for me:

YourBody = Sheets("data").Range("I1") & " , " & Sheets("data").Range("J1") & vbCrLf _
                    & Sheets("data").Range("I2") & " , " & Sheets("data").Range("J2") & vbCrLf _
                    & Sheets("data").Range("I3") & " , " & Sheets("data").Range("J3") & vbCrLf _
                    & Sheets("data").Range("I4") & " , " & Sheets("data").Range("J4") & vbCrLf _
                    & Sheets("data").Range("I5") & " , " & Sheets("data").Range("J5") & vbCrLf _
                    & Sheets("data").Range("I6") & " , " & Sheets("data").Range("J6") & vbCrLf _
                    & Sheets("data").Range("I7") & " , " & Sheets("data").Range("J7") & vbCrLf _
                    & Sheets("data").Range("I8") & " , " & Sheets("data").Range("J8") & vbCrLf _
                    & Sheets("data").Range("I9") & " , " & Sheets("data").Range("J9") & vbCrLf _
                    & Sheets("data").Range("I10") & " , " & Sheets("data").Range("J10") & vbCrLf _
                    & Sheets("data").Range("I11") & " , " & Sheets("data").Range("J11") & vbCrLf _
                    & Sheets("data").Range("I12") & " , " & Sheets("data").Range("J12")

Open in new window

Rey Obrero (Capricorn1)Commented:

MsgBody1 = Sheets("data").Range("I1:J12")
gracie1972Author Commented:
@ Rey I get a type mismatch error when I try that.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Rey Obrero (Capricorn1)Commented:
what is the content of I1:J12 ?
upload a copy of the excel file
Jeffrey CoachmanMIS LiasonCommented:
The issue is that, to do multiple "rows",... you need some sort of carriage return/Line feed to actually insert a range into a text email message body (text or HTML)
My guess is that you will have to find a way to insert this range as a table...

...but, lets see what Rey or the other experts will post

gracie1972Author Commented:
I cant it is our daily sales template.
It is just a table with linked data from another sheet.  Might be too complicated for what I need.
gracie1972Author Commented:
Thanks Jeffrey I will try that.
gracie1972Author Commented:
That worked perfect.

Any idea as to why the SaveASPDF is not working (This code worked on another project where I only had 1 sheet)?  I have multiple sheets and I only want to save and convert one of the sheets to PDF and attach to email.

Or should I open another question?
Jeffrey CoachmanMIS LiasonCommented:
I actually tested my snippet in a modified version of your code, and the PDF was created just fine.

It would be best if you posted another question for the PDF issue.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.