Just to say thank you for helping me out in the Newsgroups with my excel
query of sending one sheet of a workbook in the email.

It would have taken me forever without your help, so thank you very much.
I'm quite good on Word if you ever need a hand!!

Trendy Wendy


"Nigel" wrote:

> 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.
>
>
>