Send Outlook Attachment within Excel

  • Thread starter Thread starter JR
  • Start date Start date
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
 
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("(e-mail address removed)")
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)
 
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
 
Bob may I ask a favour?
Could you modify your code in order to be used by MS Outlook Express?
TIA
 
Back
Top