How can I send a sheet in an Automated email with VBA?

I posted this question here originally http://www.excelforum.com/excel-prog...ml#post4128075 and realized that What I am asking may not be clear so I am basically starting over with the original unmodified codes.

I have a macro (1st code below) that after the "Before" sheet has been updated, it will create a new sheet named "End of Shift Report" and copy the headers over from the before sheet. Then it searches in column A for currentuser and then column B for current date. Once it finds those matches, it pastes only those rows to the end of shift report sheet. All of this works absolutely wonderful.

I have a RangetoHTML function (2nd code below) that I would like to put the end of shift report sheet into the body of the email as HTML. All it does right now is send a blank email with a blank attachment. I don't want an attachment, I want ot to be placed in the email body.

1st code (macro)

Please Login or Register  to view this content.

2nd code (function)

Please Login or Register  to view this content.

Is there a way to integrate the macro code into the function code? Or am I going about this all wrong?

Anyone have an idea of what I can do here, or if its even possible?

Gregor Y,
Thank you for you assistance with this, I really do appreciate it. I have run into some issues with the above code and am having problems figuring them out. at the following line, I get a Compile error: Sub or Function not defined,

Please Login or Register  to view this content.

With the RangetoHTML part selected. I have tried to declare is as String, Object, Variant, Range, pretty much everything I can think of which does allow the Function Macro1a code to run, After the function runs and the new sheet is created and it returns to the Private sub I get a Run-Time Error 91: Object variable or with block variable not set (at the above line). I have also tried to add With before the EmailHTMLBody line but that doesn't work either. My knowledge of VBA is very, VERY limited so now I am at a loss. Also, If I do get it to go past that line, it runs the finction and tries to create the sheet again, which of course errors out as 2 sheets can not have the same name.
If I comment out the line, I get errors in the function code........

That didn't work either. I'm probably doing something on my end that is wrong. With the Private Sub and Function codes that you provided me, how do I call them? What I did was change the Private Sub Macro1a_Example() to Private Sub CloseButton_Click(), as that is how my original macro.functions were called. SO, with you last changes, this is how the codes look...

Please Login or Register  to view this content.

I still get a Compile error: Sub or Function not defined with RangetoHTML(r) highlighted in blue.

Edit* I had removed the original RangetoHTML code that I had. I put it back in and now I get another error - Compile error: Expected Function or variable with EmailHTMLBody highlighted in blue

I did try but lost where I am because now I have about 8 different codes/functions and don't know what to keep, delete, change or whatever it is I am supposed to do. I'm thinking that I might just have to start all over from a blank workbook, which is the worst option available to me...I think. I will have to try this tomorrow. I leave work in less than an hour. Again, thank you for your help with this. Even if we cant get it figured out (which is probably because of something I've done, or will do lol), I still appreciate you helping me.

Thank you, I will check this out first thing tomorrow morning.

Gregor Y,
Thank you for the code, I did get it to work with some minor tweaks. However, I am getting an odd MsgBox that I haven't seen before. I am assuming it is supposed to pop up per the Public Sub CloseButton_Click() code. If this is the case, then all is well?? Now, If I replace your Public Sub CloseButton_Click() code with my email code, I get a different error that I had before and had it figured out, now I have to figure it out again Oh happy happy joy joy.

Gregor Y,
On my way to JUMPING for JOY!!!!!!! After a lot of trial and error with putting the right codes in after you supplied me with the clean version, it is finally doing what I want it to do with one kind of minor but important exception. The format in the email isn't correct, see attached Sheet 1 for an explanation. The sample on the left is exactly what is being sent in the email (YAY! for getting this part finally), the sample on the right is the way it should be sent.
Gergor Y clean code Formatting issue.xlsx
It doesn't seem to be auto adjusting the cells according to the text in the cells. If I need to create a new thread for this, let me know.

Update* I figured out how to get the date column A to adjust by adding

Please Login or Register  to view this content.

into the code I am using, but still need the last column, column F to auto adjust to the text in the cell as it will vary each time.

Shazam, boo-ya!, yippie, yee-haw, and all other words of excitement! I got everything to this point figured out. I found the code below

Please Login or Register  to view this content.

to auto adjust the cells and it works great for all sheets when placed in the workbook.
I also discovered that I needed to delete the new sheet that was created as the workbook is being saved on a server and is updated daily so we didn't need all the extra sheets that we were going to be getting. It took me a little tinkering, but, I got it to work with the code below

Please Login or Register  to view this content.

Thank you again Gregor Y! The next time I am in CO. or you are in Tx., I owe you a beer or 12. I have been working on this (I hate to admit) on and off since November, Even our IT department couldn't get it figured out. Once you gave me the "clean" code everything fell into place. I am marking this as SOLVED.......FINALLY!!!!!!!!!
P.S. I know that once this goes "live" at work, I will have 'macros not enabled issues' for some/all end users, but, I will post another thread for that when I get to that hurdle. Sheesh, hopefully it's just a hurdle and not another gigantic bridge!
Again, THANK YOU Gregor Y!

Thanks for the updated code and the info. I will check with out IT dept. and find out what PKI we have, if any.
When I get back to work tomorrow, I will look through the code to see if/where I have set things back to True. In the CloseButton_Click event I have code toward the end that deletes the new sheet automatically. I set the DisplayAlerts to false so the default message wouldn't pop up asking them if they were sure they wanted to delete the sheet. Or, I think I will just save it as another workbook, add your updated code to it and see how things go.

Again, many, many, MANY thanks for helping me with this.