Sending a Specific Range via Email in Excel VBA

If you want to send only a specific range from a workbook, you can first let the user select the range, copy it into a new workbook, and then email that new workbook:

Sub SendSelectedRange()
    Dim selectedRange As Range
    Dim appOutlook As Outlook.Application
    Dim MailItem As Outlook.MailItem
    ' Let user select the range to send
    Set selectedRange = Application.InputBox( _
        Prompt:="Select the range to email", Type:=8)
    ' Copy the selected range
    selectedRange.Copy
    ' Create a new workbook and paste the copied range
    Workbooks.Add
    ActiveSheet.Paste
    ' Save and close the new workbook
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\C:\Users\POPOLY\Desktop\Doc.xlsx"
    ActiveWorkbook.Close
    ' Start Outlook and create a new email
    Set appOutlook = CreateObject("Outlook.Application")
    Set MailItem = appOutlook.CreateItem(olMailItem)
    ' Set email properties
    MailItem.To = "max.muster@mailziel.de"
    MailItem.Subject = "Test"
    ' Attach the newly saved workbook
    MailItem.Attachments.Add ThisWorkbook.Path & "\AttachmentWorkbook.xlsx"
    ' Send the email
    MailItem.Send
    ' Quit Outlook and clean up
    appOutlook.Quit
    Set MailItem = Nothing
    Set appOutlook = Nothing
    Set selectedRange = Nothing
End Sub

Explanation:

• The method Application.InputBox() allows the user to select a range with the mouse.
• The Copy() method copies this selected range. Since no destination is specified, the copied data is stored in the clipboard.
• A new workbook is created with Workbooks.Add(), which becomes the active workbook.
• The clipboard contents are pasted into the new workbook using Paste().
• The new workbook is saved (here named AttachmentWorkbook.xlsx) and then closed.
• An email is created using CreateItem(). The To, Subject, and Attachments properties are assigned. The attachment is the newly saved workbook.
• The email is placed into the Outlook Outbox, ready for sending.