Hi, Here is solution you might like to try or adapt. It has two procedures
the first MailSheets scans each worksheet in the activeworkbook and if cell
B1 has a value (string > 0) it creates a copy of the worksheet as a new
workbook (same name as the worksheet) and mails it to the contents of B1.

If you place each managers email address in cell B1 on each related sheet
you could use this?

The second part is the SendMail procedure that takes the email address and
sends the workbook just created. Finally control returns to the first
procedure and the workbook created is then deleted. Only word of caution,
it presumes that the email address in B1 is valid! and that the creation of
a new workbook of the same name as the sheet is acceptable. If it already
exists you get a warning.

You need to add a reference to Outlook in the VBE.
Sub MailSheets()
Dim sh As Worksheet, email As String, shName As String
Application.ScreenUpdating = False
For Each sh In Worksheets
email = sh.Range("B1").Value
shName = sh.Name
If Len(email) > 0 Then
sh.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & shName & ".xls"
Call SendMail(email)
ActiveWorkbook.Close False
Kill ThisWorkbook.Path & "\" & shName & ".xls"
End If
Next sh
Application.ScreenUpdating = True
End Sub

' Mailing procedure
Sub SendMail(eMadd As String)
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = eMadd
.CC = ""
.BCC = ""
.Subject = "WorkSheet Mailing"
.Body = "This is an automated email with the attached worksheet"
.Attachments.Add ActiveWorkbook.FullName
'.DeleteAfterSubmit = True ' use this option if you do not want an
entry in the senders sent mail folder
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--
Cheers
Nigel



"Trendy Wendy" <TrendyWendy@discussions.microsoft.com> wrote in message
news:F6ABE36D-3057-475F-A700-BECBF8E9FCB6@microsoft.com...
> Scenario:
>
> I have a workbook with names of people, managers and telephone costs. I
> need to email the managers a list of all of their people and their
telephone
> costs. So, I begin by making a Pivot Table of my data (grouped by
manager)
> and then when I double-click on the total costs, it automatically creates
a
> new sheet detailing all of the staff and their costs. Lovely! Now I want
to
> send these individual sheets to the managers (i.e. the one sheet only, not
> the rest of the workbook). Can I do this? The manager's email is in one
of
> the fields, if that helps.
>
> Anyway, what I tried to do was this. Record a macro, double-click on the
> pivot table cell, a new sheet is created. Copy the cell with the managers
> name on and paste into the sheet name. Move the sheet to a new workbook.
> Save the workbook and paste the sheet name into the saved name. Do File,
> Send as Attachment, paste name in To box and Send. Carry on until all
> managers are done.
>
> However, I come stuck right near the beginning because my VBA is saying
> select sheet 3, when of course next time it will be sheet 4 and therefore
is
> still looking for sheet 3. Can I say somehow, just select the active
sheet
> that I am on?
>
> I can attach excel spreadsheet if you like?
>
> Many thanks is much appreciated.