Se entendi, só será enviado email se os valores na coluna 10 a partir da linha 8 for "Fim da Validade", o que não compreendi qdo diz que " Na célula "B4" tem uma data automática", pois não encontrei nenhuma instrução que altere esta data a não ser manualmente.
Desta forma eliminando algumas hipóteses, porque não altera a rotina do evento change para executar qdo ao se digitar em B4 realizar a verificação a partir de "J8" que contêm formulas se o valor é o texto e envia o email. Assim poderia criar a rotina que envia email, separando-a do evento e ser chamada somente se tiver a coincidência verificada.

[]s

Mauro Coutinho bom dia! Quando disse data automática na "B4" quero dizer que é uma formula =hoje(), independentemente vc pode colocar a data manualmente na célula "B4" porque se ela for igual a "i8" então "J8" vai ser "Fim da Validade" senão "Ok". Se for igual a "Fim da Validade" então envia o e-mail, o fato é que quando vc insere manualmente a data na "B4" para ser igual a "i8" Não envia o e-mail.
Agora se vc clicar duas vezes dentro da célula "J8" e dar enter vai enviar normalmente, não consigo entender o porque disso...

Eduardo, a sua rotina está no evento change, ou seja só correrá qdo alterar alguma celula, como o campo data não estava com a formula que disse, supuz que estava digitando, e para eu entrar com a formula depois que abrir o modelo o evento correrá, e a data será alterada, não adiantando eu salvar e fechar o arquivo, o mesmo já vai estar com a data atualizada.

Mas vamos falar sobre a rotina em si :
Temos as instruções :
linha = ActiveCell.Row - 1
Na linha acima e tanto na abaixo temos duas situações diferentes, explicado mais abaixo.
If Target.Address = "$J$" & linha Then

Se selecionar B4 e digitar uma data e pressionar o enter a Variável linha será carregada com o nº "4", porque apos o Enter o cursor move-se para baixo na linha 5 e o -1 informa que saiu da linha acima diminuindo um, mas se selecionar B4 e alterar sem pressionar o Enter o valor da Variável será 3, porque como não saltamos de linha temos a linha atual 4 - 1, e o mesmo acontece com a coluna "J".

Se correr a rotina utilizando o F8 poderá acompanhar passo a passo, e verá que saimos da rotina se alterarmos manualmente em B4 devido a condição "If Target.Address = "$J$" & linha Then
". por isto disse para capturar a alteração em B4 e depopis correr verificação na coluna "J".
Não sei se entendeu, escrevi bem rápido que agora tenho de sair, mais tarde se der retorno ao tópico.

[]s

Mauro você pode por gentileza adaptar o código para fazer funcionar a planilha?

não estou conseguindo fazer...

Eduardo, segue uma adaptação, aqui não tenho como testar, então faça os testes e veja se é isto.

Estou supondo que estamos alterando somente em "B4", ou seja digitando, porem se for necessário a questão da data automática como você disse, pode estar utilizando o Evento Calculate que o mesmo é executado antes do Change :

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$B$4" & linha Then
        Call Verifica_Condicao_Envia_Mail
    End If

End Sub


Sub Verifica_Condicao_Envia_Mail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim texto As String

    Dim sTT_Lin As Long, linha As Long

    With Sheets("Vencimento")
        'Qde de linhas preenchidas na coluna J
        sTT_Lin = .Range("J" & .Rows.Count).End(xlUp).Row
        
        'Iniciamos a verificação na linha 8
        For linha = 8 To sTT_Lin

            If Range("J" & linha).Value = "Fim da Validade" Then

                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
                
                texto = "Prezado(a) " & Plan1.Cells(linha, 1) & "," & vbCrLf & vbCrLf & _
                    "FALTAM 30 DIAS PARA EXPIRAR A VALIDADE DO CURSO DO FUNCIONÁRIO" & Plan1.Cells(linha, 3) & " CURSO ESPAÇO CONFINADO VENCIMENTO EM " & _
                    Plan1.Cells(linha, 8) & " FAVOR AGENDAR RENOVAÇÃO." & vbCrLf & _
                    " INFORMAÇÕES ABAIXO:" & vbCrLf & _
                    "    Status: " & Plan1.Cells(linha, 10) & vbCrLf & _
                    "    Ação tomada: " & Plan1.Cells(linha, 10) & vbCrLf & vbCrLf & _
                    "Atenciosamente," & vbCrLf & _
                    "Eduardo Paulo Martins" '
              
                With OutMail
                     .To = Plan1.Cells(linha, 1)
                     .CC = ""
                     .BCC = ""
                     .Subject = "Título do email"
                     .Body = texto
                    ' .Display   'Utilize Send para enviar o email sem abrir o Outlook
                    .send
                End With
                
                On Error GoTo 0
            
            Else
            'não faz nada
            End If
             
        Next linha

    End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
        
    End Sub

[]s

PERFEITO Mauro Coutinho, cara vc mandou super bem isso que eu chamo de raciocínio lógico...
Muito obrigado pela paciência e por desperder do seu precioso tempo para ajudar leigos como eu...
Abraços,