Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending Email
I want to be able to send an automated email through outlook expres which attaches an excel spreadsheet. I have the following code which i used to use in outlook, but will no not work in my current set up. I've tried changing the Dim statment to look for outlook express rathe than outlook but does not seem to like this either. Do I need an add in to allow me to do this, or can anyone sugges anything else Thanks K Sub Send() 'Dim objOutlook As outlookexpress.Application Dim objmessage As outlook.MailItem Dim objAttach As outlook.Attachments 'Creates objects to send mail Set objOutlook = CreateObject("Outlook.Application") Set objmessage = objOutlook.CreateItem(olMailItem) Set objAttach = objmessage.Attachments 'Loads data into fields objmessage.To = "robert smith ; martin Gore" objmessage.Subject = "Stock Booked in By Day (DPS Report & Re Report)" 'This is the messgage in the body of email that recipients see objmessage.Body = "Dear All" & Chr(13) _ & "Please find attached the files containing stock booked i and returned by day " & Chr(13) & Chr(13) & Chr(13) _ & "Regards" & Chr(13) & Chr(13) 'Attach's file to message depending if Sunday then attach full wee file. 'If Weekday(Now) = 1 Then objAttach.Add "\\hmvnetapp1\data\finsys\daily\DPS Report.xls" objAttach.Add "\\hmvnetapp1\data\finsys\daily\RET Report.xls" 'Else 'objAttach.Ad "\\hmvnetapp1\strpublic\Finance\BaseBookingInRepor t.xls" 'End If 'Sets in expiriy time, just a housekeeping thing objmessage.ExpiryTime = Date + 2 objmessage.Display End Su -- katmand ----------------------------------------------------------------------- katmando's Profile: http://www.excelforum.com/member.php...fo&userid=2980 View this thread: http://www.excelforum.com/showthread.php?threadid=51828 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending Email
Hi katmando
See http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "katmando" wrote in message ... I want to be able to send an automated email through outlook express which attaches an excel spreadsheet. I have the following code which i used to use in outlook, but will not not work in my current set up. I've tried changing the Dim statment to look for outlook express rather than outlook but does not seem to like this either. Do I need an add in to allow me to do this, or can anyone suggest anything else Thanks K Sub Send() 'Dim objOutlook As outlookexpress.Application Dim objmessage As outlook.MailItem Dim objAttach As outlook.Attachments 'Creates objects to send mail Set objOutlook = CreateObject("Outlook.Application") Set objmessage = objOutlook.CreateItem(olMailItem) Set objAttach = objmessage.Attachments 'Loads data into fields objmessage.To = "robert smith ; martin Gore" objmessage.Subject = "Stock Booked in By Day (DPS Report & Ret Report)" 'This is the messgage in the body of email that recipients see objmessage.Body = "Dear All" & Chr(13) _ & "Please find attached the files containing stock booked in and returned by day " & Chr(13) & Chr(13) & Chr(13) _ & "Regards" & Chr(13) & Chr(13) 'Attach's file to message depending if Sunday then attach full week file. 'If Weekday(Now) = 1 Then objAttach.Add "\\hmvnetapp1\data\finsys\daily\DPS Report.xls" objAttach.Add "\\hmvnetapp1\data\finsys\daily\RET Report.xls" 'Else 'objAttach.Add "\\hmvnetapp1\strpublic\Finance\BaseBookingInRepor t.xls" 'End If 'Sets in expiriy time, just a housekeeping thing objmessage.ExpiryTime = Date + 2 objmessage.Display End Sub -- katmando ------------------------------------------------------------------------ katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803 View this thread: http://www.excelforum.com/showthread...hreadid=518286 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending Email
Thanks for that. I had found the site before but couldn't quite get the code to do what wanted. I'll play around with it a bit more Cheers K -- katmando ------------------------------------------------------------------------ katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803 View this thread: http://www.excelforum.com/showthread...hreadid=518286 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending Email
Hi K,
I use the following very succesfully. Sub email() Dim OutApp As Object Dim OutMail As Object Dim EmailAddr As String Dim Subj As String Dim BodyText As String EmailAddr = " Subj = "Your subject" BodyText = "your subject" Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .to = EmailAddr .CC = "" .BCC = "" .Subject = Subj .Body = BodyText .Attachments.Add ActiveWorkbook.FullName .Display 'or use .send End With Set OutMail = Nothing Set OutApp = Nothing End sub Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending Email
Hi Les Thats great, my only problem is that i'm using outlook express, and this code opens outlook. I can't seem to find the application name for outlook Express, to replace Set OutApp = CreateObject("Outlook.Application") Cheers K -- katmando ------------------------------------------------------------------------ katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803 View this thread: http://www.excelforum.com/showthread...hreadid=518286 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending Email
With OE it is not possible to send files and text in the body.
The only option you have is CDO http://www.rondebruin.nl/cdo.htm -- Regards Ron de Bruin http://www.rondebruin.nl "katmando" wrote in message ... Hi Les Thats great, my only problem is that i'm using outlook express, and this code opens outlook. I can't seem to find the application name for outlook Express, to replace Set OutApp = CreateObject("Outlook.Application") Cheers K -- katmando ------------------------------------------------------------------------ katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803 View this thread: http://www.excelforum.com/showthread...hreadid=518286 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending Email
Thanks Ron I had a look at your site again before and figured as much..... Managed to get a work around by using an example on your site. Great site you've got there, very helpful Cheers K -- katmando ------------------------------------------------------------------------ katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803 View this thread: http://www.excelforum.com/showthread...hreadid=518286 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending Email
Ron Just tried the code you linked to, Excellent, A lot better than my work around. Thanks again K -- katmando ------------------------------------------------------------------------ katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803 View this thread: http://www.excelforum.com/showthread...hreadid=518286 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sending a Spreadsheet as an Email Attachment vs. Imbedded in Email | Excel Discussion (Misc queries) | |||
Email sending | Excel Discussion (Misc queries) | |||
sending email using macros | Excel Programming | |||
Scheduling sending an email | Excel Programming | |||
Sending an email | Excel Programming |