Hello, I have some code that I managed to put together in an excel template that automatically generates an email in outlook. Question 1: I have found ways to create different types of breaks and font adjustments using "", "" etc. and that is working fine, however, there is a portion of my email that i need to be able to start manually writing in point form (the 3 spots with the "-" in the email body under each heading "final inspection, coating inspection, preblast inspection etc.) everything looks good, however when I am finished writing the first point in the email and i want to add another "-" on the next line underneath to add more point under each section, I hit enter to space down and it automatically skips two lines. I have played around with positioning of and as well as the break but i cannot find a solution. as far as formatting goes that seems to be the only real issue i am having with this. eg. i want to be able to do something like thisPre-Blast Inspection:-was good-found to be adhered well.-more info .... Question 2: there seems to be a maximum number of & _ breaks that i can add in this code and there are 2 additional lines that i was hoping to add. is there a workaround for that so that i can add more info? The code is as follows: Dim OutApp As ObjectDim OutMail As ObjectDim strbody As StringSet OutApp = CreateObject("Outlook.Application")Set OutMail = OutApp.CreateItem(0)strbody = "<BODY style = font-size:8pt; font-family:Bahnscrift>" & _"" & Sheets("Email").Cells(12, 1).Text & " " & "" & Sheets("Email").Cells(12, 2).Text & "" & _"" & Sheets("Email").Cells(14, 1).Text & " " & "" & Sheets("Email").Cells(14, 2).Text & _" " & Sheets("Email").Cells(15, 1).Text & " " & "" & Sheets("Email").Cells(15, 2).Text & _" " & Sheets("Email").Cells(16, 1).Text & " " & "" & Sheets("Email").Cells(16, 2).Text & _" " & Sheets("Email").Cells(17, 1).Text & " " & "" & Sheets("Email").Cells(17, 2).Text & _" " & Sheets("Email").Cells(18, 1).Text & " " & "" & Sheets("Email").Cells(18, 2).Text & _" " & Sheets("Email").Cells(19, 1).Text & " " & "" & Sheets("Email").Cells(19, 2).Text & _" " & Sheets("Email").Cells(20, 1).Text & " " & "" & Sheets("Email").Cells(20, 2).Text & _" " & Sheets("Email").Cells(21, 1).Text & " " & "" & Sheets("Email").Cells(21, 2).Text & _" " & Sheets("Email").Cells(22, 1).Text & " " & "" & Sheets("Email").Cells(22, 2).Text & _" " & Sheets("Email").Cells(23, 1).Text & " " & "" & Sheets("Email").Cells(23, 2).Text & _"" & Sheets("Email").Cells(25, 1).Text & " " & "" & Sheets("Email").Cells(25, 2).Text & "" & _"" & Sheets("Email").Cells(27, 1).Text & " " & "" & Sheets("Email").Cells(27, 2).Text & "" & _" " & Sheets("Email").Cells(28, 1).Text & _"" & Sheets("Email").Cells(29, 1).Text & " " & "" & Sheets("Email").Cells(29, 2).Text & "" & _" " & Sheets("Email").Cells(30, 1).Text & _"" & Sheets("Email").Cells(31, 1).Text & " " & "" & Sheets("Email").Cells(31, 2).Text & "" & _" " & Sheets("Email").Cells(32, 1).Text & _"" & Sheets("Email").Cells(33, 2).Text & "" & _" " & Sheets("Email").Cells(34, 2).Text & _" " & Sheets("Email").Cells(36, 2).Text & _"" & Sheets("Email").Cells(38, 2).Text & "" & _" " & Sheets("Email").Cells(39, 2).Text & " " & Sheets("Email").Cells(40, 2).Text & _"" & Sheets("Email").Cells(42, 2).TextOn Error Resume NextWith 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.Display.HTMLBody = strbody & .HTMLBodyEnd WithOn Error GoTo 0Set OutMail = NothingSet OutApp = Nothing Thanks for your patience and help!

Forum Discussion

Jn12345

Brass Contributor

Aug 15, 2024

Solved

Manipulating Spacing In A String VBA

Hello,

I have some code that I managed to put together in an excel template that automatically generates an email in outlook.

Question 1: I have found ways to create different types of breaks and font adjustments using "", "" etc. and that is working fine, however, there is a portion of my email that i need to be able to start manually writing in point form (the 3 spots with the "-" in the email body under each heading "final inspection, coating inspection, preblast inspection etc.) everything looks good, however when I am finished writing the first point in the email and i want to add another "-" on the next line underneath to add more point under each section, I hit enter to space down and it automatically skips two lines. I have played around with positioning of and as well as the break but i cannot find a solution. as far as formatting goes that seems to be the only real issue i am having with this.

eg. i want to be able to do something like this

Pre-Blast Inspection:

-was good

-found to be adhered well.

-more info ....

Question 2: there seems to be a maximum number of & _ breaks that i can add in this code and there are 2 additional lines that i was hoping to add. is there a workaround for that so that i can add more info?

The code is as follows:

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

strbody = "<BODY style = font-size:8pt; font-family:Bahnscrift>" & _
"" & Sheets("Email").Cells(12, 1).Text & " " & "" & Sheets("Email").Cells(12, 2).Text & "" & _
"" & Sheets("Email").Cells(14, 1).Text & " " & "" & Sheets("Email").Cells(14, 2).Text & _
" " & Sheets("Email").Cells(15, 1).Text & " " & "" & Sheets("Email").Cells(15, 2).Text & _
" " & Sheets("Email").Cells(16, 1).Text & " " & "" & Sheets("Email").Cells(16, 2).Text & _
" " & Sheets("Email").Cells(17, 1).Text & " " & "" & Sheets("Email").Cells(17, 2).Text & _
" " & Sheets("Email").Cells(18, 1).Text & " " & "" & Sheets("Email").Cells(18, 2).Text & _
" " & Sheets("Email").Cells(19, 1).Text & " " & "" & Sheets("Email").Cells(19, 2).Text & _
" " & Sheets("Email").Cells(20, 1).Text & " " & "" & Sheets("Email").Cells(20, 2).Text & _
" " & Sheets("Email").Cells(21, 1).Text & " " & "" & Sheets("Email").Cells(21, 2).Text & _
" " & Sheets("Email").Cells(22, 1).Text & " " & "" & Sheets("Email").Cells(22, 2).Text & _
" " & Sheets("Email").Cells(23, 1).Text & " " & "" & Sheets("Email").Cells(23, 2).Text & _
"" & Sheets("Email").Cells(25, 1).Text & " " & "" & Sheets("Email").Cells(25, 2).Text & "" & _
"" & Sheets("Email").Cells(27, 1).Text & " " & "" & Sheets("Email").Cells(27, 2).Text & "" & _
" " & Sheets("Email").Cells(28, 1).Text & _
"" & Sheets("Email").Cells(29, 1).Text & " " & "" & Sheets("Email").Cells(29, 2).Text & "" & _
" " & Sheets("Email").Cells(30, 1).Text & _
"" & Sheets("Email").Cells(31, 1).Text & " " & "" & Sheets("Email").Cells(31, 2).Text & "" & _
" " & Sheets("Email").Cells(32, 1).Text & _
"" & Sheets("Email").Cells(33, 2).Text & "" & _
" " & Sheets("Email").Cells(34, 2).Text & _
" " & Sheets("Email").Cells(36, 2).Text & _
"" & Sheets("Email").Cells(38, 2).Text & "" & _
" " & Sheets("Email").Cells(39, 2).Text & " " & Sheets("Email").Cells(40, 2).Text & _
"" & Sheets("Email").Cells(42, 2).Text

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
.Display
.HTMLBody = strbody & .HTMLBody

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Thanks for your patience and help!

excel

Macros and VBA

NikolinoDE
Sep 01, 2024
yw 🙂

3 Replies

NikolinoDE

Gold Contributor

Aug 16, 2024

Jn12345

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.

Jn12345
Brass Contributor
Sep 01, 2024
Hey, thanks for the response! i actually figured out what I was doing wrong and it was quite simple. Outlook was acting weird when there were any added so I doubled up on the and everything worked fine. sometimes its the simplest solutions.
- NikolinoDE
 Gold Contributor
 Sep 01, 2024
 yw 🙂