VBA - adding extra lines to an email body

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
112
Office Version
  1. 2016
I have this code to send an email (not my own work - I flogged it). In the body, I'd like to add carriage returns and use more cell references for additional lines to this email.

If someone could enlighten me on how to adjust my code, that'd be fantastic.

VBA Code:
Sub Email()

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    Set rng = Selection.SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

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

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = Range("c350")
        .CC = ""
        .BCC = ""
        .Subject = Range("c351")
        .Body = Range("c354")
        .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Lee J

New Member
Joined
Jun 30, 2020
Messages
43
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi Jasesair,

You can use the below between the .Body line and before the .Display line:

& VbLf & _ = to carriage return a line down so after the Range("C351")
"" & vbLf & _ = to insert a blank line between lines of text
Range("Column&Row") = as per your c354
 

Lee J

New Member
Joined
Jun 30, 2020
Messages
43
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
NP you can also do something like this (you might be able to miss out the .value but my test was being fussy):

"This data was generated on " & Sheet1.Range("A4").Value & vbLf & _
 

Watch MrExcel Video

Forum statistics

Threads
1,113,940
Messages
5,545,112
Members
410,659
Latest member
theloudbloke
Top