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

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 :)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Davers

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

New Member
Joined
Sep 5, 2002
Messages
11
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

Well-known Member
Joined
Sep 17, 2002
Messages
1,165

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
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

Well-known Member
Joined
Sep 17, 2002
Messages
1,165

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
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

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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
 

Forum statistics

Threads
1,144,210
Messages
5,723,047
Members
422,476
Latest member
beck85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top