Automatically send emails to multiple recipients identified by IF formula

[asiuol84]

I have highlighted the bit in green where I am having issue. I want the email to be sent to multiple recipients that are found from a table using an IF statement. I know that if I just reference the cell, it won't work because it's a formula - but don't know how to make it work! Very New to this, and learning as I go.

This is the formula in the cell that I want to reference and send the email to: Cell A9 Sheet1 =TEXTJOIN(";"|TRUE||IF(StaffDetails!$A$2:$A$10000="Manager"|StaffDetails!$C2:$C1000|"")) (The email addresses of managers within the staff list)



I have this set up as part of a larger list of code:

Sub SendColHYesMail(rw As Range)
Const NL2 As String = vbNewLine & vbNewLine
Dim mMailBody As String

mMailBody = "Hi " & NL2 & _
rw.Columns("B") & " has sent an expert to approve" & NL2 & _
"See Row " & rw.Columns("A").Value & NL2 & _
"Name: " & rw.Columns("D").Value & (" ") & Range("E3") & NL2 & _
"Platform: " & rw.Columns("F").Value & NL2 & _
("Handle: ") & rw.Columns("G").Value & NL2 & _
"Thank you"

SendAMail recip:="multiple email addresses found by IF", CC:="", BCC:="", _
subject:=rw.Columns("B") & " has sent an expert to approve", _
bodyText:=mMailBody

End Sub