Keeping Conditional fromating When Emailing Using A Control Button

[Sam]

Hi everyone,

I hope you can help.

I am trying to keep the conditional formatting I have on a spreadsheet
when I email to a recipient. The email code I use on on my command
button is a formula I got on this message bord and it works GREAT!!

The only issue is the conditional formatting piece.
Can anyone help?


Private Sub CommandButton3_Click()
Sheet1.Unprotect Password:="ds"
Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim I As Long

Set Source = Nothing
On Error Resume Next
Set Source = Range("e1:i29").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Source Is Nothing Then
MsgBox "The sheet is protected. " & _
"Click On Cell B1 - Go to Tools - Protection -
Unprotect Sheet.", vbOKOnly
Exit Sub
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)
Set Source = Range("e1:i29")

Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False

End With

TempFilePath = Environ$("temp") & "\"
TempFileName = wb.Name _


If Val(Application.Version) < 12 Then
'You use Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010
FileExtStr = ".xlsx": FileFormatNum = 51
End If

With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail "", _
""
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have sent
Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Sheet1.Protect Password:="ds"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$H$5"
Range("H9").Select
Case "$H$9"
Range("H10").Select
End Select
End Sub