Davers
Well-known Member
- Joined
- Sep 17, 2002
- Messages
- 1,165
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
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