Excel Rodar macro quando o conteúdo da célula é alterado via fórmula ou manualmente

[DigusX]

Bom dia!

 

Estou com esse macro que roda automaticamente, porém ele só está enviando e-mail quando eu edito qualquer célula da coluna M manualmente, mas preciso que ele envie quando qualquer célula da coluna M mudar via fórmula.

 

Private Sub Worksheet_Change(ByVal Target As Range)

'Envia e-mail pelo Outlook

    Dim OutApp As Object
    Dim OutMail As Object
    Dim texto As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    linha = ActiveCell.Row - 1
        If Target.Address = "$M$" & linha Then

        If Planilha10.Cells(linha, 13) = "Sim" Then
         texto = "Prezado(a), " & vbCrLf & vbCrLf & _
                    "O item: " & Planilha10.Cells(linha, 3) & " necessita de reparo" & "." & vbCrLf & vbCrLf & _


           End If

           If Planilha10.Cells(linha, 13) = "Não" Then
            texto = "Prezado(a), " & vbCrLf & vbCrLf & _
                    "Foi reparado o item: " & Planilha10.Cells(linha, 3) & vbCrLf & vbCrLf & _


           End If

        With OutMail
            .To = "e-mail para quem deseja enviar"
            .CC = ""
            .BCC = ""
            .Subject = "Título do e-mail"
            .Body = texto
            .Display   'Utilize Send para enviar o email sem abrir o Outlook
        End With
        On Error GoTo 0

        Set OutMail = Nothing
        Set OutApp = Nothing

            End If



End Sub

[DigusX]

Problema resolvido!

 

 

Private Sub Worksheet_Change(ByVal Target As Range)

'Envia e-mail pelo Outlook

    Dim OutApp As Object
    Dim OutMail As Object
    Dim texto As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    linha = ActiveCell.Row - 1
        'If Target.Address = "$M$" & linha Then

        If Range("M2").Value <> PrevVal Then

        If Planilha10.Cells(linha, 13) = "Sim" Then
         texto = "Prezado(a), " & vbCrLf & vbCrLf & _
                    "O item: " & Planilha10.Cells(linha, 3) & " necessita de reparo" & "." & vbCrLf & vbCrLf & _


           End If

           If Planilha10.Cells(linha, 13) = "Não" Then
            texto = "Prezado(a), " & vbCrLf & vbCrLf & _
                    "Foi reparado o item: " & Planilha10.Cells(linha, 3) & vbCrLf & vbCrLf & _


           End If

 

PrevVal = Range("M2").Value

        With OutMail
            .To = "e-mail para quem deseja enviar"
            .CC = ""
            .BCC = ""
            .Subject = "Título do e-mail"
            .Body = texto
            .Display   'Utilize Send para enviar o email sem abrir o Outlook
        End With
        On Error GoTo 0

        Set OutMail = Nothing
        Set OutApp = Nothing

            End If

 

[Patropi]

@DigusX , boa tarde

 

Para saber como marcar o tópico como Resolvido, clique no link da minha assinatura.

 

[]s

[Charley Rocha]

@DigusX Como você fez para que o valor da variável PrevVal fique armazenada? Ao meu ver, o condicional

 

If Range("M2").Value <> PrevVal Then

 

Sempre vai dar Verdadeiro, porque ao iniciar a macro PrevVal vai estar vazia.

[DigusX]

48 minutos atrás, Charley Rocha disse:

@DigusX Como você fez para que o valor da variável PrevVal fique armazenada? Ao meu ver, o condicional

 

If Range("M2").Value <> PrevVal Then

 

Sempre vai dar Verdadeiro, porque ao iniciar a macro PrevVal vai estar vazia.

 

Dá pra adicionar em EstaPastaDeTrabalho:

 

Private Sub Worksheet_Open()
PrevVal = Planilha10.Range("M2").Value
End Sub
 

E criar um Módulo:

Public PrevVal As Variant

 

Porque aí quando você abrir a planilha ele irá gravar os valores.

O curioso é que mesmo sem isso que eu mencionei acima a planilha está funcionando. Não entendo muito de VBA, não sei dizer se  If Planilha10.Cells(linha, 13) = "Sim" Then está compensando o PrvVal vazio. 

 

[DigusX]

Em 27/01/2020 às 10:16, DigusX disse:

Problema resolvido!

 

 

Private Sub Worksheet_Change(ByVal Target As Range)

'Envia e-mail pelo Outlook

    Dim OutApp As Object
    Dim OutMail As Object
    Dim texto As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    linha = ActiveCell.Row - 1
        'If Target.Address = "$M$" & linha Then

        If Range("M2").Value <> PrevVal Then

        If Planilha10.Cells(linha, 13) = "Sim" Then
         texto = "Prezado(a), " & vbCrLf & vbCrLf & _
                    "O item: " & Planilha10.Cells(linha, 3) & " necessita de reparo" & "." & vbCrLf & vbCrLf & _


           End If

           If Planilha10.Cells(linha, 13) = "Não" Then
            texto = "Prezado(a), " & vbCrLf & vbCrLf & _
                    "Foi reparado o item: " & Planilha10.Cells(linha, 3) & vbCrLf & vbCrLf & _


           End If

 

PrevVal = Range("M2").Value

        With OutMail
            .To = "e-mail para quem deseja enviar"
            .CC = ""
            .BCC = ""
            .Subject = "Título do e-mail"
            .Body = texto
            .Display   'Utilize Send para enviar o email sem abrir o Outlook
        End With
        On Error GoTo 0

        Set OutMail = Nothing
        Set OutApp = Nothing

            End If

 

 

Está havendo um outro problema.

 

Ela perdeu a função de só enviar e-mail quando alguma célula da coluna M for editada. Depois da alteração que eu fiz, sempre quando muda qualquer célula de qualquer coluna ele envia um e-mail. Às vezes até mesmo 3 e-mails repetidos.

[Charley Rocha]

@DigusX Eu estou interessado em resolver esse seu problema, até mesmo pra eu aprender a solução, porque com o que eu trabalho, é praticamente certo que vou precisar disso em breve.

 

Eu consegui fazer aqui, porém eu receio que minha planilha de teste é muito diferente da sua. O ideal seria você anexar um exemplo da sua planilha pra gente tentar trabalhar na solução.

[Visitante]

Em 27/01/2020 às 07:35, DigusX disse:

... porém ele só está enviando e-mail quando eu edito qualquer célula da coluna M manualmente, mas preciso que ele envie quando qualquer célula da coluna M mudar via fórmula.

 

Já tentou executar o código Worksheet_Change ao ser alterada a célula precedente na fórmula em M ?

Exemplo, se M2 = K2 * 5 ~~~> então o email seria enviado após alteração manual em K2, que é a célula precedente na fórmula de M2.

[Charley Rocha]

1 hora atrás, osvaldomp disse:

 

Já tentou executar o código Worksheet_Change ao ser alterada a célula precedente na fórmula em M ?

Exemplo, se M2 = K2 * 5 ~~~> então o email seria enviado após alteração manual em K2, que é a célula precedente na fórmula de M2.

 

Por isso eu pedi que ele anexasse a planilha. Porque eu por exemplo tenho planilhas cujo relatório final depende de várias e várias células, ficando inviável a vinculação do evento Change para as células de input.

 

Mas caso a planilha dele tenha 1 ou 2 células de input a sua sugestão resolve perfeitamente.

[DigusX]

3 horas atrás, Charley Rocha disse:

@DigusX Eu estou interessado em resolver esse seu problema, até mesmo pra eu aprender a solução, porque com o que eu trabalho, é praticamente certo que vou precisar disso em breve.

 

Eu consegui fazer aqui, porém eu receio que minha planilha de teste é muito diferente da sua. O ideal seria você anexar um exemplo da sua planilha pra gente tentar trabalhar na solução.

 

Planilha anexada!

ExemploTrocadeÓleo - Fórum.rar

[Visitante]

4 horas atrás, Charley Rocha disse:

Mas caso a planilha dele tenha 1 ou 2 células de input a sua sugestão resolve perfeitamente.

 

@Charley Rocha com base no arquivo do post #10, se colocar o evento Worksheet_Change nas colunas E e I da planilha Troca de Óleo então poderá conduzir a alguma solução. É uma ideia.

 

Outra possibilidade seria criar um espelho dos valores da coluna M, dependendo das respostas aos comentários abaixo.

 

@DigusX Você escreveu na planilha ~~~> Desejo que o e-mail seja enviado somente quando alguma célula da coluna M alterar, para Sim ou para Não.

Qual o seu objetivo ao enviar email quando a fórmula mudar para "Não" ?

Por exemplo, o veículo novo iniciou os trabalhos, aí você lança que a primeira troca de óleo será aos 10.000km ou após 6 meses de uso. Nessa situação a fórmula em M mudará de vazio para "Não", e conforme o seu critério, seria enviado email. Qual o seu objetivo ao avisar alguém que não precisa trocar o óleo?

Após atingir um dos critérios (10 kkm ou 6 meses) a fórmula irá mudar para "Sim", então ok, será enviado email para alguém para providenciar a troca do óleo.

Depois da troca a fórmula irá mudar de novo para "Não", e nesse caso, de novo, qual o seu objetivo ao avisar alguém que não precisa trocar ?

Não seria necessário enviar o email somente se a fórmula retornar "Sim" ? Ou há outros fatores envolvidos ?

 

[DigusX]

3 horas atrás, osvaldomp disse:

 

@Charley Rocha com base no arquivo do post #10, se colocar o evento Worksheet_Change nas colunas E e I da planilha Troca de Óleo então poderá conduzir a alguma solução. É uma ideia.

 

Outra possibilidade seria criar um espelho dos valores da coluna M, dependendo das respostas aos comentários abaixo.

 

@DigusX Você escreveu na planilha ~~~> Desejo que o e-mail seja enviado somente quando alguma célula da coluna M alterar, para Sim ou para Não.

Qual o seu objetivo ao enviar email quando a fórmula mudar para "Não" ?

Por exemplo, o veículo novo iniciou os trabalhos, aí você lança que a primeira troca de óleo será aos 10.000km ou após 6 meses de uso. Nessa situação a fórmula em M mudará de vazio para "Não", e conforme o seu critério, seria enviado email. Qual o seu objetivo ao avisar alguém que não precisa trocar o óleo?

Após atingir um dos critérios (10 kkm ou 6 meses) a fórmula irá mudar para "Sim", então ok, será enviado email para alguém para providenciar a troca do óleo.

Depois da troca a fórmula irá mudar de novo para "Não", e nesse caso, de novo, qual o seu objetivo ao avisar alguém que não precisa trocar ?

Não seria necessário enviar o email somente se a fórmula retornar "Sim" ? Ou há outros fatores envolvidos ?

 

 

Quando ela muda para SIM ele envia e-mail dizendo que necessita trocar o óleo.

Quando ela muda para Não ele envia e-mail dizendo que foi trocado o óleo.

 

Até aí está funcionando correto, o problema mesmo é que por algum motivo ele está enviando e-mail quando qualquer célula de qualquer coluna é alterada, manualmente ou via fórmula. Eu preciso que envie e-mail somente quando as células da coluna M forem alteradas via fórmula.

 

Um outro problema é que eu queria que na parte do: 

 

.To = "[email protected]"

 

Ao invés de escrever apenas um único e-mail ele poderia buscar uma lista, tipo: 

 

For i = 2 to 100

.to = "" & Planilha10.Cells(i, 15) & ""

Next i

 

.To = "" & Planilha10.Cells(i, 15) & ""   'adicionar os e-mails contidos nas células da coluna O, da linha 2 à 100. 

 

Mas já tentei e não deu certo.

[Charley Rocha]

@DigusX Consegui aplicar a ideia na sua planilha.

 

Basicamente eu estou usando a coluna N para armazenar o status anterior dos veículos. Você pode ocultar essa coluna que continua funcionando.

 

Tive que criar uma nova aba também.

 

Eu não tive tempo de comentar todo o código, mas acho que você vai conseguir adaptar para sua planilha sem problemas. Mas qualquer dúvida, estamos aqui.

 

ExemploTrocadeÓleo - Fórum.rar

[DigusX]

3 horas atrás, Charley Rocha disse:

@DigusX Consegui aplicar a ideia na sua planilha.

 

Basicamente eu estou usando a coluna N para armazenar o status anterior dos veículos. Você pode ocultar essa coluna que continua funcionando.

 

Tive que criar uma nova aba também.

 

Eu não tive tempo de comentar todo o código, mas acho que você vai conseguir adaptar para sua planilha sem problemas. Mas qualquer dúvida, estamos aqui.

 

ExemploTrocadeÓleo - Fórum.rar 29 kB · 1 download

 

Eu trabalho com muitas planilhas e essa aí em questão, a original aqui possui 11 abas. Às vezes acontece que eu esqueço de olhar essa do Relatório acabando por atrasar a troca do óleo, por esse motivo eu tive a necessidade de fazer com que ela me avisasse por e-mail. Então um botão com macro para disparar não resolve o meu problema. Tem que ser automático assim que a célula da coluna M alterar.

 

No código que eu enviei na planilha, se você trocar If Range("M2").Value <> PrevVal Then por If Target.Address = "$M$" & linha Then e apagar PrevVal = Range("M2").Value ele dispara manualmente também, é só apertar F2 + ENTER, porém manualmente não me ajuda. Tem que ser automático.

[Charley Rocha]

16 horas atrás, DigusX disse:

 

Quando ela muda para SIM ele envia e-mail dizendo que necessita trocar o óleo.

Quando ela muda para Não ele envia e-mail dizendo que foi trocado o óleo.

 

Até aí está funcionando correto, o problema mesmo é que por algum motivo ele está enviando e-mail quando qualquer célula de qualquer coluna é alterada, manualmente ou via fórmula. Eu preciso que envie e-mail somente quando as células da coluna M forem alteradas via fórmula.

 

Um outro problema é que eu queria que na parte do: 

 

.To = "[email protected]"

 

Ao invés de escrever apenas um único e-mail ele poderia buscar uma lista, tipo: 

 

For i = 2 to 100

.to = "" & Planilha10.Cells(i, 15) & ""

Next i

 

.To = "" & Planilha10.Cells(i, 15) & ""   'adicionar os e-mails contidos nas células da coluna O, da linha 2 à 100. 

 

Mas já tentei e não deu certo.

 

A questão do botão para disparar a macro veio para solucionar a questão acima. Se você botar o envio no evento Change ele vai enviar um por um. Por que no momento que você alterar, por exemplo, a quilometragem de algum veículo, o processamento de dados será executado e a célula mudará de "NÃO" para "SIM", logo o e-mail precisa ser disparado nesse momento.

 

Para se enviar o e-mail um por um é bem fácil. Você estava no caminho certo.

adicionado 5 minutos depois

22 minutos atrás, DigusX disse:

 

Eu trabalho com muitas planilhas e essa aí em questão, a original aqui possui 11 abas. Às vezes acontece que eu esqueço de olhar essa do Relatório acabando por atrasar a troca do óleo, por esse motivo eu tive a necessidade de fazer com que ela me avisasse por e-mail. Então um botão com macro para disparar não resolve o meu problema. Tem que ser automático assim que a célula da coluna M alterar.

 

No código que eu enviei na planilha, se você trocar If Range("M2").Value <> PrevVal Then por If Target.Address = "$M$" & linha Then e apagar PrevVal = Range("M2").Value ele dispara manualmente também, é só apertar F2 + ENTER, porém manualmente não me ajuda. Tem que ser automático.

 

É muito diferente você ir mandando e-mails um por um à medida que vai alterando os inputs das células do que você apertar apenas um botão para enviar automaticamente dezenas de e-mails. Tenta adicionar mais carros na planilha que você vai conseguir ver a vantagem disso.

[DigusX]

4 minutos atrás, Charley Rocha disse:

 

A questão do botão para disparar a macro veio para solucionar a questão acima. Se você botar o envio no evento Change ele vai enviar um por um. Por que no momento que você alterar, por exemplo, a quilometragem de algum veículo, o processamento de dados será executado e a célula mudará de "NÃO" para "SIM", logo o e-mail precisa ser disparado nesse momento.

 

Para se enviar o e-mail um por um é bem fácil. Você estava no caminho certo.

Exatamente isso. Só preciso que ele envie quando mudar de Sim para Não ou de Não para Sim. Para me deixar em alerta.

 

Em relação ao botão, até bolar uma coluna de e-mails eu posso ir usando:

 

.To = "[email protected]; [email protected]; [email protected]"

 

A coluna de e-mail ajudaria só pra não ter que ficar acessando o código sempre que querer fazer alguma alteração.

[Charley Rocha]

23 minutos atrás, DigusX disse:

Exatamente isso. Só preciso que ele envie quando mudar de Sim para Não ou de Não para Sim. Para me deixar em alerta.

 

Em relação ao botão, até bolar uma coluna de e-mails eu posso ir usando:

 

.To = "[email protected]; [email protected]; [email protected]"

 

A coluna de e-mail ajudaria só pra não ter que ficar acessando o código sempre que querer fazer alguma alteração.

 

A coluna de e-mails já está pronta. Está na aba extra que eu criei. Você altera todas as quilometragens da planilha e ele vai preenchendo as colunas com os e-mails para disparar depois.

 

A última coisa que você faz é apertar o botão da macro.

 

P.S.: Perdão porque eu anexei a planilha sem te explicar como usar. 

[Visitante]

Com base no que comentei no post #12, segue uma ideia para testes no arquivo anexado.

ExemploTrocadeÓleo - Fórum V2.zip

[DigusX]

12 horas atrás, osvaldomp disse:

Com base no que comentei no post #12, segue uma ideia para testes no arquivo anexado.

ExemploTrocadeÓleo - Fórum V2.zip 27 kB · 2 downloads

Você me entendeu muito bem. Eu edito manualmente a aba Troca de Óleo e aí a do Relatório muda automaticamente, nesse momento ela dispara o e-mail, ou então quando a planilha for salva e reaberta de novo. Perfeito!

[Patropi]

@DigusX

 

Você está se esquecendo de clicar em Curtir nas respostas dos colaboradores quando forem úteis.

Quando a dúvida estiver sanada, marque o tópico como Resolvido. (Na minha assinatura tem um link para uma instrução de como proceder).

 

[]s

 

[DigusX]

Em 30/01/2020 às 19:58, osvaldomp disse:

Com base no que comentei no post #12, segue uma ideia para testes no arquivo anexado.

ExemploTrocadeÓleo - Fórum V2.zip 27 kB · 4 downloads

E agora, como adiciona o códio do e-mail?

[Visitante]

1 hora atrás, DigusX disse:

E agora, como adiciona o códio do e-mail? ~~~> Sugestão: inclua no seu repertório as expressões "por favor" e "obrigado".

 

Em 31/01/2020 às 08:32, DigusX disse:

... nesse momento ela dispara o e-mail, ... ~~~> eu entendi que você já havia resolvido como enviar o email

 

Para cada veículo, como vamos saber o destinatário do email e também os demais dados como cc, título, texto, ... ?

Ainda, responda a pergunta acima para M = "Sim" e para M = "Não".

 

 

[DigusX]

4 horas atrás, osvaldomp disse:

 

 

Para cada veículo, como vamos saber o destinatário do email e também os demais dados como cc, título, texto, ... ?

Ainda, responda a pergunta acima para M = "Sim" e para M = "Não".

 

 

 

Boa tarde! Tudo bem? mais uma vez obrigado pela a atenção.

 

Quando M mudar para sim:

            texto = "Prezado(a), " & vbCrLf & vbCrLf & _
                    "O veículo: " & Planilha10.Cells(linha, 3) & " necessita trocar o óleo do motor" & "." & vbCrLf & vbCrLf & _
                    "Veja algumas informações abaixo:" & vbCrLf & vbCrLf & _
                    "Km total rodado: " & Planilha10.Cells(linha, 5) & vbCrLf & _
                    "Km da última troca: " & Planilha10.Cells(linha, 9) & vbCrLf & _
                    "Km restante para a próxima troca: " & Planilha10.Cells(linha, 12) & vbCrLf & _
                    "Data da última troca: " & Planilha10.Cells(linha, 6) & vbCrLf & _
                    "Data do vencimento: " & Planilha10.Cells(linha, 6) + 183 & vbCrLf & vbCrLf & _
                    "Atenciosamente," & vbCrLf & _
                    "Empresa X"

 

Quando M mudar para não:

 

            texto = "Prezado(a), " & vbCrLf & vbCrLf & _
                    "Foi trocado o óleo do motor do veículo: " & Planilha10.Cells(linha, 3) & vbCrLf & vbCrLf & _
                    "Veja algumas informações abaixo:" & vbCrLf & vbCrLf & _
                    "Km total rodado: " & Planilha10.Cells(linha, 5) & vbCrLf & _
                    "Km da troca: " & Planilha10.Cells(linha, 9) & vbCrLf & _
                    "Km restante para a próxima troca: " & Planilha10.Cells(linha, 12) & vbCrLf & _
                    "Data da troca: " & Planilha10.Cells(linha, 6) & vbCrLf & _
                    "Data do vencimento: " & Planilha10.Cells(linha, 6) + 183 & vbCrLf & vbCrLf & _
                    "Atenciosamente," & vbCrLf & _
                    "Empresa X"

 

Destinatário:

           

            .To = "[email protected]" 
            .CC = ""
            .BCC = ""
            .Subject = "Troca de óleo - Veículos Empresa X" 'Título do e-mail
            .Body = texto
            .Display 'Use Send para enviar sem abrir o Outlook

 

[Visitante]

Com base no seu último post me parece que você já tem praticamente prontos os códigos para enviar os emails, então eu sugiro que você os complete, gerando assim dois códigos, Sub TrocarÓleo() e Sub ÓleoTrocado(), instale-os em um módulo comum, e nos dois códigos que eu sugeri no arquivo do post #18 substitua o comando MsgBox pelo trecho de código abaixo.

 

   

If .Cells(x.Row, 13) = "Sim" Then
 TrocarÓleo
ElseIf .Cells(x.Row, 13) = "Não" Then
 ÓleoTrocado
End If
  

 

[DigusX]

Em 03/02/2020 às 15:15, osvaldomp disse:

Com base no seu último post me parece que você já tem praticamente prontos os códigos para enviar os emails, então eu sugiro que você os complete, gerando assim dois códigos, Sub TrocarÓleo() e Sub ÓleoTrocado(), instale-os em um módulo comum, e nos dois códigos que eu sugeri no arquivo do post #18 substitua o comando MsgBox pelo trecho de código abaixo.

 

   

If .Cells(x.Row, 13) = "Sim" Then
 TrocarÓleo
ElseIf .Cells(x.Row, 13) = "Não" Then
 ÓleoTrocado
End If
  

 

Eu substituir mas não funcionou.