Email specific range of cells using .htmlbody

Hello Everyone,

Thank you in advance for your help with issue, it seems I’ve run into a brick wall. Please forgive the length of the post but I want to cover everything. This is a cross post from two other forums that have also attempted to resolve this.

Microsoft Discussion Groups >> English > Developer Discussions >> Office Developer >> Excel >> Excel Programming >> “Excel VBA toggle outlook ’03 Word email editor on / off”

Microsoft Discussion Groups >> English > Developer Discussions >> Office Developer >> Outlook >> Programming-VBA >> “VBA toggle Outlook ’03 Word email editor on / off”

I am using Windows XP SP2 w/ Excel & Outlook ’03. I am building some VBA code to generate a report using raw data from another system, do some formatting and then make a pivot table to sum up the data; then I’m going to copy / paste the data to another part of the worksheet (i.e. Range(“C3:D10”)) then I need the information sent in an email using HTML formatting to a set list of people.

I can do everything mentioned above EXCEPT when it comes to getting the code to send the range of cells in an email w/ using .htmlbody inside the email AND having it show the range of cells. The code I am using is adapted from Ron’s website’s using RangeToHTML (URL: http://www.rondebruin.nl/mail/folder3/mail4.htm) to send Range(“C3:D10”).

The caveat at the bottom of Ron’s page says: Note: This is not working if Word is your mail editor, you can change this setting in Outlook: Tools>Options>…Mail Format tab

The code I am devolving will be used by multiple people so I am attempting to build a workaround for the end user of having their mail setup with Word being their editor or not. If there is other code out in the land of the internet that does the same thing but is flexible enough to ignore if Word ’03 is the email editor I’m open to it but I have not come across it as of yet.

I managed to come across a way of forcing the email to start as RTF (which I thought might work for what I want) but that has turned into a dead end when it comes getting the HTML info I’m needing to show in the body of the email.

I have attached a sample worksheet with the code I am working with to show what I have so far.

Jason

Hello Jason Smith,

Welcome to the Forum!

It looks like you either edited Ron's code or didn't copy all of it correctly. You have no Outlook MailItem in your RangetoHTML macro. You have that in the macro HTMLEmailV1. You need this code to create the Outlook application and its associated objects before you can email the range as HTML.

Sincerely,
Leith Ross

Leith,

Sorry about the confusion, I am using "HTMLEmailV1" as my main function which calls "RangeToHTML".

If you run "HTMLEmailV1" from the Excel sheet and inside Outlook '03 Tools >> Options >> Mail Format >> (Header: Message format) Use Microsoft Office Word 2003 to edit e-mail messages .. that box is checked then the code will only show the Range("C3:D10") and will omit all of the text. If the box is unchecked it works like a charm.

I'm trying to find a way to force Excel / Outlook / VBA to 'check' and or 'ignore' if the box is checked and always get the text AND Range info to show on 1 email. The other two forum posts listed above was an attempt to have VBA check to see if the box is 'checked' or not then do whatever is needed for the end result.

By no means do I have my heart set on using Ron's code; its just the only code that gets me close to my end goal.

Thank you for the quick reply,

Jason

Hello Jason,

I have never written code to change that check box. I have been looking in my books and the VBA editor, but haven't found it yet.

Sincerely,
Leith Ross

Leith,

Thank you, if you happen to run into any code that does deselect the checkbox let me know If I mange to somehow figure it out (or run across the answer) I will post the resolution here.

Thank you for your time,

Jason