![]()
Sub EmailHearnNC() ContinueEmail: Application.DisplayAlerts = False '1 All Dim sh, arr() As Variant Dim bDim As Boolean: bDim = False x=0 For Each sh In Array("61") If Evaluate("=ISREF('" & sh & "'!A1)") Then x=1 If bDim = False Then ReDim arr(0 To 0) As Variant arr(0) = sh bDim = True Else ReDim Preserve arr(0 To UBound(arr) + 1) As Variant arr(UBound(arr)) = sh End If End If Next sh if x=0 then msgbox "Sheet not found" Exit sub endif Sheets(arr).Select Sheets(arr).Copy ChDir "U:\" ActiveWorkbook.SaveAs Filename:="U:\testfile.xlsx", FileFormat:=51 ActiveWorkbook.SendMail Recipients:= _ Array("test@test.com"), _ Subject:="test" & Eom ActiveWorkbook.Saved = True ActiveWorkbook.Close Dim myVal On Error Resume Next Set QueryOutlook = GetObject(, "Outlook.Application") If Err <> 0 Then myVal = Shell("C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\OUTLOOK.EXE", 1) Exit Sub Else End If End Sub
Bookmarks