E-Mail a worksheet by clicking a button in that worksheet

[Davers]

Hi everyone, this is my first post. First, let me say I've searched on my subject, and found roughly 2000 references to it. However, none of them seem to work. I've also posted to other newsgroups to no avail. Understand, I am by no means a guru of excel, in fact, I am pretty new to it. I feel I have a basic understanding of it. With that in mind, here is my dilemma:

I have quite a few workbooks I am responsible for. All of them need to be e-mailed to different people at various times. I would like to be able to add a button on the worksheet that needs to be e-mailed that when pressed, it puts that worksheet as an attachment in outlook. Let's just assume I haven't renamed my worksheet, it is still worksheet1. Here is what I've tried so far:
Sub mailer()

Dim objOut As Outlook.Application
Dim objMess As Outlook.MailItem


Set objOut = CreateObject("Outlook.Application")
Set objMess = objOut.CreateItem(olMailItem)


strto = "dmorri18@ford.com"
strsub = "test of mail alert" 'can be any text value
strbody = CStr(Now) 'can be any text value

With objMess
.To = strto
.Subject = strsub
.Body = strbody
.Display
.Attachments.Add ("C:cool.txt")
'.Send

End With

End Sub

It gives me all kinds of errors, and I assume I have to save my worksheet as "cool.txt" on my C drive??? I really need it to just attach the worksheet I am currently in when I press the button. Or do I need to save it first? HELP!!! I have been working on this believe it or not, ALL DAY! :-/ I just can not figure it out...

Thanks in advance,

Dave Morrison...the unhappy
Workforce Planning Analyst

 

[kskinne]

whenever I try to use this VBA code:

Sub send_mymail()
ActiveWorkbook.SendMail Recipients:="username@domaincom"
End Sub

I get the following error msg:

"The file could not be accessed. Try one of the following: make sure the specified folder exists, make sure the folder that contains the file is not read only, make sure the file name does not contain any of the following characters <>?[]:| or *, make sure the file/path name doesn't contain more than 218 characters."

Then after I close this msgbox, I get the following error msg:

"EXCEL.exe has generated errors and will be shut down. Your will have to restart your program. An error log is being created."

I don't understand why I am getting this error message because my file does not meet any of the criteria mentioned in the first error msg. Any ideas?

Thanks
kevin