Excel / Visual Basic / Outlook Attachments

[Ryan]

Is there a way using Excel Visual basic that a
spreadsheet that you are working on can be saved and then
sent as an attachement? I have learned how to open
Outlook and send a text email using Excel Visual Basic,
but have not been able to determine how to send an
attachment. Any help would be greatly appreciated.

 

[Bob Phillips]

Ryan,

Here is some code that includes an attachment. Most of it should be familiar
to you, and the rest can be worked into yours.

Dim objOutlook As Object
Dim objMailItem As Object
Dim objRecipient As Object
Dim objNameSpace As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
objNameSpace.Logon , , True

Set objMailItem = objOutlook.CreateItem(0)
Set objRecipient =
objMailItem.Recipients.Add("(e-mail address removed)")
objRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
objMailItem.Subject = "The extract has finished."
objMailItem.Body = "This is an automatic email notification"
objMailItem.Attachments.Add (Filename)
objMailItem.Display 'change to .Send if you don't want to interact

One thing to note, the attachment must be a disk file, so you will need to
save the workbook, and attach that file.

--

HTH

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