Create VBA variable worksheet reference to email sheets based on cell value

Question

I have a workbook which has individual sheets that house office data based on the office location (example; sheet named Chicago would have data for our Chicago office and so on and so forth). Currently on every sheet I have a button with an assigned macro that exports the sheet as a pdf and attaches it to a prefilled email that I can then send to that office's designated contact based on some code I found online that works perfectly. The email of the contact is located in the same cell in every sheet. (See code below). I only need to send this email to locations that haven't reached a certain benchmark.

    Sub SendEmailBulk()
'Update 20131209
Dim Wb As Workbook
Dim ws As Worksheet
Dim FileName As String
Dim OutlookApp As Object
Dim OutlookMail As Object
On Error Resume Next
Set Wb = ThisWorkbook
Set ws = Active.Sheet
FileName = Wb.FullName
xIndex = VBA.InStrRev(FileName, ".")
With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .FitToPagesTall = 1
    .FitToPagesWide = 1
End With
If xIndex > 1 Then FileName = VBA.Left(FileName, xIndex - 1)
FileName = FileName & "_" + ActiveSheet.Name & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileName
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
    .SentOnBehalfOfName = "abc@xyz.com"
    .To = ActiveSheet.Range("J10")
    .CC = ""
    .BCC = ""
    .Subject = Range("C1") & " Data"
    .Body = "abcxyz"
    .Attachments.Add FileName
    .Display
End With
Kill FileName
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

What I would like to do is instead of having to check every sheet and send the emails individually to instead have some code that checks and emails only the required ones for me.

In another sheet I have a sheet called SUMMARY which contains a summary of all of our office locations in column B and a true or false counter setup that records whether they have achieved the benchmark in column C. I only need to send the above email to those locations who have not reached that benchmark (a FALSE value in column C).

I understand that I would need some code that checks the first row of the SUMMARY sheet for the first office location in Column B if it contains the FALSE value in Column C then, if so, assigns the value in Column B to a variable (lets say SheetName). Then the ActiveSheet reference would need to be replaced with WorkSheets("SheetName") to have the email code run before looping back around to check the second office location and so on until the end.

I have an idea of how this would work in theory I just don't have the exact VBA knowledge on how to write something to this effect. Any help would be greatly appreciated.

Create VBA variable worksheet reference to email sheets based on cell value

0 Answers0