Email Excel Spreadsheets (Excel 2000)

[agnewfarms]

Does anyone know how to email individual worksheets from an excel workbook to different recipients. I need to email leave sheets to employees from a large workbook. I want to be able to click a button or run a program that would open the workbook, read each sheet and mail it to the person on the tab. Let me know if you have any ideas or if there is a program out there that will do this. (One major problem - we have Groupwise as our email server!!!) Thanks

[LegareColeman]

You will have to write code to create a new workbook from each sheet and mail the workbooks. The code below will create the new workbooks and it shows where to put the code to mail it. However, I am not familiar enough with email to know how to do that.

<pre>Public Sub MailSheets()
Dim oBook As Workbook, oSheet As Worksheet
Dim lSaveNumSheets As Long
Dim strPath As String
lSaveNumSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
strPath = ActiveWorkbook.Path
If Right(strPath, 1) <> "" Then
strPath = strPath & ""
End If
For Each oSheet In Worksheets
Set oBook = Workbooks.Add
oSheet.Cells.Copy
oBook.Worksheets(1).Paste Destination:=oBook.Worksheets(1).Range("A1")
oBook.Worksheets(1).Name = oSheet.Name
oBook.SaveAs FileName:=strPath & oSheet.Name
oBook.Close
' code to mail goes here
Next oSheet
Application.SheetsInNewWorkbook = lSaveNumSheets
End Sub
</pre>

[Andrew Cronnolly]

The following code should work with Outlook, but I have never worked with groupwise, so cannot really say if it will suit you. Try placing it in Legare's code before the workbook is closed, and see if it helps.<pre>Workbooks("WorkBook Name").SendMail Recipients:="Recipient"</pre>

You will need to substitute appropriate names for the workbook and the recipient.

Andrew C

[LegareColeman]

Using Andrew's suggestion, this works with Outlook and Exchange:

<pre>Public Sub MailSheets()
Dim oBook As Workbook, oSheet As Worksheet
Dim lSaveNumSheets As Long
Dim strPath As String
lSaveNumSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
strPath = ActiveWorkbook.Path
If Right(strPath, 1) <> "" Then
strPath = strPath & ""
End If
For Each oSheet In Worksheets
Set oBook = Workbooks.Add
oSheet.Cells.Copy
oBook.Worksheets(1).Paste Destination:=oBook.Worksheets(1).Range("A1")
oBook.Worksheets(1).Name = oSheet.Name
oBook.SaveAs FileName:=strPath & oSheet.Name
oBook.SendMail Recipients:=oSheet.Name
oBook.Close
Next oSheet
Application.SheetsInNewWorkbook = lSaveNumSheets
End Sub
</pre>