excel 2000 vba 1

[UKalliance]

can any one help with an excel 2000 query. do you know the code for a macro to send a selected worksheet rather than the workbook via email?

thanks for your help
________________________________________

The Collective known as
The UK Alliance
________________________________________

 

[Gavona]

I don't think you can do it directly but simply:

Copy the sheet to a new workbook

then use Sendmail method

On Error GoTo ErrorHandler
ActiveWorkbook.sendmail Recipients:=sendto, Subject:=EmailTitle, RETURNRECEIPT:=True
On Error GoTo 0

ErrorHandler
MsgBox Prompt = "Sendmail failure for " + sendto + ". Probably invalid email address or mailbox if full"
End sub

 

[DaleWatson123321]

UkAlliance,

I believe this is the code you're looking for...

Sub EMail_CurrentSheet()
Dim File_Name As String
File_Name = ThisWorkbook.Name
ActiveSheet.Copy
ActiveWorkbook.SendMail _
Recipients:="dwatson@bsi.gov.mb.ca", _
Subject:=File_Name
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End Sub

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[UKalliance]

Thanks Dale. that has worked perfectly.

Just one more question....how would I add in a text box where you could type in the recipient email address rather than specify it in the coding itself? ________________________________________

The Collective known as
The UK Alliance
________________________________________

 

[UKalliance]

I know you can just remove the email address from the coding and leave the 2 " marks and then this will give the email and ask you to type in the TO field. ________________________________________

The Collective known as
The UK Alliance
________________________________________

 

[KenWright]

You might like to take a look at Ron De Bruins free Sendmail addin. This gives you numerous options of what to send - It's a really nice addin if you do this stuff regularly:-

Addin itself is here

http://www.rondebruin.nl/sendmail.htm#Add-in

Example code from Addin is here

http://www.rondebruin.nl/sendmail.htm#body

Regards
Ken.............