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
It would be apprciated if someone could kindly amend my code
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]