VBA Email TO Cell that contains IF formula

[asiuol84]

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?


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

[Logit]

You might try :


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

[asiuol84]

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.

[Logit]

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


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

[asiuol84]

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


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.


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