Hi
I am experimenting with Ron de Bruin's "Sending mail from Excel with
CDO" and so far it has worked.


With iMsg
Set .Configuration = iConf
.To = "
.CC = ""
.BCC = ""
.From = """Andrew"" "
.Subject = "Progress Report"
.TextBody = strbody

"The line below caused an error"
.addattachment ThisWorkbook.Sheets(2)

"The line below worked"
'.addattachment "C:\Users\Andrew\Desktop\Email example that
works.xls"

.Send
End With



I would like to be able to email a worksheet from the current active
workbook and I tried the line below

..addattachment ThisWorkbook.Sheets(2)

but it gave a type mismatch error.


Help on this greatly appreciated please.

Andrew B

The full path is required for attachments.


"Andrew Bourke" wrote in message
...
Hi
I am experimenting with Ron de Bruin's "Sending mail from Excel with CDO"
and so far it has worked.


With iMsg
Set .Configuration = iConf
.To = "
.CC = ""
.BCC = ""
.From = """Andrew"" "
.Subject = "Progress Report"
.TextBody = strbody

"The line below caused an error"
.addattachment ThisWorkbook.Sheets(2)

"The line below worked"
'.addattachment "C:\Users\Andrew\Desktop\Email example that
works.xls"

.Send
End With



I would like to be able to email a worksheet from the current active
workbook and I tried the line below

.addattachment ThisWorkbook.Sheets(2)

but it gave a type mismatch error.


Help on this greatly appreciated please.

Andrew B

Hi Andrew,

You need to save the worksheet to a separate workbook. There is an example
at the following link.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=326


--
Regards,

OssieMac

JLGWhiz wrote:
The full path is required for attachments.


"Andrew Bourke" wrote in message
...
Hi
I am experimenting with Ron de Bruin's "Sending mail from Excel with CDO"
and so far it has worked.


With iMsg
Set .Configuration = iConf
.To = "
.CC = ""
.BCC = ""
.From = """Andrew"" "
.Subject = "Progress Report"
.TextBody = strbody

"The line below caused an error"
.addattachment ThisWorkbook.Sheets(2)

"The line below worked"
'.addattachment "C:\Users\Andrew\Desktop\Email example that
works.xls"

.Send
End With



I would like to be able to email a worksheet from the current active
workbook and I tried the line below

.addattachment ThisWorkbook.Sheets(2)

but it gave a type mismatch error.


Help on this greatly appreciated please.

Andrew B



Hi
Thanks for the response - I'm still not sure how to insert the full path
name for the worksheet.

something i threw together, i think it will work enough to give you an idea.
it will create a new workbook with whatever is on sheet1, save it to the
same path as your existing workbook, load outlook and attach it.

you can fill in variables in the code to fill the various fields outlook.



Option Explicit
Sub test()
Dim ws3 As Worksheet
Dim wb As Workbook
Dim wbNew As Workbook
Dim ws4 As Worksheet
Dim fPath As String
Dim FName As String
Dim OutApp As Object
Dim OutMail As Object
Dim strBody As String

Dim NewSheet As Worksheet
Set wb = ThisWorkbook
Set wb = ThisWorkbook
Set ws4 = wb.Worksheets("Sheet1")

strBody = "This is a test."
fPath = ThisWorkbook.Path & "\"
FName = "Your File Name.xls"
ws4.Copy

ActiveWorkbook.SaveAs Filename:=fPath & FName,
ReadOnlyRecommended:=False
Set wbNew = ActiveWorkbook
wbNew.ActiveSheet.Name = "Summary"

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Your Subject"
.Body = strBody
.Attachments.Add fPath & FName
.Display
End With

Xit:
Set OutMail = Nothing
Set OutApp = Nothing
End Sub




--


Gary Keramidas
Excel 2003


"Andrew Bourke" wrote in message
...
Hi
I am experimenting with Ron de Bruin's "Sending mail from Excel with CDO"
and so far it has worked.


With iMsg
Set .Configuration = iConf
.To = "
.CC = ""
.BCC = ""
.From = """Andrew"" "
.Subject = "Progress Report"
.TextBody = strbody

"The line below caused an error"
.addattachment ThisWorkbook.Sheets(2)

"The line below worked"
'.addattachment "C:\Users\Andrew\Desktop\Email example that
works.xls"

.Send
End With



I would like to be able to email a worksheet from the current active
workbook and I tried the line below

.addattachment ThisWorkbook.Sheets(2)

but it gave a type mismatch error.


Help on this greatly appreciated please.

Andrew B

There is a code example for the activeworkbook / or worksheet on my CDO page
http://www.rondebruin.nl/cdo.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Andrew Bourke" wrote in message ...
Hi
I am experimenting with Ron de Bruin's "Sending mail from Excel with
CDO" and so far it has worked.


With iMsg
Set .Configuration = iConf
.To = "
.CC = ""
.BCC = ""
.From = """Andrew"" "
.Subject = "Progress Report"
.TextBody = strbody

"The line below caused an error"
.addattachment ThisWorkbook.Sheets(2)

"The line below worked"
'.addattachment "C:\Users\Andrew\Desktop\Email example that
works.xls"

.Send
End With



I would like to be able to email a worksheet from the current active
workbook and I tried the line below

.addattachment ThisWorkbook.Sheets(2)

but it gave a type mismatch error.


Help on this greatly appreciated please.

Andrew B

Thanks Ron - I've managed to get it to work!


Ron de Bruin wrote:
There is a code example for the activeworkbook / or worksheet on my CDO
page
http://www.rondebruin.nl/cdo.htm

Thanks for that - I will give it a try.


Gary Keramidas wrote:
something i threw together, i think it will work enough to give you an
idea. it will create a new workbook with whatever is on sheet1, save it
to the same path as your existing workbook, load outlook and attach it.

you can fill in variables in the code to fill the various fields outlook.



Option Explicit
Sub test()
Dim ws3 As Worksheet
Dim wb As Workbook
Dim wbNew As Workbook
Dim ws4 As Worksheet
Dim fPath As String
Dim FName As String
Dim OutApp As Object
Dim OutMail As Object
Dim strBody As String

Dim NewSheet As Worksheet
Set wb = ThisWorkbook
Set wb = ThisWorkbook
Set ws4 = wb.Worksheets("Sheet1")

strBody = "This is a test."
fPath = ThisWorkbook.Path & "\"
FName = "Your File Name.xls"
ws4.Copy

ActiveWorkbook.SaveAs Filename:=fPath & FName,
ReadOnlyRecommended:=False
Set wbNew = ActiveWorkbook
wbNew.ActiveSheet.Name = "Summary"

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Your Subject"
.Body = strBody
.Attachments.Add fPath & FName
.Display
End With

Xit:
Set OutMail = Nothing
Set OutApp = Nothing
End Sub