Fonts and Formatting in Excel generated email

I am working with Ron de Bruin email code (see below) that emails a single worksheet in the body of an email. This macro works great generating a form from Excel 2007 to Outlook 2007. However, I lose all of the formatting of the Excel form when it is displayed in Outlook and I would like to know if there is a way to adjust the formatting in the macro so I can maintain the fonts and colors from the Excel worksheet?

Please Login or Register  to view this content.

Hi,

You can apply the formatting to the text within the ".HTMLBody" line of your code. In here you will use HTML syntax to set these attributes, such as

Please Login or Register  to view this content.

to make the signature appear in italics, or

Please Login or Register  to view this content.

to apply the italics to the signature and make the entire body of text appear in red.

If you don't know your HTML syntax, then now would be a good time to learn. I would suggest heading on over to the w3schools website to get yourself started with some simple HTML to allow you to apply some formatting to your email body.

I hope this helps

EDIT: You also lose all instances of "vbNewLine" in your message if you have these present within the string. The way around this is to break the string into multiple strings (1 string per line), and use the HTML line break "<br>" to set the line breaks within the body of your email.

ajryan88,

Thank you for your response. The codes below only work on the signature block and NOT the body for the email. I need the body of the email to maintain the formatting from the Excel form, is this possible?

Thank you for your help!

Please Login or Register  to view this content.

Yes my code was an example of what you will need to change. So to change the formatting of the body of your email, wrap some HTML tags around the message rather than the signature

ajryan88,

Could you show me some examples of how you would format the .HTMLBody RangetoHTML(rng) with a particular font. I have reviewed the W3Schools website and was unable to make any of the formatting work for this situation, it would always work for the Signature block no matter where I placed the HTML tags.

Thank you for your help.

Sure, like this:

Please Login or Register  to view this content.

I hope this is more clear to you now

ajryan88,

The examples you provided would not work for with the .HTMLBody RangetoHTML(rng) line of code. It always changes the Signature block. Could you please demonstrate how I could get information that is located in a form and when I run the code listed above it would import the data from the form into an email that it would maintain a particular font. I am not using strLine1, strLine2, strLine3 as the as the data that is sent in the email changes based on the user inputs.

Thank you for your help.

Hi,

The reason it isn't working for you is because you are using the RangeToHTML subroutine, which was designed by Ron de Bruin to convert values from a range to a HTML format in a single block. It does not allow formatting. So instead of using this subroutine, try simply saving the results of the inputs to single variables (such as strLine1, etc.) and formatting them as demonstrated above.

ajryan88,

The only way I know to insert the text from the user form and into the body of the email is to use the .HTMLBody RangetoHTML(rng) line of code. This form will vary in length and input depending on the total number of tracking numbers and item numbers the end user will enter. If you have a way I can incorporate the same information from the user form into the body of an email; I am always open to suggestions.

Thank you for Excelling with me.

Hi,

Could you please upload your workbook with an example of a typical input into the userform, along with how you would like those inputs formatted in an email (in a Word document is fine for this part).

Thanks

ajryan88,

Attached is a sanitized copy of my spreadsheet and a Word document of how I wanting the email to look.

Thanks for help

Hi,

Insert this line of code:

Please Login or Register  to view this content.

after this line of code:

Please Login or Register  to view this content.

in the RangetoHTML function.

Hope this helps

ajryan88,

This did not work for me!

Thank for your help

That's strange...it worked fine for me.

Old Screenshot - Old Screenshot.PNG
New Screenshot - New Screenshot.PNG

Here is your original file with my single line of code inserted - TrackingEmails.xlsm

Hope this works

ajryan88,

I can make it go to Arial, Century Gothic but I cannot get it to go as Times New Roman. Any way, I have convinced them to use Century Gothic and that works.

Thank you for all your help.

Hi, that is very strange. Possibly not an issue related to the code then...

Glad you convinced them on something that does work though.

You're very welcome, happy to help

Have a great day