Attach Open Excel Spreadshee to Email

[BillyRogers]

I'm trying to send an open spreadsheet as an attachment from code in Outlook
2003. I tried using the excel command SendMail but that triggers a security
warning.

The code fails when I try to add the workbook as an attachment.


Sub temp()

Dim xlApp As New Excel.Application
Dim appwbook As Excel.Workbook
Dim appwsheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set appwbook = xlApp.Workbooks.Add
Set appwsheet = appwbook.Worksheets(1)
xlApp.Visible = True


Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem

Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)

objMail.BodyFormat = olFormatHTML
objMail.Recipients.Add ("(e-mail address removed)")
objMail.Subject = "this is the subject"

objMail.Attachments.Add (appwbook) ' ** this line doesn't work!!!

objMail.Display

End Sub
--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003

 

[JLGWhiz]

Based on the following, which was copied from Outlook VBA help, looks like
you need the complete path for your Attachments.Add statement.

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
Set myAttachments = myItem.Attachments
myAttachments.Add "C:\My Documents\Q496.xls", _
olByValue, 1, "4th Quarter 1996 Results Chart"

The last line is the one I was referring to.

 

[JLGWhiz]

After reading your post a little closer, I'm not sure you can send an open
file as an attachment. That is probably why you got the security warning
when you tried it from the menu.

 

[Karim Benabd]

Try:

Add a reference to Microsoft Outlook in VBE (Tools > References).

Add this function to a module

Function msgA(mySubject, myBody, myTo, myCC, myAttachment)
Dim OutlookApp As Object
Const olMailItem = 0

Set OutlookApp = CreateObject("Outlook.Application")
With OutlookApp.CreateItem(olMailItem)
.Subject = mySubject
.Body = myBody
.To = myTo
.cc = myCC
.Attachments.Add myAttachment
.Send
End With
Set OutlookApp = Nothing
End Function

In your userform, add a button and in the click event add the following
lines:
Subject = "Test"
Body = "Attached Excel file"
A = "(e-mail address removed)"
CC = "(e-mail address removed)"

msgA Subject, Body, A, CC, ActiveWorkbook.FullName

This is working for me with Excel 2000 pro.

NB. The code snippet is not mine. But I do not remember where I picked
from Internet.

I hope this helps.

Regards,
Karim

 

[Karim Benabd]

Try:

Add a reference to Microsoft Outlook in VBE (Tools > References).

Add this function to a module

Function msgA(mySubject, myBody, myTo, myCC, myAttachment)
Dim OutlookApp As Object
Const olMailItem = 0

Set OutlookApp = CreateObject("Outlook.Application")
With OutlookApp.CreateItem(olMailItem)
.Subject = mySubject
.Body = myBody
.To = myTo
.cc = myCC
.Attachments.Add myAttachment
.Send
End With
Set OutlookApp = Nothing
End Function

In your userform, add a button and in the click event add the following
lines:
Subject = "Test"
Body = "Attached Excel file"
A = "(e-mail address removed)"
CC = "(e-mail address removed)"

msgA Subject, Body, A, CC, ActiveWorkbook.FullName

This is working for me with Excel 2000 pro.

NB. The code snippet is not mine. But I do not remember where I picked
from Internet.

I hope this helps.

Regards,
Karim

 

[Karim Benabd]

Try:

Add a reference to Microsoft Outlook in VBE (Tools > References).

Add this function to a module

Function msgA(mySubject, myBody, myTo, myCC, myAttachment)
Dim OutlookApp As Object
Const olMailItem = 0

Set OutlookApp = CreateObject("Outlook.Application")
With OutlookApp.CreateItem(olMailItem)
.Subject = mySubject
.Body = myBody
.To = myTo
.cc = myCC
.Attachments.Add myAttachment
.Send
End With
Set OutlookApp = Nothing
End Function

In your userform, add a button and in the click event add the following
lines:

Subject = "Test"
Body = "Attached Excel file"
A = "(e-mail address removed)"
CC = "(e-mail address removed)"

ActiveWorkbook.Save 'must save before sending

msgA Subject, Body, A, CC, ActiveWorkbook.FullName

This is working for me in Excel 2000 pro.

NB. The code snippet is not mine. But I do not remember where I picked
it from Internet.

I hope this helps.

Regards,
Karim

 

[BillyRogers]

JLGWhiz,

It looks like I'm going to have to save each workbook. The sendmail command
will send an open workbook that hasn't been saved but it triggers warnings
which I want to avoid.

Karim--thanks for the suggestion but this code is already in outlook and
there is no userform and I've already tried the attachments.add method. It
looks like it only accepts a path to a workbook.
--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003

 

[Karim Benabd]

Try:

Add a reference to Microsoft Outlook in VBE (Tools > References).

Add this function to a module

Function msgA(mySubject, myBody, myTo, myCC, myAttachment)
Dim OutlookApp As Object
Const olMailItem = 0

Set OutlookApp = CreateObject("Outlook.Application")
With OutlookApp.CreateItem(olMailItem)
.Subject = mySubject
.Body = myBody
.To = myTo
.cc = myCC
.Attachments.Add myAttachment
.Send
End With
Set OutlookApp = Nothing
End Function

In your userform, add a button and in the click event add the following
lines:

Subject = "Test"
Body = "Attached Excel file"
A = "(e-mail address removed)"
CC = "(e-mail address removed)"

ActiveWorkbook.Save

msgA Subject, Body, A, CC, ActiveWorkbook.FullName

This is working for me in Excel 2000 pro.

NB. The code snippet is not mine. But I do not remember where I picked
it from Internet.

I hope this helps.

Regards,
Karim

 

[Karim Benabd]

After reading your post a little closer, I'm not sure you can send an open
file as an attachment. That is probably why you got the security warning
when you tried it from the menu.