PDA

View Full Version : Help With "Email Activesheet in Body of Mail Using Default Mailer"



jasono
03-11-2011, 08:23 PM
Came across this post by "johnske" on this website and it was exactly what I was looking for...however I can't get it to work...

I'm trying to send an excel worksheet through Outlook but I don't want to lose formatting. Also want to send charts and graphs. When I try the example below I get a Run-time error '1004': Method'EnvelopeVisible' of object'_Workbook' failed error.

I've read on another post on a different topic that if I don't have compatible versions of Excel and Outlook (I have Excel 2003 and Outlook 2007) that the procedure won't work.

This VBA beginner is begging for help...this is for a report that I want to start sending to my employees. I have attached the example from the post below that I downloaded the code into but it doesn't work.

Thanks to anyone who can help...

Because I'm new I can't post the hyperlink to the thread on this website. I have attached the example spreadsheet that johnske provided that has the code.

Here is the description of the post by johnske that I can't get to work...

A common question in Help forums is "how to automate emailing the active sheet in the body of an email". The usual solution given utilizes Outlook, and the solution given there involves a process of deleting all shapes and converting values to text, needless to say, charts can't be sent in this manner. So if the sheet to send relies heavily on formatting, charts, images, and shapes for presentation purposes, all of that is lost... In addition, if you don't use Outlook as your default emailer and you're using that code you may think that you have emailed it - but you haven't - it's actually just sitting in the Outlook outbox waiting for you to open Outlook so it can be sent.... Excel provides an inbuilt tool that circumvents all these difficulties, but this requires you to click a few buttons on your toolbar to activate the process, which is often not considered adequate when the process is to be fully automated and activated by code. This entry uses code that "clicks the buttons" for the user and sends the email. The only variation from 'normal' usage here is that the recipients(s) can't be written into the code itself, however, we can make use of the fact that once it's been saved, the workbook remembers the last recipients that the workbook was sent to and automatically sends it to the same recipients every time the code is actuated. What this means is that there is actually no need to write the recipients name into the code, instead, the recipients are written in the "To:" field of the mailer the very first time it is sent. When the code is actuated after that, it gives the options to i) use the same recipients, ii) ALWAYS use the same recipients without asking again, or, iii) to change them in the To: field whenever the code is actuated. It's suggested that this code be placed in Personal.xls and used whenever you wish to send the active sheet in any active workbook. SPECIAL NOTE: Any hyperlinks used for navigating about the active sheet will still work on the emailed sheet



:dunno

Rob342
03-12-2011, 07:03 AM
Jason

Refer to post below works with outlook 2003,2010 might be of some help ?

http://www.vbaexpress.com/forum/showthread.php?t=36336