'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 |
---|