VBA Email TO Cell that contains IF formula

Hi, Very new to excel VBA and learning as I go by using forums. I have a working VBA code that generates an email on specific cell changes.
One of the emails, I need to go to just Managers. I have an IF formula set up from a table that generates just the managers email addresses from a table of staff (in a different sheet)

How do I reference the Cell into the 'recip:' field of my email?

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 & (" ") & rw.Columns("E").Value & NL2 & _
        "Platform: " & rw.Columns("F").Value & NL2 & _
        ("Handle: ") & rw.Columns("G").Value & NL2 & _
        "Thank you"
       
    SendAMail recip:=("Managers"), CC:="", BCC:="", _
              subject:="Your expert has been " & rw.Columns("J").Value, _
              bodyText:=mMailBody
           
       
    End Sub

---

formula is =TEXTJOIN("; "|TRUE||IF(StaffDetails!$A$2:$A$10000="Manager"|StaffDetails!$C2:$C1000|"") ) in Cell A9 of 'Sheet 1'

Thank you

You might try :

Code:

---

recip:=Sheet1.Range("A9").Value

---

Thank you. I tried that, and it brings up the email but with a blank 'To' field.
I've been told I may need to use workbook_calculate, but don't know where to put that, also been told to set the IF formula as a Named Range, but I tried that and then referenced the named range, and it still didn't work. Not sure if I need to add another function for the named range before referring to it in the recip: field.

I'm not familiar with the email format you are using. The one used here is similar but ...

Code:

---

Option Explicit

Sub Mail_workbook_Outlook()

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

    With OutMail
        .To = Sheet1.Range("D5").Value    '<----- change cell value here
        .CC = ""
        .BCC = ""
        .Subject = "Your Subject"
        .Body = "The Body"
        .Display    'or you can use .Send to autosend the email without first reviewing
    End With

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

---

Quote:

---

Originally Posted by Logit

I'm not familiar with the email format you are using. The one used here is similar but ...

Code:

---

Option Explicit

Sub Mail_workbook_Outlook()

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

    With OutMail
        .To = Sheet1.Range("D5").Value    '<----- change cell value here
        .CC = ""
        .BCC = ""
        .Subject = "Your Subject"
        .Body = "The Body"
        .Display    'or you can use .Send to autosend the email without first reviewing
    End With

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

---

---

Thank you - I've attached my full code below. The emails generate as they should for all of the options already set up, it's just the 'TO' bit that i'm having issue with - I have the staff roles, names and email addresses in a table, Cell A9 on Sheet1 has the IF setup to look at the table, and generate just the Managers email addresses. It's the managers emails that I want the email to go to. Assume it doesn't like it because of the IF formula.
I've tried so many different ways, but nothing works!
I may have to re-think the way the email addresses are found.

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge > 1 Then Exit Sub 'only one cell at a time


 
    'check for col J
    If Not Application.Intersect(Target, Me.Range("J3:J1000")) Is Nothing Then
        If Target.Value = "Approved" Or _
        Target.Value = "Rejected" Then
            SendApprovedMail Target.EntireRow 'pass row to sendmail sub
        End If
    End If
   
    'check for col H
    If Not Application.Intersect(Target, Me.Range("H3:H1000")) Is Nothing Then
        If Target.Value = "Yes" Then
            SendColHYesMail Target.EntireRow 'pass row to sendmail sub
        End If
    End If
   
   
    'check for col P
    If Not Application.Intersect(Target, Me.Range("P3:P1000")) Is Nothing Then
        If Target.Value = ("OK") Then 'send chase email
            SendChaseUpMail Target.EntireRow 'pass row to sendmail sub
        End If
    End If
   
End Sub




'send a mail using info from row `rw`
Sub SendApprovedMail(rw As Range)
    Const NL2 As String = vbNewLine & vbNewLine
    Dim mMailBody As String
   
    mMailBody = "Hi " & rw.Columns("B").Value & NL2 & _
        "Your expert has been " & rw.Columns("J").Value & NL2 & _
        "See Row " & rw.Columns("A").Value & NL2 & _
        "Name: " & rw.Columns("D").Value & (" ") & rw.Columns("E").Value & NL2 & _
        "Platform: " & rw.Columns("F").Value & NL2 & _
        ("Handle: ") & rw.Columns("G").Value & NL2 & _
        "Notes: " & rw.Columns("I").Value & NL2 & _
        "Thanks"
       
    SendAMail recip:=rw.Columns("C").Value, CC:="", BCC:="", _
              subject:="Your expert has been " & rw.Columns("J").Value, _
              bodyText:=mMailBody
   
End Sub


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 & (" ") & rw.Columns("E").Value & NL2 & _
        "Platform: " & rw.Columns("F").Value & NL2 & _
        ("Handle: ") & rw.Columns("G").Value & NL2 & _
        "Thank you"
       
    SendAMail recip:= "", CC:="", BCC:="",  'This is where i'm having issues.  The rest all works correctly
              subject:="An Expert has been sent for approval", _
              bodyText:=mMailBody
             
       
    End Sub
Sub SendChaseUpMail(rw As Range)
    Const NL2 As String = vbNewLine & vbNewLine
    Dim mMailBody As String
   
    mMailBody = "Hi " & rw.Columns("B").Value & NL2 & _
        "It's been 7 days or more since your initial contact was made with:" & NL2 & _
        rw.Columns("D").Value & (" ") & rw.Columns("E").Value & NL2 & _
        "See Row " & rw.Columns("A").Value & NL2 & _
        "Please Chase Up" & NL2 & _
        "Thanks"
       
    SendAMail recip:=rw.Columns("C"), CC:="", BCC:="", _
              subject:=" An Expert requires follow up", _
              bodyText:=mMailBody
       
    End Sub


'send an email
Sub SendAMail(recip As String, CC As String, BCC As String, _
              subject As String, bodyText As String)
    With CreateObject("Outlook.Application").CreateItem(0)
        .To = recip
        .CC = CC
        .BCC = BCC
        .subject = subject
        .body = bodyText
        .Display 'or you can use .Send
    End With
End Sub

---