Help sending active sheet to email list
 

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 "
..Recipients.Add "
..Recipients.Add "
..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


---
Message posted from http://www.ExcelForum.com/

Help sending active sheet to email list
 

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 " wrote in message
...
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 "
Recipients.Add "
Recipients.Add "
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


---
Message posted from http://www.ExcelForum.com/

Help sending active sheet to email list
 

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

--
Message posted from http://www.ExcelForum.com

Help sending active sheet to email list
 

which example ?

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Kingtriotn " wrote in message ...
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 I
tried to run it, I get an error that higlights the body text when I
debug. The error message says somthing to the tune of bad sendmail
type. Any ideas? All of my other macros worked fine on the emailed
version.
Thanks in advance,
Kingtriton


---
Message posted from http://www.ExcelForum.com/

Help sending active sheet to email list
 

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 ", _
"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


---
Message posted from http://www.ExcelForum.com/

Help sending active sheet to email list
 

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 ", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Kingtriotn " wrote in message ...
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 ", _
"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


---
Message posted from http://www.ExcelForum.com/