Send Outlook Attachment within Excel

J

JR

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
 
B

Bob Phillips

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("[email protected]")
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)
 
J

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
 
M

Martyn

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