Manipulating Spacing In A String VBA

Gold Contributor

Aug 16, 2024

Question 1: Handling Line Breaks in the Email Body

When you're creating a bulleted list in an HTML email, using tags will cause extra spacing between lines because is a block-level element that adds a margin by default. To control the spacing and create a simple line break without extra spacing, you should use tags instead of .

In your specific case, you want to be able to write bullets with a simple line break between each one. Here's how you can adjust your code:

Replace with for your bullet points.
You can use the   (non-breaking space) entity to add indentation if needed.

Example:

Vba Code is untested backup your file first.

strbody = "<BODY style='font-size:8pt; font-family:Bahnschrift;'>" & _
"<b><u><font size=2>" & Sheets("Email").Cells(12, 1).Text & " " & "</b>" & Sheets("Email").Cells(12, 2).Text & "</u></font>" & _
"<p><b>" & Sheets("Email").Cells(14, 1).Text & " " & "</b>" & Sheets("Email").Cells(14, 2).Text & _

"<br><b>Pre-Blast Inspection:</b>" & _
"<br>- " & Sheets("Email").Cells(16, 1).Text & _
"<br>- " & Sheets("Email").Cells(16, 2).Text & _

"<br><b>Coating Inspection:</b>" & _
"<br>- " & Sheets("Email").Cells(17, 1).Text & _
"<br>- " & Sheets("Email").Cells(17, 2).Text & _

"<br><b>Final Inspection:</b>" & _
"<br>- " & Sheets("Email").Cells(18, 1).Text & _
"<br>- " & Sheets("Email").Cells(18, 2).Text

This setup ensures that each line will have a simple line break between them without adding extra space.

Question 2: Overcoming the Limitation of & _ in VBA

VBA has a limit on the number of line continuation characters (& _) you can use, which can make it difficult to manage large strings. Here are a few ways to handle this:

Concatenate the String in Chunks: Instead of continuing the string on multiple lines with & _, concatenate it in parts using variables.

Vba Code is untested backup your file first.

Dim part1 As String, part2 As String, part3 As String
part1 = "<BODY style='font-size:8pt; font-family:Bahnschrift;'>" & _
        "<b><u><font size=2>" & Sheets("Email").Cells(12, 1).Text & " " & "</b>" & Sheets("Email").Cells(12, 2).Text & "</u></font>"

part2 = "<br><b>Pre-Blast Inspection:</b>" & _
        "<br>- " & Sheets("Email").Cells(16, 1).Text & _
        "<br>- " & Sheets("Email").Cells(16, 2).Text

part3 = "<br><b>Coating Inspection:</b>" & _
        "<br>- " & Sheets("Email").Cells(17, 1).Text & _
        "<br>- " & Sheets("Email").Cells(17, 2).Text

strbody = part1 & part2 & part3

Use an Array or Collection: Store each line in an array or collection and concatenate them at the end:

Vba Code is untested backup your file first.

Dim lines() As String
ReDim lines(2)
lines(0) = "<BODY style='font-size:8pt; font-family:Bahnschrift;'>"
lines(1) = "<b><u><font size=2>" & Sheets("Email").Cells(12, 1).Text & " " & "</b>" & Sheets("Email").Cells(12, 2).Text & "</u></font>"
lines(2) = "<br><b>Pre-Blast Inspection:</b>"

strbody = Join(lines, "")

Write to a Text File (Advanced): If your string is very large, consider writing the HTML content to a text file and then reading it back into your strbody variable.

Adjusting Your Existing Code

Here’s how you might adjust your existing code to incorporate both of these improvements:

Vba Code is untested backup your file.

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim part1 As String, part2 As String, part3 As String

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

part1 = "<BODY style='font-size:8pt; font-family:Bahnschrift;'>" & _
        "<b><u><font size=2>" & Sheets("Email").Cells(12, 1).Text & " " & "</b>" & Sheets("Email").Cells(12, 2).Text & "</u></font>"

part2 = "<br><b>Pre-Blast Inspection:</b>" & _
        "<br>- " & Sheets("Email").Cells(16, 1).Text & _
        "<br>- " & Sheets("Email").Cells(16, 2).Text

part3 = "<br><b>Coating Inspection:</b>" & _
        "<br>- " & Sheets("Email").Cells(17, 1).Text & _
        "<br>- " & Sheets("Email").Cells(17, 2).Text

strbody = part1 & part2 & part3

On Error Resume Next
With OutMail
    .To = Sheets("Email").Cells(2, 2).Text
    .CC = Sheets("Email").Cells(5, 2).Text
    .BCC = Sheets("Email").Cells(8, 2).Text
    .Subject = Sheets("Email").Cells(10, 2).Text
    .HTMLBody = strbody & .HTMLBody
    .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

This way, you manage both the formatting issue and the string continuation limit.

The behavior of HTML formatting in emails generated through VBA can depend on the versions of Excel, Outlook, and even the underlying Office version and settings. The text, steps and codes were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Forum Discussion

Resources