Email Single WorkSheets from a Workbook

[Trendy Wendy]

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.

[Ron de Bruin]

Hi Trendy Wendy

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


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

[Nigel]

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.

[Trendy Wendy]

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

[Trendy Wendy]

Thanks guys - I'm too tired to look at this now - but will go through your
suggestions and get back to you. Thanks for taking the time.

Trendy

"Trendy Wendy" wrote:

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