VLOOKUP in Macro to send e-mails

[gregoprt]

Hello, folks.

I'm trying to create a Macro to send e-mails to different To and CC based on 2 informations (same row of Column A and Column F) in the same sheet. My idea here is to check if Column F = 'X' and then search the correct To and CC in another sheets vith VLOOKUP function.
The major issue right now is that the VLOOKUP function is returning False. I know that the problem is in the RC argument of VLOOKUP, but I haven't been able to figure out how can I solve it.

The minor issue then is how the Macro would go to the next info (that is, next Value) and not the next row, because the data can have multiple lines with same info at Column A and continue to send the e-mails.

Also, can't lock the cells because this Macro will run weekly with variable amount of data.

The code is the following:

Sub manda_email()

Dim cc_padrao As String
Dim Elt As String
Dim Bt As String
Dim cell As Range
Dim lojas As Range
Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

cc_padrao = "Carlos@email; Son@email; Lau@email"

Set lojas = Range("A2:A" & Lastrow)

'Consultar Band
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Bandeiras!R2C1:R170C2,2,0)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & Lastrow)

Set OutApp = CreateObject("Outlook.Application")
Dim saudacao, corpo, dif, final As String
      
For Each lj In lojas

    If lj.End(xlToRight).Value = "Elt" Then
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        .display

'So far, so good!

        .To = GR_Elt()
        .cc = cc_padrao & CR_Elt()
        .Subject = "Dif | " & "Eletro" & lj 'lj here would be the cell value'
                
        saudacao = "Olá, " & Chr(10) & Chr(10)
        corpo = "Por gentileza, " & Chr(10) & Chr(10)
        dif = "Incluir tabela" & Chr(10) & Chr(10)
        final = "Por gentileza, enviar." & Chr(10) & Chr(10) & "Aguardo breve retorno." & Chr(10) & Chr(10)
        
        .BODY = saudacao & corpo & dif & final

        'Email.send
    End With
    
    Else
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .display
            .To = GR_Bt() & EN_Bt()
            .cc = cc_padrao & CR_Bt()
            .Subject = "Dif | " & "BT" & lj
            
            saudacao = "Olá, " & Chr(10) & Chr(10)
        corpo = "Por gentileza, " & Chr(10) & Chr(10)
        dif = "Incluir tabela" & Chr(10) & Chr(10)
        final = "Por gentileza, enviar." & Chr(10) & Chr(10) & "Aguardo breve retorno." & Chr(10) & Chr(10)
            
            .BODY = saudacao & corpo & dif & final
            
            'Email.send
    
    End With
    End If

Next

End Sub
Public Function GR_Elt() As String
    GR_Elt = ActiveCell.FormulaR1C1 = "=VLOOKUP(RC,'Contatos Elt'!C1:C4,4,0)"
    Exit Function
End Function

Public Function GR_Bt() As String
    GR_Bt = ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'Contatos Bt'!C1:C3,4,0)"
    Exit Function
End Function

Public Function CR_Elt() As String
    CR_Elt = ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'Contatos Elt'!C1:C6,4,0)"
    Exit Function
End Function

Public Function CR_Bt() As String
    CR_Bt = ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'Contatos Bt'!C1:C5,4,0)"
    Exit Function
End Function

Public Function EN_Bt() As String
    EN_Bt = ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'Contatos Bt'!C1:C4,4,0)"
    Exit Function
End Function

 

[earthworm]

Try wrapping vlookup in IFERROR and upon error apply second lookup in this way if you get an error it will look in another sheet.

Example

=IFERROR(VLOOKUP(RC[-5],Bandeiras!R2C1:R170C2,2,0),VLOOKUP(RC[-5],Bandeiras2!R2C1:R170C2,2,0))