VLOOKUP in Macro to send e-mails

gregoprt

New Member
Joined
Sep 10, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA Code:
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
 

Attachments

  • Main Sheet.png
    Main Sheet.png
    53.8 KB · Views: 6
  • VLOOKUP 1.png
    VLOOKUP 1.png
    37.4 KB · Views: 6
  • VLOOKUP 2.png
    VLOOKUP 2.png
    50.4 KB · Views: 6

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

earthworm

Well-known Member
Joined
May 19, 2009
Messages
619
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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))
 

Forum statistics

Threads
1,147,559
Messages
5,741,817
Members
423,689
Latest member
Jords998

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top