Checkbox to remove recipient from email

[FastFord]

I am creating a workbook where each worksheet represents Vacation Leav
Hours accrued for an employee. The Vacation information is update
weekly and then I press a custom toolbar button that activates a macr
that emails each induvidual statement to that particular emloyee only
The employee's email address is stored in b2. The employee's superviso
also gets a copy so they can monitor how much vacation that thei
employee(s) might be taking in the near future. There are approx 5
employees/worksheets. I created the macro by altering VBA code take
from Ron DeBruin's website and it works very well.

Now I need to add checkboxes next to the two email addresses with
note that if checked, the email address next to this checkbox is not t
recieve a copy of this worksheet during the bulk mailing. This will cu
down on the amount of mail in an inbox for an employee or superviso
who does not care about the statement.

When I added CheckBox1 and CheackBox2 and the following code, I a
running into problems with people that are not supposed to recieve an
email still getting one, and everone recieves a copy of worksheet
instead of their own worksheet. Any suggestions would be appreciated.

-Joe

Here's what I have so far. I will also attach a sample calle
3WACAP_Leave.xls


Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Application.ScreenUpdating = False
'loop through each wookbook
For Each sh In ThisWorkbook.Worksheets
sh.Copy
Set wb = ActiveWorkbook
With wb
Dim strdate As String
Dim firstName As String
strdate = Format(Now, "mm-dd-yy")
'Get the employee's first name from b2
firstName = sh.Range("b2").Value
.SaveAs firstName & "'s Leave Hours as of " & " "
strdate & ".xls"
'check to see if this person wants a copy of the leav
statement
If wb.CheckBox1.Value = False Then
'send statement of leave to employee
.SendMail ActiveSheet.Range("e2").Value, _
firstName & "'s Leave Hours as of " & " "
strdate
ElseIf wb.CheckBox1.Value = True Then
End If
'check to see if this person wants a copy of the leav
statement
If wb.CheckBox2.Value = False Then
'send copy to employee's supervisor
.SendMail ActiveSheet.Range("e5").Value, _
"Your employee " & firstName & "'s Leave Hours a
of " & " " & strdate
ElseIf wb.CheckBox2.Value = True Then
End If

.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next sh
'end of loop
Application.ScreenUpdating = True
End Su

Attachment filename: 3wacap_leave.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=45047

 

[Ron de Bruin]

Hi

I have not see the file but
Try this

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Application.ScreenUpdating = False
'loop through each wookbook
For Each sh In ThisWorkbook.Worksheets
sh.Copy
Set wb = ActiveWorkbook
With wb
Dim strdate As String
Dim firstName As String
strdate = Format(Now, "mm-dd-yy")
'Get the employee's first name from b2
firstName = sh.Range("b2").Value
.SaveAs firstName & "'s Leave Hours as of " & " " & strdate & ".xls"
'check to see if this person wants a copy of the leave statement
If wb.Sheets(1).CheckBox1.Value = False Then
'send statement of leave to employee
.SendMail ActiveSheet.Range("e2").Value, _
firstName & "'s Leave Hours as of " & " " & strdate
End If
'check to see if this person wants a copy of the leave statement
If wb.Sheets(1).CheckBox2.Value = False Then
'send copy to employee's supervisor
.SendMail ActiveSheet.Range("e5").Value, _
"Your employee " & firstName & "'s Leave Hours as of " & " " & strdate
End If

.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next sh
'end of loop
Application.ScreenUpdating = True
End Sub