Send Email Depending on cell Value

[ClaudeV2240]

Hi guys,

I'm stuck with this code, tried everywhere with no luck.

My sheet:

1. loops through a range of store names from a different list then,
2. creates a pdf,
3. stores the pdf in a folder and
4. a INDEX(MATCH) formula identifies the email address in Column O2:O11.
   • Now sometimes there are more than 1 person (not exceeding 10) to receive the same email and sometimes there maybe 1 person

What I would like to do:

1. determine how many people should receive the pdf document (identified in column O2:O11)
   • I tried using the countif function with the special cells but that does not work.  Still counting the INDEX(MATCH) formula with blank values
2. send email to them
3. start the next loop

I have all of the code ready, just the one where I need get them in the VBA.  I'm stuck on line 20 to create a range called EmailTo and insert that in line 33

Sub PrintSendStore()
    
    Dim Oapp As Outlook.Application
    Dim Omail As Object
        
    storecnt = 1
    
    Do While storecnt <= Range("SharePoint[STORE_NAME]").Rows.Count
    
    StoreName = Range("SharePoint[[#All],[STORE_NAME]]").Offset(storecnt, 0)
    
    
    Sheet3.Range("D5").Value = StoreName
    
    Filename = Sheet3.Range("D5").Value & " - " & Format(Sheet3.Range("D9").Value, "MMMM YYYY") & ".pdf"
    FileName2 = Sheet3.Range("D5").Value & " - " & Format(Sheet3.Range("D9").Value, "MMMM YYYY")
    
    
    Sheet3.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "/" & Filename
    'EmailTo = 
    
    Set Oapp = New Outlook.Application
    Set Omail = Oapp.CreateItem(0)
        
    Oapp.Session.Logon
    
    Dim last_row As Integer
    Dim i As Integer
    
    last_row = Application.WorksheetFunction.CountA(Sheet3.Range("O:O"))
    
    With Omail
        .To = EmailTo                              ' Sheet3.Range("O2").Value (TO EMAIL TO SINGLE PERSON)
        .Subject = "Monthly Meeting template: " & FileName2
        .Body = "Hi " & Sheet3.Range("P2") & "," & vbCrLf & vbCrLf & _
                "Please find attached your Monthly Store Operational Meeting Template." & vbCrLf & vbCrLf & _
                "Kind Regards" & vbCrLf & vbCrLf & _
                "Admin"
        .Attachments.Add (ThisWorkbook.Path & "/" & Filename)
        .display
    
    End With
     
    storecnt = storecnt + 1
    
    Loop

    Set Oapp = Nothing
    Set Omail = Nothing

End Sub