'Sending mail with specific range in body using Excel VBA

I have VBA code by which I send mails with specific string body.

I need this code to paste specific range(B2:F13) from sheet "MAIN" to mail body with its format.

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("AD252"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 200 Then
        Call Mail_small_Text_Outlook
    End If
End Sub

Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Average sales per store:" & vbNewLine & vbNewLine & _
              "" & vbNewLine & _
              "SPAR:" & vbNewLine & _
              "" & vbNewLine & _
              Range("AI306").Value & vbNewLine & _
              "" & vbNewLine & _
              "Franchise:" & vbNewLine & _
              "" & vbNewLine & _
              Range("AI307").Value & vbNewLine & _
              "" & vbNewLine & _
              "Daily(SPAR):" & vbNewLine & _
              "" & vbNewLine & _
              Range("AI308").Value & vbNewLine & _
              "" & vbNewLine & _
              "Daily(ALL):" & vbNewLine & _
              "" & vbNewLine & _
              Range("AI309").Value

    On Error Resume Next
    With xOutMail
        .To = Range("BX8").Value & Range("BX9").Value & Range("BX10").Value & Range("BX11").Value & Range("BX12").Value & Range("BX13").Value & Range("BX14").Value & Range("BX15").Value & Range("BX16").Value & Range("BX17").Value & Range("BX18").Value & Range("BX19").Value & Range("BX20").Value & Range("BX21").Value
        .CC = ""
        .BCC = Range("BX30").Value
        .Subject = Range("AG477").Value
        .Body = xMailBody
        .Display   'or use .Send
        .attachments.Add ("EXCEL FILE")
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Every time I try to add new line or make it without new line like this

xMailBody = worksheets("MAIN").Range("B2:F13").Value

I get type mismatch error.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source