Add paragraph in vba

0

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

It turns out that every cell I would like to have line breaks but I am not able to

I have already used WrapText and Chr () but it is not working, can someone 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
    
asked by anonymous 26.10.2018 / 14:00

2 answers

0

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

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

Private Sub CommandButton1_Click()

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


End Sub
    
26.10.2018 / 14:17
0

New Line

For a new line in VBA you can 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 Microsoft Documentation

Code

Then the following code can be created to support 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 vbNewLine on the newest.

email in html

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

    
26.10.2018 / 16:06