Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email selected sheets
Hello,
I came across some fabulous code by Dave Peterson which allows me to print out selected sheets based on checkbox selections. Could someone please assist me to call a separate macro which I have to send the separate sheets to separate addressees instead of printing the sheets. My sendmail macro (called Mail_ActiveSheet) currently sends the activesheet having the email address in cell A1. I think I got this from Ron Debruin's site. Please see Dave's code below. Sub SelectSheets() Dim i As Integer Dim TopPos As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Dim curWkbk As Workbook Application.ScreenUpdating = False Set curWkbk = ActiveWorkbook ' Check for protected workbook ' If ActiveWorkbook.ProtectStructure Then ' MsgBox "Workbook is protected.", vbCritical ' Exit Sub ' End If ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = Workbooks.Add.DialogSheets.Add SheetCount = 0 ' Add the checkboxes TopPos = 40 For i = 1 To curWkbk.Worksheets.Count Set CurrentSheet = curWkbk.Worksheets(i) ' Skip empty sheets and hidden sheets If Application.CountA(CurrentSheet.Cells) < 0 And _ CurrentSheet.Visible Then SheetCount = SheetCount + 1 PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(SheetCount).Text = _ CurrentSheet.Name TopPos = TopPos + 13 End If Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 .Caption = "Select sheets to print" End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box CurrentSheet.Activate Application.ScreenUpdating = True If SheetCount < 0 Then If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb.Value = xlOn Then Worksheets(cb.Caption).Activate ActiveSheet.PrintOut ' ActiveSheet.PrintPreview 'for debugging End If Next cb End If Else MsgBox "All worksheets are empty." End If curWkbk.Close savechanges:=False ' Reactivate original sheet CurrentSheet.Activate End Sub Thank you in advance for your assistance. -- By persisting in your path, though you forfeit the little, you gain the great. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email selected tabs of spreadsheet? | Excel Discussion (Misc queries) | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Programming | |||
Email selected range in Excel | Excel Programming |