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

 

[Chris Davison]

Hi Dave,

http://www.mrexcel.com/board/viewtopic.php?topic=18362&forum=2

here's what Richard suggested I use, which is still working for me when I employ it

 

[Davers]

Thanks for the info...but I need a lot more information... I know absolutely nothing about Visual Basic. I really need my hand held through the entire process...I wouldn't even know where to start on my own....

Thanks,

Dave Morrison

 

[delta5555]

Sub mailsheet()

ActiveWorkbook.SendMail _
Recipients:="name@demon.co.uk", _
Subject:="" & ActiveWorkbook.Name & ""

End Sub

just paste in this code, change the email address, it works on win98 computers

 

[Davers]

Now I feel like a real idiot. That doesn't work either. The whole 3 lines of code lite up yellow....I changed the e-mail to mine...still doesn't work...sigh...

I even have 3 Excel books open in front of me...pretty pathetic hey???

Oh well...

Thanks anyway,

DaveM.

 

[Chris Davison]

On 2002-09-18 11:33, Davers wrote:
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

Dave,

yup, I hear you.....

My VBA skills are about as useful as an Iraqi weapons-inspector co-ordinator...

you see where you have all that code, just try deleting it and replacing it with the single line on my link that Richard very helpfully provided (you can cut n paste from the board)

his code says A1, where A1 on the spreadhseet houses the email address you want to send it to...... without getting into arrays or loops, I suppose you could have a list of email recipients and just repeat the code, replacing A1 with A2, then A3 each time to send it to each name on the list

I'll quit here as that's probably the best I can offer at this juncture......

good luck mate
Loads of very skilled people here

 

[Davers]

SWEET!!! Not only does it work, but I created a drop down list to choose who to send it to and voila' when I push the button, out it goes!!!! Thank you very very much!

Of course, you know I didn't get it all to work until I messed with it at home....

Thanks again,

Dave Morrison

 

[Chris Davison]

Dave,

hopefully RIchard will take a bow when he reads this thread !

(also though, can you test it when Outlook is closed.... I think mine didn't email anything once when I didn't actually have Outlook open although I couldn't swear to it.... just a thought....)

 

[Jack in the UK]

Hi these loads of diff ways heres a few - i use a spammer NO i willnot email it out doesit all for me, agro free!

ActiveWorkbook.SendMail Recipients:=Range("A1")

OR

Sub Email()
ActiveWorkbook.SendMail recipients:="Name@Email.com"
'Edit as needed
End Sub

HTH

 

[Jack in the UK]

OR this [can get stroopy thou:]got this from MS themself, dug out my OLD box of goodies!

Sub Send_Msg()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
.To = "name@domain.com"
.Subject = "Automated Mail Response"
.Body = "This is an automated message from Excel. " & _
"The cost of the item that you inquired about is: " & _
Format(Range("A1").Value, "$ #,###.#0") & "."
.Display
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub