Sending Email

**katmando** · 03-02-2006, 12:12 PM

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\BaseBookingInReport.xls"
'End If
'Sets in expiriy time, just a housekeeping thing
objmessage.ExpiryTime = Date + 2
objmessage.Display
End Sub

Ron de Bruin · 03-02-2006, 12:25 PM

Hi katmando

See
http://www.rondebruin.nl/sendmail.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl

"katmando" <[email protected]> wrote in message
news:[email protected]...
>
> 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\BaseBookingInReport.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
>

**katmando** · 03-03-2006, 07:25 AM

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

Les Stout · 03-03-2006, 08:25 AM

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 = "[email protected]"
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 ***

**katmando** · 03-06-2006, 10:01 AM

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

Ron de Bruin · 03-06-2006, 12:15 PM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

**katmando** · 03-06-2006, 01:01 PM

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