Macro to exit sub if sheet not found

[howard]

I have written code to email a sheet, but if the sheet does not exist, then Message box to advise "Sheet does not exist as no data available" and then to exit sub, but if sheet exits then to continue


It would be apprciated if someone could kindly amend my code

Sub Email_BR1()

Dim File As String, strBody As String, ws As Worksheet
On Error Resume Next
Set ws = Sheets("MySheet")



Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Err.Number <> 0 Then
MsgBox "There are no overdue amounts for this branch, hence there is no sheet "
Else
File = Environ$("temp") & "\" & Format(Range("Q2"), "mmm-yy ") & Format(Now, "dd-mmm-yy h-mm-ss") & ".xlsx"
strBody = "Hi " & Sheets("BR1").Range("U1") & vbNewLine & vbNewLine & _
"Attached, please find overdue amounts." & vbNewLine & vbNewLine & _
"The total overdue balance is " & Format(Sheets("BR1").Range("P1"), "R#,##0.00") & "." & vbNewLine & vbNewLine & _
"Regards" & vbNewLine & vbNewLine & _
"Howard"
ActiveWorkbook.Save

Sheets("BR1").Copy
With ActiveWorkbook
.SaveAs Filename:=File, FileFormat:=51
.Close savechanges:=False
End With
With CreateObject("Outlook.Application").CreateItem(0)
.Display
.to = Join(Application.Transpose(Sheets("BR1").Range("V1:V2").Value), ";")
.Subject = "overdue Debtors"
.body = strBody
.Attachments.Add File
'.Send
End With
End If
Kill File
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub [code]

 

[dmt32]

thanks Dave it works perfectly

welcome - glad resolved

Dave