I'm trying to send an outlook email from within excel
using VBA. I can send a spreadsheet or a range of cells
but can't seem to figure out how to send an attachment.

I used to be able to do this just fine in Office 2000 but
now I'm using Office 2003 and many things are different
(like the reference to MsoEnvelope).

Note: the file I'm trying to send isn't the current
workbook (or any workbook)---I'm actually copying a range
of cells to a new workbook and then saving that workbook
in .csv format-----I then want to send that .csv file via
Outlook.

Any help is much appreciated.

-JR

JR,

Here is an example that uses late binding, attachments, to/cc/bcc, and
subject and body text. Not tested in 2003, but I am confident<G

Sub SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim emailDate As Date
Dim sAttachment As String

If Weekday(Date, vbSunday) = vbSunday Then
emailDate = Date - 2
ElseIf Weekday(Date, vbSunday) = vbMonday Then
emailDate = Date - 3
Else
emailDate = Date - 1
End If

sAttachment = "C:\Mytest\Text1.txt"

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient = ")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
.Subject = "Data for " & Format(emailDate, "dd mmm yyyyy")
.Body = "This is data for " & Format(emailDate, "dd mmm yyyyy")
.Attachments.Add sAttachment
.Display
End With


End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JR" wrote in message
...
I'm trying to send an outlook email from within excel
using VBA. I can send a spreadsheet or a range of cells
but can't seem to figure out how to send an attachment.

I used to be able to do this just fine in Office 2000 but
now I'm using Office 2003 and many things are different
(like the reference to MsoEnvelope).

Note: the file I'm trying to send isn't the current
workbook (or any workbook)---I'm actually copying a range
of cells to a new workbook and then saving that workbook
in .csv format-----I then want to send that .csv file via
Outlook.

Any help is much appreciated.

-JR

Bob,

works great thanks. Not sure why this doesn't appear
anywhere in the VB help of Excel. There also must be a
different way to do this since Office 03 appears to prefer
to use different objects, has a way-different library,
etc.. But thanks again!
-JR

-----Original Message-----
JR,

Here is an example that uses late binding, attachments,
to/cc/bcc, and
subject and body text. Not tested in 2003, but I am
confident<G

Sub SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim emailDate As Date
Dim sAttachment As String

If Weekday(Date, vbSunday) = vbSunday Then
emailDate = Date - 2
ElseIf Weekday(Date, vbSunday) = vbMonday Then
emailDate = Date - 3
Else
emailDate = Date - 1
End If

sAttachment = "C:\Mytest\Text1.txt"

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient = .Recipients.Add
")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these
lines with
'your names, adding to
the collection.
.Subject = "Data for " & Format(emailDate, "dd
mmm yyyyy")
.Body = "This is data for " & Format
(emailDate, "dd mmm yyyyy")
.Attachments.Add sAttachment
.Display
End With


End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JR" wrote in
message
...
I'm trying to send an outlook email from within excel
using VBA. I can send a spreadsheet or a range of cells
but can't seem to figure out how to send an attachment.

I used to be able to do this just fine in Office 2000
but
now I'm using Office 2003 and many things are different
(like the reference to MsoEnvelope).

Note: the file I'm trying to send isn't the current
workbook (or any workbook)---I'm actually copying a
range
of cells to a new workbook and then saving that workbook
in .csv format-----I then want to send that .csv file
via
Outlook.

Any help is much appreciated.

-JR



.

Bob may I ask a favour?
Could you modify your code in order to be used by MS Outlook Express?
TIA

Martyn

Outlook Express doesn't expose an object model like Outlook does. That
reduces the flexibility you have when using Outlook Express. Check Ron's
site for some good info on working with email.

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


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Martyn" wrote in message
...
Bob may I ask a favour?
Could you modify your code in order to be used by MS Outlook Express?
TIA

Thanks Dick, I'll do what you have suggested...