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
.