How to send worksheet in email body?

Question

Is there any way I can attach as well as send a worksheet as an email body.

The below VBA code sends the worksheet as an attachment.

How can I send a worksheet in the body of email?

Sub Email()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim wbTemp As Workbook
    Dim strFilename As String
    Dim Sendrng As Range
    
    ThisWorkbook.Worksheets("Test Worksheet").Copy
    
    
    Set wbTemp = ActiveWorkbook
    
    
    wbTemp.SaveAs ThisWorkbook.Path & "/" & "TestWb", XlFileFormat.xlOpenXMLWorkbook
    
    strFilename = wbTemp.FullName
    
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "Test Email"
        .Body = ""
        .Attachments.Add strFilename
        .display
    End With
    
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    wbTemp.Close
    
    Kill strFilename
    
End Sub

Here's a previous question that answers what you are looking for. Also, this website by Ron de Bruin covers the topic. — Dan

Doug Coats Doug Coats · Accepted Answer · 2016-04-28T14:26:40

Private Sub CommandButton1_Click()

    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim rng As Range

    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

     Set Sendrng = Worksheets("Sheet1").Range("A1:g15")

     Set AWorksheet = ActiveSheet

     With Sendrng
    .Parent.Select
    Set rng = ActiveCell
     .Select
      ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope
            With .Item
                .To = "recipient"
                .CC = ""
                .BCC = ""
                .Subject = "My subject"
                .Send
            End With

        End With
              rng.Select
    End With

        AWorksheet.Select

Sheet1.Activate
Range("c2:c3").ClearContents
Range("e2:e3").ClearContents
Range("c5") = ""
Range("B8:b10").ClearContents
Range("c8:c10").ClearContents
Range("d8:d10").ClearContents
Range("e8:e10").ClearContents
Range("f8:f10").ClearContents
Range("g8:g10").ClearContents
Range("b13") = ""



 StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False

End Sub

you should add your code that also copies it to this. Forgive all the crap you dont need here, I just copy and pasted one I have been using

How to send worksheet in email body?

1 Answers