Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Conditional fromating When Emailing Using A Control Button
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Emailing a template and keeping same layout as the message body | Excel Discussion (Misc queries) | |||
Emailing a worsheet from a button in Excel | Excel Programming | |||
Keeping Control Buttons Still | Excel Worksheet Functions | |||
Keeping button pointing to original workbook | Excel Programming |