Email from Excel

matthewh · May 14th, 2002, 07:47 AM

Can anyone tell me how to email direct from Excel using Outlook 2000 MAPI - ie so I can get the user to hit a button and the active sheet gets mailed as an attachment to 2 people - one as a BCC - without any further intervention. The only way I can see for now is...

Application.Dialogs(xlDialogSendMail).Show

Thanks

nuozek · May 14th, 2002, 08:14 AM

try

Sub Email()
ActiveWorkbook.SendMail recipients:="whatever@whatever.com"
End Sub

Iain Lewis · May 14th, 2002, 09:33 AM

Try this :

Sub aaa()
Dim OL As Object, MailSendItem As Object
Set OL = CreateObject("Outlook.Application")
Set MailSendItem = OL.CreateItem(olMailItem)
ActiveSheet.Copy
ChDir "C:WINNTTemp"
ActiveWorkbook.SaveAs FileName:="C:WINNTTemptempBook.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
filepath = ActiveWorkbook.Path
actfilename = ActiveWorkbook.Name
sendfile = filepath & "" & actfilename
With MailSendItem
.Subject = ""
.Body = ""
.To = ""
.BCC = ""
.Attachments.Add (sendfile)
.Send
End With
ActiveWorkbook.Close
Kill "C:WINNTTemptempBook.xls"
End Sub

This copies the activesheet to a new book. The new book is saved to the temp folder ( in this case in WINNT - change to suite system / user requirements ).

It then creates a new email - put what ever you need for Subject, Body etc between the "".

Finally it closes the "new" file & deletes it from the place it was saved.

matthewh · May 16th, 2002, 01:56 AM

iAN lEWIS - THANKS. iNTERESTING APP - I THINK IT'S WHAT I'M LOOKING FOR, BUT IT COMPLAINED THAT A COUPLE OF VARIABLES WERN'T DECLARED-filepath, actfilename, sendfile - CHANGED THAT - NOW IT COMPLAINS THAT THERE'S A PATH ERROR(75). THIS IS TO DO WITH ACCESSING READ-ONLY FILES OR THE OS CAN'T ACCESS THE PATH FOR SOME REASON. cAN YOU SHED ANY LIGHT? I HAVE OUTLOOK LIBRARIES AVAILABLE TO XL IN THE REFERENCES.

matthewh · May 16th, 2002, 02:02 AM

aaaaaaaaaaaRRRRRRRRRRRRRGGGGGGGGGGGHHHHHHHH!!!! *&^%$£!!!!!

IAN, I FANALLY GET IT WORKING AND THEN....

FILE DELETED
------------

Antigen for Exchange removed tempBook.xls since it
was found to be infected with W97M/MailWorm.Variant.Worm (CA(InoculateIT)) virus.

matthewh · May 16th, 2002, 02:13 AM

My appologies - can't remember who it was, someone sent me a post with a sub called sendrange.

I've tried it but only get and error box with a very descritive '400' diplayed. So what's that all about then? Press help and you get a not-so-helpful grey blank window.

Can you help?

Matt

Iain Lewis · May 17th, 2002, 02:59 AM

Hi Matthew,

Not sure what has been going wrong with the code - it's something I wrote for someone else & both he & I have not had these problems. What system are you on ?

If you want email me & I'll send you the file with the macro in.

gareth · May 17th, 2002, 06:03 AM

Absolutely right

It worked for me.Maybe his settings is wrong,what do you think?

Maybe he should try this for kicks :

Sub EMAIL1()

Mailee = InputBox("Please Enter Name to Send sheet to.........")
subj = InputBox("Please enter eMail Subject..............")
Application.Dialogs(xlDialogSendMail).Show arg1:="", arg2:="Here is the sheet as Requested"

End Sub

============
Have fun with it.

Iain Lewis · May 22nd, 2002, 01:24 AM

One problem I've found with the code code above is that it only works with Outlook 2000. The "fix" I've found is to add a .DDL file called MS09.DLL. You will also need to add the refernce to the .DLL to the VBA Reference library.

Feel free to send a person email if you'd like more details.

May 17th, 2002, 06:03 AM	#8
gareth Board Regular Join Date: Apr 2002 Location: Cape Town,South Africa Posts: 234	Absolutely right It worked for me.Maybe his settings is wrong,what do you think? Maybe he should try this for kicks : Sub EMAIL1() Mailee = InputBox("Please Enter Name to Send sheet to.........") subj = InputBox("Please enter eMail Subject..............") Application.Dialogs(xlDialogSendMail).Show arg1:="", arg2:="Here is the sheet as Requested" End Sub ============ Have fun with it. __________________ Regards Gareth Visit The Excel Maniacs :<>: Have a Chat! :<>: Free Stuff!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 14th, 2002, 07:47 AM	#1
matthewh New Member Join Date: Mar 2002 Posts: 34	Can anyone tell me how to email direct from Excel using Outlook 2000 MAPI - ie so I can get the user to hit a button and the active sheet gets mailed as an attachment to 2 people - one as a BCC - without any further intervention. The only way I can see for now is... Application.Dialogs(xlDialogSendMail).Show Thanks

May 14th, 2002, 08:14 AM	#2
nuozek New Member Join Date: Mar 2002 Posts: 16	try Sub Email() ActiveWorkbook.SendMail recipients:="whatever@whatever.com" End Sub

May 14th, 2002, 09:33 AM	#3
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	Try this : Sub aaa() Dim OL As Object, MailSendItem As Object Set OL = CreateObject("Outlook.Application") Set MailSendItem = OL.CreateItem(olMailItem) ActiveSheet.Copy ChDir "C:WINNTTemp" ActiveWorkbook.SaveAs FileName:="C:WINNTTemptempBook.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False filepath = ActiveWorkbook.Path actfilename = ActiveWorkbook.Name sendfile = filepath & "" & actfilename With MailSendItem .Subject = "" .Body = "" .To = "" .BCC = "" .Attachments.Add (sendfile) .Send End With ActiveWorkbook.Close Kill "C:WINNTTemptempBook.xls" End Sub This copies the activesheet to a new book. The new book is saved to the temp folder ( in this case in WINNT - change to suite system / user requirements ). It then creates a new email - put what ever you need for Subject, Body etc between the "". Finally it closes the "new" file & deletes it from the place it was saved.

May 16th, 2002, 01:56 AM	#4
matthewh New Member Join Date: Mar 2002 Posts: 34	iAN lEWIS - THANKS. iNTERESTING APP - I THINK IT'S WHAT I'M LOOKING FOR, BUT IT COMPLAINED THAT A COUPLE OF VARIABLES WERN'T DECLARED-filepath, actfilename, sendfile - CHANGED THAT - NOW IT COMPLAINS THAT THERE'S A PATH ERROR(75). THIS IS TO DO WITH ACCESSING READ-ONLY FILES OR THE OS CAN'T ACCESS THE PATH FOR SOME REASON. cAN YOU SHED ANY LIGHT? I HAVE OUTLOOK LIBRARIES AVAILABLE TO XL IN THE REFERENCES.

May 16th, 2002, 02:02 AM	#5
matthewh New Member Join Date: Mar 2002 Posts: 34	aaaaaaaaaaaRRRRRRRRRRRRRGGGGGGGGGGGHHHHHHHH!!!! *&^%$£!!!!! IAN, I FANALLY GET IT WORKING AND THEN.... FILE DELETED ------------ Antigen for Exchange removed tempBook.xls since it was found to be infected with W97M/MailWorm.Variant.Worm (CA(InoculateIT)) virus.

May 16th, 2002, 02:13 AM	#6
matthewh New Member Join Date: Mar 2002 Posts: 34	My appologies - can't remember who it was, someone sent me a post with a sub called sendrange. I've tried it but only get and error box with a very descritive '400' diplayed. So what's that all about then? Press help and you get a not-so-helpful grey blank window. Can you help? Matt

May 17th, 2002, 02:59 AM	#7
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	Hi Matthew, Not sure what has been going wrong with the code - it's something I wrote for someone else & both he & I have not had these problems. What system are you on ? If you want email me & I'll send you the file with the macro in.

May 22nd, 2002, 01:24 AM	#9
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	One problem I've found with the code code above is that it only works with Outlook 2000. The "fix" I've found is to add a .DDL file called MS09.DLL. You will also need to add the refernce to the .DLL to the VBA Reference library. Feel free to send a person email if you'd like more details.