Using code to email a sheet but need to strip the code before sending

[dcgrove]

Hi, I am using the code below that I got off of these forums to email a particular sheet in my workbook, but I need to strip all of the VBA code and the command button from the sheet being sent. Is this possible?

Please Login or Register  to view this content.

Thanks!
Clayton Grove

[Leith Ross]

Hello Clayton,

Can you post a sample workbook? It would be easier and faster to answer you questions with it.

[dcgrove]

Here you go! The button and the sheet I am trying to email is the "CRW Spreadsheet to AR" tab.

Thanks!

Clayton Grove


http://www.4shared.com/file/83957410..._Template.html

[Leith Ross]

Hello Clayton,

I have worked all day on this workbook trying to get the email to work. You are SOL on this one. No matter what I do, it will not work. The same code worked on other workbooks. This file is demon spawn and should be cast in the fires of eternal damnation! Sorry, I got nothing.

[dcgrove]

wow.... I am not quite sure what to say... If you can't do it I am fubar'ed. Thanks for giving it a shot.



Clayton

[Leith Ross]

Hello Clayton,

I thought perhaps it was due to the links in the workbooks that was causing the problem. However, even including code to break all the links in the copy, had no effect. If you don't need the formulas to be functional, we have a chance. Let me know.

[dcgrove]

All I really need is the results of the formulas on the sheet that is emailed. So the formulas do not have to be functional in the copy that is being atatched to the email.

Thanks!
Clayton

[Leith Ross]

Hello Clayton,

LOL! I should have asked before I started pulling my hair out! I had a Navy instructor who always told us "There is only one dumb question - the one you never ask." I'll start recoding it to use only the values from the worksheet. By the way, will everyone who receives the email be using Outlook?

[dcgrove]

I don't know about the people receiving but the people sending the sheets will be using outlook.

Thanks again for your help!
Clayton

[Leith Ross]

Hello Clayton,

The format will be plain text (no color, lines, etc.) just the values in rows and columns. If everyone were using Outlook, the worksheet could be emailed as is using HTML. Better to make it as universal as possible.

[dcgrove]

plain text is fine. I think the Data is imported into a database after it has been sent on so HTML would probably not work.

[Leith Ross]

Hello Clayton,

Maybe the server will let me post the workbook this time. I have been trying since late last night to get this to you. I fixed it and it is working. There is too much code to post here. I will post the workbook when the server is working. In the mean time, I could email it to you. You could then let me know how it works.

[Leith Ross]

Hello Clayton,

Here is the working version of the workbook. Perhaps this will work as a zip file. Finally, success!

[dcgrove]

Hi Leith, thank you for helping me out on this. When I click on commandbutton1 to start the macro I am getting an error that says Run-time error 1004: Method 'VBE' of object'_Application' failed.

When I debug it highlights this line:

Please Login or Register  to view this content.

Any Ideas?

Clayton

[Leith Ross]

Hello Clayton,

What version of Excel are you using?

[Leith Ross]

Hello Clayton,

I just checked Excel 2000 and the statement exists and the same version of the object library is used. The reference to the Visual Basic for Applications Extensibility object library should have been loaded when the workbook opened. Check the references in the Visual Basic Editor: Tools > References... It will tell you if something is missing.

[dcgrove]

I am using 2003.