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