Send Email with sheet as body

[astrevens]

Plase can someone help.
I need to send the contents of an excel worksheet via email exactly how you do it if you select: file>Send To>Mail Recipient.

Im using excel xp and need to code this in vba. Ive tried the sendmail function but this sends the whole thing.

Many thanks
Andy

 

[xlbo]

I havn't got XP but I know you can send 1 sheet by email - look at the VBA help for sendmail - should be one of the optional arguments I'd've thought Rgds
~Geoff~

 

[astrevens]

sorry it only says bout the workbook method. im really pullin out me hair here!

 

[xlbo]

try typing send worksheet in the help wizard (VBA, not excel) Rgds
~Geoff~

 

[astrevens]

sorry mate, nothing there about it. if no one can help me on that perhaps someone could tell me how to open an outlook new mail message, copy the excel sheet and paste onto the mail message and send it?

 

[acron]

Try the following code, which shoul dsend the active sheet to 'Recipient', which hcan be replaced with a valid email address.

A new workbook is created with just one sheet and mailed. It is then closed without saving.

Sub MailActiveSheet()
    ActiveSheet.Copy
    With ActiveWorkbook
        .sendmail Recipients:="Recipient"
        .Saved = True
        .Close
    End With
End Sub

A.C.

 

[astrevens]

That works ok but if you actually go into xp and select file>send to>mail recipient (not "as attachement&quot. This actually puts the data into html format then into the body of the email itself.
I can make a new mail message from excel vba but i can't figure out how to get the sheet into the body of the email.

 

[xlbo]

Record yourself doing the steps you mention above - then look at the macro that is produced Rgds
~Geoff~

 

[astrevens]

tried that also and nothing shows up in the code!!!

 

[Guest_imported]

i sympathise- this is exactly what i want to do! i cant- u solve it?

 

[Adamba]

Have you tried the sendkeys statement?
Its not the most efficient way of doing it and if its run while another programs ontop it wont send the keys to excel without more coding.
Adam

 

[astrevens]

nah, thats too messy. this needs to work on most of the computers in the building!!
I can't beleive microsoft would build in something as useful as this but not let u code with it!!!
someone must know!!!