CODEWITHSUNDEEP
excelvba
Matt Pierce
Matt Pierce

Reputation: 805

Excel VBA TextBox Keep Line Break

I am creating an email using VBA in Excel. For the body of the email I am taking the value of a TextBox in the excel sheet. I have enabled multiple lines in the TextBox and have put text on the first line and have text on the line below, but when I generate the email, it takes both lines of text and puts them on the same line in the email.

I need to know how to keep the line breaks in the TextBox.

Sub Test1()

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim SigString As String
Dim Signature As String

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "?*@?*.?*" And _
       LCase(Cells(cell.Row, "C").Value) = "yes" Then

        Set OutMail = OutApp.CreateItem(0)

        SigString = Environ("appdata") & _
            "\Microsoft\Signatures\Default.htm"

        If Dir(SigString) <> "" Then
            Signature = GetBoiler(SigString)
        Else
            Signature = ""
        End If

        On Error Resume Next
        With OutMail
            .Display
            .To = cell.Value
            .Subject = "Reminder"
            .HTMLBody = "<p>Dear " & Cells(cell.Row, "A").Value & ",</p>" _
                  & "<br><br>" & ActiveSheet.TextBox1.Value & _
                  .HTMLBody               
            .Attachments.Add ("")
            .Display
        End With
        On Error GoTo 0
        Set OutMail = Nothing
    End If
Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

enter image description here

Here is the TextBox1 properties enter image description here

Upvotes: 1

Views: 1874

Answers (2)

Josh Eller
Josh Eller

Reputation: 2065

You can replace your newlines, vbNewLine, with HTML formatted line-breaks, <br>, like so:

With outMail
    .display
    .To = Cell.Value
    .subject = "Reminder"
    .HTMLBody = "<p>Dear " & Cells(Cell.row, "A").Value & ",</p>" _
          & "<br><br>" & Replace(ActiveSheet.TextBox1.Value, vbNewLine, "<br>") & _
          .HTMLBody
    .Attachments.Add ("")
    .display
End With

Upvotes: 1

urdearboy
urdearboy

Reputation: 14590

You can Split your string here to sperate lines

 .HTMLBody = "<p>Dear " & Cells(cell.Row, "A").Value & ",</p>" _
             & "<br><br>" _
             & Split(ActiveSheet.TextBox1.Value, ".")(0) &"."  _
             & "<br>" _
             & Split(ActiveSheet.TextBox1.Value, ".")(1) & "."  _
             & .HTMLBody

Upvotes: 1

Related Questions