Email help

[Andrew Bourke]

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 = "(e-mail address removed)"
.CC = ""
.BCC = ""
.From = """Andrew"" <[email protected]>"
.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

 

[JLGWhiz]

The full path is required for attachments.

 

[OssieMac]

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

 

[Andrew Bourke]

The full path is required for attachments.

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

 

[Gary Keramidas]

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

 

[Ron de Bruin]

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

 

[Andrew Bourke]

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

 

[Andrew Bourke]

Thanks for that - I will give it a try.