Format E-mail Body

Hello everyone,

Can someone help me to format my e-mail body to the same as the default format of the e-mails?

I have .htmlbody because the Signature contains an image.

Also, the message is in a worksheet, but now I lost the breaks between lines of the cells.

eg:

Hello,

Regards.

Now is:

Hello, Regards.

Here's the code:

Please Login or Register  to view this content.

Thank you for your help.

Hello, thank you for your reply.

What I mean by default format does not refer to the code. When you create a new e-mail with outlook, you already have a format for the text you are going to write.
I want the cells from the worksheet to be in that format.

Here's the result I have:

Capturar.PNG

And here's what I want:

Capturar1.PNG

Just one thing that will also add difficulty to the case: the message i need to format is not always the same, so the line break will not occur always in the same place.

When I had .body, although i couldn't get the signature, the cells was copied correctly, but now, not only it is pasted as plain text, it also loses the line breaks.

Thank you for your help.

Hi Rui,

To solve your problem, you have to understand the difference between an Outlook Email .Body and .HTMLBody.

When sending an Email using Outlook, the Email Body can be either .Body or .HTMLBody:
a. Body uses ASCII code for a NEW LINE which can be one of:
(1) Carriage Return LineFeed - the combination of Characters Chr(13) and Chr(10) which can also be coded as vbCrLf.
(2) LineFeed Only - Chr(10) which can also be coded as vbLf.
(3) Carriage Return Only - Chr(13) which can also be coded as vbCr.
NOTE: The above numbers are decimal numbers (e.g. the ASCII code for Linefeed is Decimal 10).

b. HTMLBody uses HTML code to indicate a NEW LINE. The HTML Code for NEW LINE is <BR> .

c. To switch between .Body and .HTMLBody, the appropriate NEW LINE code must be used.

For example if reading code from a Cell and to convert to HTML:

Please Login or Register  to view this content.

To convert from HTML code to Body code:

Please Login or Register  to view this content.

See the attached file which demonstrates the above concepts and contains the following code in ordinary code module ModOutlookBodyVsHTMLBody:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

Thank you very much for your explanation.

For the line breaks, the problem is solved (although I will need more time to understand it all, because the ASCII part is completely new to me). I have the cell in my worksheet to read as HTML and it works thanks to you.

The thing is, to have my signature, which contains an image, I need to have .htmlbody, right? But that way, how can i keep the format on the body? Because when I convert to .body, I loose the image from the signature. I've tried to convert to .body and then convert back to .htmlbody but that does not seem to do the trick.

Anyway, thank you for the explanation. Not only you tried to help with the problem, but also provided a way to learn the basis, so I am grateful for your answer.

PS: thank you for the Variable Declaration advice. It's a nice control to have.

To add the signature, try the following (new code in red):

Please Login or Register  to view this content.

Lewis

With the workbook you provided, and with the change you made above, the result is this:

Capturar.PNG

where the bottom part is the logo (image) I have in the signature.

Trying it in the Convert 'B14' to .Body, this is the result:

Capturar1.PNG

Where no logo appears.

So here you can see my dilemma. I either have the body formatted the way I want it but without the Signatures logo, or have the Signature alright but not the body format.

Anyway, thanks again for your efforts.

Rui said:

So here you can see my dilemma. I either have the body formatted the way I want it but without the Signatures logo, or have the Signature alright but not the body format.

In Excel 2016, I was able to duplicate your problem.

Outlook Font Types and Sizes.
(1) My default is Calibri 11 (unchanged from Microsoft default) using Outlook 2016.
(2) Creating Outlook Emails from Excel with the .Body command uses Font Calibri 12.
(3) Creating Outlook Emails from Excel with the .HTMLBody command and no signature uses Font Times New Roman 12.
(4) Creating Outlook Emails from Excel with the .HTMLBody command and signature uses Font Times New Roman 12 for the first paragraph and font Calibri 10 for other paragraphs.

The good news was that I was able to workaround the problem using some HTML code to control the font name and the font size in the body, when a signature was used.

See the attached demo file, which contains the following updated VBA code. Items of interest are in red.

Hard Coded Font Name and Font Size (Module ModOutlookBodyVsHTMLBody):

Please Login or Register  to view this content.

Font Name and Font Size as variables in the code (Module ModZTest):

Please Login or Register  to view this content.

Font Name and Font Size as variables in the code with values obtained from the Spreadsheet (Module ModZTest):

Please Login or Register  to view this content.

Lewis

Thank you so much.

I will use the second option you presented. I have multiple users for the code, so I need to keep the format flexible. Since I already have cells prepared to adapt according to each user (I have a userform when the excel opens to indicate the user - so it changes the default paths to linked workbooks), I will simply have to write the default formats.

Once again, thank you very much. Not only did you help to solve the situation, but also provided a way to learn why I was having difficulties.

All the best.