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

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


    Excel.Application.Quit
    
    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 = "angela.mXXXXXX@XXXXXX.com"
        'EmailAddress = "rick.wXXXXXX@XXXXX.com"
        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.Save
        wkb.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            SavePDFas, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
            False
        Excel.Application.ActiveWorkbook.Save
        wkb.Close
        Excel.Application.Quit
        xlApp.Quit
        
    '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
            .Send
        End With
        
    
   
    'House cleaning
    Set OutlookApp = Nothing
    Set xlApp = Nothing
    Set XLWbk = Nothing
    Set XLSht = Nothing
    
    
    


  
  
    
End Function

Select allOpen in new window