Add paragraph in vba

Question

I have several cells I want to concatenate to send an email through excel

Turns out every cell I wish I had line breaks but I’m not getting

I’ve used Wraptext and Chr() but it’s not working, someone can help me?

 Dim outapp As Outlook.Application
Dim outmail As Outlook.MailItem
Dim linha As Integer
Dim folha_origem As String
Dim listamails As String
Dim email As String

'cria e chama os objetos

Set outapp = CreateObject("outlook.application")
Set outmail = outapp.CreateItem(olMailItem)
linha = 7
folha_origem = "Contactos Experts"

'desativar a mensagem de alerta
Application.DisplayAlerts = False

email = ""
While Sheets(folha_origem).Cells(linha, 4).Value <> ""
    listamails = listamails & ";" & Sheets(folha_origem).Cells(linha, 4).Value
    email = email & Chr(10) & Sheets(folha_origem).Cells(linha, 6).Value

    linha = linha + 1
Wend

linha = 7


    With outmail
        'email do destinatário
        .To = listamails
        'titluo da mensagem
        .Subject = email
        'mensagem
        .HTMLBody = Sheets(folha_origem).Range("F7").Value
        'enviar anexos que não existem
        '.Attachments.Add = Range("dshf
        .Display
       ' .Send


    End With
Application.DisplayAlerts = True

Answer 1

You must use the line break character, which can use the function Chr(10).

For this, I created an example that works, taking the values of cells.

Private Sub CommandButton1_Click()

        MsgBox (Range("A1") & Chr(10) & Range("B1"))


End Sub

Answer 2

New Line

For a new line in VBA it is possible to use the following options:

| Constante |                  Equivalente                   |                                            Descrição                                            |
|-----------|------------------------------------------------|-------------------------------------------------------------------------------------------------|
| vbCrLf    | Chr (13) + Chr (10)                            | Combinação de alimentação de linha de retorno de carro                                          |
| vbCr      | Chr (13)                                       | Caractere de retorno de carro                                                                   |
| vbLf      | Chr (10)                                       | Caractere de alimentação de linha                                                               |
| vbNewLine | Chr (13) + Chr (10) ou, no Macintosh, Chr (13) | Novo caractere de linha específicos de plataforma; o que for apropriado para a plataforma atual |

According to the Microsoft documentation

Code

Then the following code can be created to give compatibility between Windows and Mac Operating Systems in older versions of Excel:

Function novaLinha() As String
    #If Win32 Or Win64 Then
        novaLinha = Chr(10)
    #ElseIf Mac Then
        novaLinha = vbNewLine
    #End If
End Function

Sub teste()
    MsgBox "oi" & novaLinha & "tchau"
End Sub

Or just the vbNewLine in the newest.

html email

In html the <br> is used for new line.

Reference

Example:

"Bom dia,<br>Segue a tabela:"

or

.HTMLBody = Sheets(folha_origem).Range("F7").Value & "<br>" _
            & Sheets(folha_origem).Range("F8").Value

For more information, see this answer