CODEWITHSUNDEEP
excelvbaoutlook
ASalley
ASalley

Reputation: 17

How to add or remove spaces between lines of text in an email?

I create an Outlook email and I want to add or remove spaces between paragraphs.

I tried using vbNewLine as well as " " but neither make an empty line.

Additionally, there is a large space after each bullet point for sentences created by my for loop. It is not an empty line, but rather the spacing Before and After the sentence (looking at the Paragraph menu) is set to "Auto" and I would like both to be "0".

Sub Email_Budget()
   
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
   
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)
   
    Dim CaseCount As Long
    CaseCount = WorksheetFunction.CountA(Range("B6:B500"))
    'Debug.Print CaseCount
   
    Dim i As Integer
   
    With objEmail
        .To = "[email protected]"
        .Subject = "TEST1: May 2019 Budget"
        .HTMLBody = "Karen,"
        .HTMLBody = .HTMLBody & vbNewLine

        .HTMLBody = .HTMLBody & "The potential " & _
          MonthName(Month(ActiveSheet.Range("A2"))) & " invoices are below."

        .HTMLBody = .HTMLBody & vbNewLine
        For i = 1 To CaseCount
            If ActiveSheet.Cells(i + 5, 4).Value = "Yes" Then
                .HTMLBody = .HTMLBody & "<ul style='list-style-type:disc;'>" & "<li>" & _
                  ActiveSheet.Cells(i + 5, 2).Value & " - " & _
                  Format(ActiveSheet.Cells(i + 5, 6).Value, "Currency") & _
                  " (" & Format(ActiveSheet.Cells(i + 5, 8).Value, "Currency") & _
                  " without budget or invoicing)." & "</li>" & _
                  "<ul style = 'list-style-type:circle;'>" & "<li>" & "Last billed " & _
                  ActiveSheet.Cells(i + 5, 10) & "." & "</li>" & "</ul>" & "</ul>"
            End If
        Next i
        .HTMLBody = .HTMLBody & vbNewLine
        .HTMLBody = .HTMLBody & vbNewLine
        .HTMLBody = .HTMLBody & "Thank you,"
        .HTMLBody = .HTMLBody & vbNewLine
        .HTMLBody = .HTMLBody & "Kurt"
        .Display
    End With
End Sub

Upvotes: 0

Views: 2405

Answers (2)

W-hit
W-hit

Reputation: 361

This should do what you're looking for:

Sub Email_Budget()

   Dim objOutlook As Object
   Set objOutlook = CreateObject("Outlook.Application")

   Dim objEmail As Object
   Set objEmail = objOutlook.CreateItem(olMailItem)

   Dim CaseCount As Long
   CaseCount = WorksheetFunction.CountA(Range("B6:B500"))
   'Debug.Print CaseCount

   Dim i As Integer

   With objEmail
      .To = "[email protected]"
      .Subject = "TEST1: May 2019 Budget"
      .HTMLBody = "Karen,<br><br>"
      .HTMLBody = .HTMLBody & "The potential " & MonthName(Month(ActiveSheet.Range("A2"))) & " invoices are below.<br>"
      For i = 1 To CaseCount
        If ActiveSheet.Cells(i + 5, 4).Value = "Yes" Then
            .HTMLBody = .HTMLBody & Application.Trim("<ul style='list-style-type:disc;'><li style=""Margin: 0;"">" & ActiveSheet.Cells(i + 5, 2).Value & " - " & Format(ActiveSheet.Cells(i + 5, 6).Value, "Currency") & " (" & Format(ActiveSheet.Cells(i + 5, 8).Value, "Currency") & " without budget or invoicing).</li><ul style = 'list-style-type:circle;'><li style=""Margin: 0;"">Last billed " & ActiveSheet.Cells(i + 5, 10) & ".</li></ul></ul>")
        End If
      Next i
      .HTMLBody = .HTMLBody & "<br>Thank you,<br>Kurt"
      .Display
   End With
End Sub

Upvotes: 0

jspcal
jspcal

Reputation: 51894

perhaps use a line break element (br)?

Line 1<br>
Line 2

Upvotes: 1

Related Questions