Help sending active sheet to email list

[Kingtriotn]

I am trying to send an active sheet to a set of different email
address's and I keep geting error messages. Here is the code I am
trying to use

Sub mail()

Dim olApp As Outlook.Application
Dim olMail As MailItem

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

ActiveSheet.Copy

ActiveSheet.SaveAs ActiveSheet.Path & "\" & _
"Sheet2.xls"

With olMail
..Recipients.Add "(e-mail address removed)"
..Recipients.Add "(e-mail address removed)"
..Recipients.Add "(e-mail address removed)"
..Subject = "Weekly Recap"
..Body = "Here is this weeks recap"
..Attachments.Add ActiveSheet.FullName
..Display
End With

ActiveSheet.Close False

Kill Sheet.Path & "\" & "Sheet2.xls"

Set olMail = Nothing
Set olApp = Nothing

End Sub
I am really new to Macro writing so I am sure I am doing this all
wrong. Am I at least in the ballpark? Another problem I may face is
that the active sheet that I want to send gets data from other sheets
so I need to find a way to send it witout error messages in the fields
that data from other sheets goes.
Is there hope?
Thanks in advance for any help

 

[Trevor Shuttleworth]

Why not go to Ron de Bruin's web site ? Ron has lots of examples and code
which will help you do this without re-inventing the wheel.

http://www.rondebruin.nl/sendmail.htm

Regards

Trevor

 

[Kingtriotn]

Thanks, I tried the mail one sheet example and it works fine from home.
I then emailed the workbook that I am developing to work and when
tried to run it, I get an error that higlights the body text when
debug. The error message says somthing to the tune of bad sendmai
type. Any ideas? All of my other macros worked fine on the emaile
version.
Thanks in advance,
Kingtrito

 

[Ron de Bruin]

which example ?

 

[Kingtriotn]

this is the example that I used.

Sub Mail_ActiveSheet()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
..SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
..SendMail "(e-mail address removed)", _
"This is the Subject line"
..ChangeFileAccess xlReadOnly
Kill .FullName
..Close False
End With
Application.ScreenUpdating = True
End Sub
The part where the subject goes is what is getting highlighted in
debuger.
Thanks

 

[Ron de Bruin]

Look at the example on my website again you see that there is a dot before
SendMail and other lines to refer to WB.
Post back if you got problems

Sub Mail_ActiveSheet()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail "(e-mail address removed)", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub