asked on

Excel VBA- Retaining original paragraph spacing and format email body using HTML

Hi Experts,

I asked a question a few days ago regarding retaining paragraph spacing when data is copied and then pasted into a text field of a userform and then submitted to Outlook to be sent to the desired recipients. The response I received worked but only if I don't use HTML for formatting the email's message content. Since I've been toying with it trying to make it work with HTML but I haven't been successful. I've searched high and low over the internet and still no solution.

My questions are:

Is it possible to retain paragraph spacing using .HTMLBody in the email module? And if so how do I accomplish this? I would like to keep the "BOLD" and "Underline" formatting within the email.

Below are examples of how the output looks with each code variation-

Module setting for body of email message:

.HTMLBody = "<html><body>" & RPV_Body & "</html></body>"

Open in new window

.HTMLBody send email code-

Private Sub btnSendMail_Click()

'sends email
RPV_Mail Me.comboMgr.Value, Me.comboSup, "BAN " & Me.txtBAN.Value & " 

Feedback", "<B>" & "<U>" & _
" Feedback:     " & "</B>" & "</U>" & "<BR>" & _
Me.textFeedback.Text & "<BR>" & "<BR>" & "<BR>" & _
"<B>" & "<U>" & "Rep's Notes:     " & "</B>" & "</U>" & "<BR>" & _
Me.TextRepNotes.Text

Open in new window

Module setting for body of email message:

.Body = RPV_Body

Open in new window

.Body send email code-

Private Sub btnSendMail_Click()

'sends email
RPV_Mail Me.comboMgr.Value, Me.comboSup, "BAN " & Me.txtBAN.Value & " 

Feedback", " Feedback:     " & vbNewLine & _
Me.textFeedback.Text & "<BR>" & vbNewLine & vbNewLine & _
"Rep's Notes:     " & vbNewLine & _
Me.TextRepNotes.Text

Open in new window

Thank you in Advance!

"Abys" Wallace

ASKER

Here's a sample workbook and text data to copy and paste into the "Rep's Notes" field. Also to bring up the tracker please click on the "Black" header that says "RPV" at the top of the worksheet.
Sample-Mail-needHTML.xlsm
sample-copy-paste-data.txt

als315

Look at sample.
Sample-Mail-needHTML.xlsm

Ryan

I opened the solution, because I was actually more curious how you got formatted text INTO the excel field. A textbox from a form is creative.

Anyhow, I'm wondering why you can't send the email as plaintext to retain the text formatting? You lose other formattings, but it doesn't look like you're using that anyway.

If sending HTML, I'm pretty sure it's just whitespace you have to fix, in particular carriage returns, tabs, and consecutive spaces. It's pretty easy to replace VBCrLf with ' ', chr(9) with '&emsp;'. There's probably a cleaner way, but I would replace 2 spaces ' ' with '  '

"Abys" Wallace

ASKER

@als315 ... Thank you for taking a look at this but it still doesn't accomplish what I was trying to do which was keeping the original format of the pasted data... It still wraps all the text instead of retaining the paragraph spacing like such:

Adjustment Template
Adjustments over $50
BAN: 123456789

PTNs: 231-555-8015

Case Number:

Amount of the Adjustment .99

Reason for Adjustment: blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah

Date where the charges appeared: 11/24

Agent at Fault Emp ID: n/a

Copy of the Notes: blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah

Initial of Supervisor (after review cases):

"Abys" Wallace

ASKER

@MrBullwinkle ... I'm using HTML because certain text needs to be made BOLD, italicized, underlined, or a different font color and or size... The text keeps the structure but doesn't allow the end user the ability to place emphasis on certain words within the body of the email message... Below is a sample of how the end user would like the data to look once the email is composed...

Sample-Mail-needHTML.xlsm

als315

In html you can insert any formatting tags as you like. I'd like to show only sample. You can look at this page's code and for your example will see:
It still wraps all the text instead of retaining the paragraph spacing like such: Adjustment Template Adjustments over $50 BAN:  123456789 PTNs:  231-555-8015 Case Number:   Amount of the Adjustment  .99 Reason for Adjustment: blah .....blah Date where the charges appeared:  11/24 Agent at Fault Emp ID: n/a Copy of the Notes: blah ...... blah Initial of Supervisor (after review cases):

Compose message in Outlook (I prefer any HTML editor, because office add too many extra tags), save it as html and take tags from this sample.

"Abys" Wallace

ASKER

@ALS315 ... Yes, HTML allows the ability to add line breaks but keep in mind the data will be pasted into a text field "Rep's Notes" aka me.TextRepNotes.text so I'm unsure how I would be able to add the line breaks needed. In other words the following text:

Adjustment Template
Adjustments over $50
BAN: 123456789

PTNs: 231-555-8015

Case Number:

Amount of the Adjustment .99

Reason for Adjustment: blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah

Date where the charges appeared: 11/24

Agent at Fault Emp ID: n/a

Copy of the Notes: blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah

Initial of Supervisor (after review cases):

Wouldn't be seen in the actual VBA code... instead the actual code will hold the me.TextRepNotes.text <<< I was wondering is there a way that once the end user pastes data into this field and emails it to the recipient the line spacing and paragraph structure are able to be retained?

Also the data that's copied-then-pasted into the Rep's Notes field will vary on what's included. I'm just looking for a way to get

.HTMLBody = "<html><body>" & RPV_Body & "</html></body>"

Open in new window

to work like

.Body = RPV_Body

Open in new window

does.

With plain text the paragraph structure is maintained whereas with HTML it isn't unless you manually place line breaks in each time the end user pastes data into the "Reps Notes" field. HTML jumbles all the data together which I'm trying to prevent...

Thank you for your help :)

Ryan

You've not addressed my suggestion of replacing the whitespace chars with their HTML tag equivalents.
The only other issues I'm reading into is that if the display width is less than you intended, then it'd going to force a line wrap. I'm sure there are HTML codings you can do to force the page width, such that if the email application window is less than that, you'll see a horizontal scroll bar, rather the formatting being "wrapped to fit".

"Abys" Wallace

ASKER

Hi @MrBullwinkle ... thanks again for your response & my apologies about the white space recommendation not being addressed. The other expert recommended the same thing but I was trying to explain that I didn't think it was a carriage issue since the data contained within the email are coming from text fields on the form (ie: me.TextRepNotes.text) .. There's no way for me to include the line breaks due to all the data being pasted into one field and then the form sends that field's data to the email body along with the other fields. Even with scroll bars on the forms text field and when the email window is explanded the words are still wrapped when using .HTMLBody ..

In my initial example at the top of the post I have an image using HTML and an image not using HTML (just plain text) ... Same user form, same width, same exact data but the plain text keeps the original paragraph structure and spacing whereas HTML doesn't it. I was just looking for a solution that'll allow the data to retain the same structure when using HTML... I was hoping there was once but it seems that there probably isn't a way.

I've been looking online still with no luck... Testing different methods ... Hope I didn't over explain ...

"Abys" Wallace

ASKER

Here's a sample of a workbook with scroll bars included in the text fields ..
Sample-Mail-needHTML.xlsm

ASKER CERTIFIED SOLUTION

Ryan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

"Abys" Wallace

ASKER

Thank you MrBullwinkle! ...

RPV_Body = Replace(RPV_Body, vbCrLf, "<br/>", 1, -1, vbTextCompare)

Open in new window

works great! I didn't understand where to place / how to code the carriage returns .. Thank you for giving me an example to view... Had a "duhh" moment :)

Appreciate your patience and working through this with me ...

Have a great one! :)

"Abys" Wallace

ASKER

Sample-Mail-needHTML.xlsm