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!

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! Read More

Cart

Cart