Consulting

Results 1 to 5 of 5

Thread: VBA Email TO Cell that contains IF formula

  1. #1
    VBAX Newbie
    Joined
    Oct 2022
    Posts
    3
    Location

    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?

    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

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    You might try :

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

  3. #3
    VBAX Newbie
    Joined
    Oct 2022
    Posts
    3
    Location
    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.

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    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

  5. #5
    VBAX Newbie
    Joined
    Oct 2022
    Posts
    3
    Location
    Quote Originally Posted by Logit View Post
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •