Emailing xlVeryHidden Sheets

[macross]

Continuing to build my workbook and running into a problem.

When I open my workbook I have it setup so that it requires you to enable macros to view the contents. As part of that I am setting 3 sheets to .Visible = xlVeryHidden so that no user can ever see those sheets.

On the sheet that is visible I have a button inserted that emails 1 of those hidden sheets to one email and another of those hidden sheets to another email.

[email protected]

Here is that button code

Private Sub CommandButton1_Click()
'Working in 97-2010
    Dim wb As Workbook
    Dim Shname As Variant
    Dim Addr As Variant
    Dim N As Integer
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim I As Long

    Shname = Array("ToTeam", "ToCom")
    Addr = Array("[email protected]", "[email protected]")

    If Val(Application.Version) >= 12 Then
        'You run Excel 2007-2010
        FileExtStr = ".xlsm": FileFormatNum = 52
    Else
        'You run Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    End If

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

    TempFilePath = Environ$("temp") & "\"

    'Create the new workbooks/Mail it/Delete it
    For N = LBound(Shname) To UBound(Shname)

        TempFileName = "Sheet " & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")

        ThisWorkbook.Sheets(Shname(N)).Copy
        Set wb = ActiveWorkbook

        With wb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
            On Error Resume Next
            For I = 1 To 3
                .SendMail Addr(N), _
                          "This is the Subject line"
                If Err.Number = 0 Then Exit For
            Next I
            On Error GoTo 0
            .Close SaveChanges:=False
        End With

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

    Next N

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

The macro fails on the part of the code I have set in red

From testign a few things it appears that it is beacuse the "ToTeam" and "ToCom" are being set to xlVeryHidden that is causing the macro to fail. If I make these both visible the macro runs fine.

So the question then becomes; what can I do so the contents stay xlVeryHidden, but can be emailed whent he button is clicked?

[TMS]

Untested, but I think you'd have to make the sheet(s) visible before copying it/them. As you have screen updating set to false, you could go "visible", "copy", "hidden" all behind the curtains.


Regards, TMS

[macross]

Ok I tried what you suggested, perhaps I am missing soemthign obvious though. It now sends the 1st email but errors at the same code segment when it tries for the 2nd email.

New code in red

Private Sub CommandButton1_Click()
'Working in 97-2010
    Dim wb As Workbook
    Dim Shname As Variant
    Dim Addr As Variant
    Dim N As Integer
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim I As Long
    
    Shname = Array("ToTeam", "ToCom")
    Addr = Array("[email protected]", "[email protected]")

    If Val(Application.Version) >= 12 Then
        'You run Excel 2007-2010
        FileExtStr = ".xlsm": FileFormatNum = 52
    Else
        'You run Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    TempFilePath = Environ$("temp") & "\"

    'Create the new workbooks/Mail it/Delete it
    
    With Application
        
    Sheets("ToTeam").Visible = True    
    Sheets("ToCom").Visible = True
        
    End With
    
    For N = LBound(Shname) To UBound(Shname)

        TempFileName = "Sheet " & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")

        ThisWorkbook.Sheets(Shname(N)).Copy
        Set wb = ActiveWorkbook
        
        With wb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
            On Error Resume Next
            For I = 1 To 3
                .SendMail Addr(N), _
                          "This is the Subject line"
                If Err.Number = 0 Then Exit For
            Next I
            On Error GoTo 0
            .Close SaveChanges:=False
        End With
        
        With Application
        
        Sheets("ToTeam").Visible = xlVeryHidden        
        Sheets("ToCom").Visible = xlVeryHidden
        
        End With

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

    Next N

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

[TMS]

You're in the Command Button code so you probably need to fully qualify the worksheets with ThisWorkbook or ActiveWorkbook. Don't think you need "With Application" ... in any case, you're not linking anything to the application.


Regards, TMS