I'm SUPER new to VBA, so I have no idea what I'm doing, but DO know what I need to do.
I have a report that includes all the checks that our payroll company processes each week (lists name, address, check number, department in different columns). I have a code to separate this master list into separate worksheets based on department code. (Everyone with department code 012 will be sorted into a sheet named 012)
I have the following code that sends those separate worksheets to a group of individuals (who are listed on a different worksheet A1: Worksheet name, B1: Email Address (Subsequent email addresses are listed under B1 in B2 and so on), C1: Email Subject...then the list continues in D1, E1, and F1).
The problem I'm running into is the code won't run completely through the list if it encounters a worksheet name that doesn't exist.
For instance. The site that is listed as 016 (therefore gets named worksheet 016) didn't work that week, so no one received a check. There is no worksheet 016 created so the code stops emailing through the rest of the list.
ALSO....Is there a way for me to include a column where I can put info in the body of the email?
My questions are:
1) How do I get the code to continue if it encounters a worksheet that doesn't exist?
2) Can I include text meant for the body of the email?
Here is the code I'm working with.
Sub Mail_sheets() Dim MyArr As Variant Dim last As Long Dim shname As Long Dim a As Integer Dim Arr() As String Dim N As Integer Dim strdate As String For a = 1 To 253 Step 3 If ThisWorkbook.Sheets("mail").Cells(1, a).Value = "" Then Exit Sub End If '<< Application.ScreenUpdating = False last = ThisWorkbook.Sheets("mail").Cells(Rows.Count, _ a).End(xlUp).Row N = 0 For shname = 1 To last N = N + 1 ReDim Preserve Arr(1 To N) Arr(N) = ThisWorkbook.Sheets("mail").Cells(shname, a).Value Next shname ThisWorkbook.Sheets(Arr).Copy With ThisWorkbook.Sheets("mail") MyArr = .Range(.Cells(1, a + 1), .Cells(Rows.Count, _ a + 1).End(xlUp)) End With ActiveWorkbook.SendMail MyArr, ThisWorkbook.Sheets("mail").Cells(1, a + 2).Value ActiveWorkbook.Close False Application.ScreenUpdating = True Next a End Sub