Ir para o conteúdo principal

Como enviar e-mail se a data de vencimento foi cumprida no Excel?

Conforme mostrado na captura de tela abaixo, se a data de vencimento na coluna C for menor ou igual a 7 dias (por exemplo, a data atual é 2017/9/13), um e-mail será enviado ao destinatário especificado na coluna A e o o conteúdo especificado na coluna B é exibido no corpo do e-mail. Como você poderia fazer para alcançá-lo? Este artigo fornece um código VBA para ajudá-lo a realizar essa tarefa.

Envie um e-mail se a data de vencimento for cumprida com o código VBA


Envie um e-mail se a data de vencimento for cumprida com o código VBA

Faça o seguinte para enviar um lembrete por e-mail caso a data de vencimento tenha sido cumprida no Excel.

1. aperte o outro + F11 simultaneamente para abrir o Microsoft Visual Basic para Aplicações janela.

2. No Microsoft Visual Basic para Aplicações janela, por favor clique inserção > Módulo. Em seguida, copie e cole o código VBA abaixo na janela Módulo.

Código VBA: enviar e-mail se a data de vencimento estiver fechada no Excel

Public Sub CheckAndSendMail()
'Updated by Extendoffice 2018/11/22
    Dim xRgDate As Range
    Dim xRgSend As Range
    Dim xRgText As Range
    Dim xRgDone As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xLastRow As Long
    Dim vbCrLf As String
    Dim xMailBody As String
    Dim xRgDateVal As String
    Dim xRgSendVal As String
    Dim xMailSubject As String
    Dim i As Long
    On Error Resume Next
    Set xRgDate = Application.InputBox("Please select the due date column:", "KuTools For Excel", , , , , , 8)
    If xRgDate Is Nothing Then Exit Sub
    Set xRgSend = Application.InputBox("Please select the recipients?email column:", "KuTools For Excel", , , , , , 8)
    If xRgSend Is Nothing Then Exit Sub
    Set xRgText = Application.InputBox("Select the column with reminded content in your email:", "KuTools For Excel", , , , , , 8)
    If xRgText Is Nothing Then Exit Sub
    xLastRow = xRgDate.Rows.count
    Set xRgDate = xRgDate(1)
    Set xRgSend = xRgSend(1)
    Set xRgText = xRgText(1)
    Set xOutApp = CreateObject("Outlook.Application")
    For i = 1 To xLastRow
        xRgDateVal = ""
        xRgDateVal = xRgDate.Offset(i - 1).Value
        If xRgDateVal <> "" Then
        If CDate(xRgDateVal) - Date <= 7 And CDate(xRgDateVal) - Date > 0 Then
            xRgSendVal = xRgSend.Offset(i - 1).Value
            xMailSubject = xRgText.Offset(i - 1).Value & " on " & xRgDateVal
            vbCrLf = "<br><br>"
            xMailBody = "<HTML><BODY>"
            xMailBody = xMailBody & "Dear " & xRgSendVal & vbCrLf
            xMailBody = xMailBody & "Text : " & xRgText.Offset(i - 1).Value & vbCrLf
            xMailBody = xMailBody & "</BODY></HTML>"
            Set xMailItem = xOutApp.CreateItem(0)
            With xMailItem
                .Subject = xMailSubject
                .To = xRgSendVal
                .HTMLBody = xMailBody
                .Display
                '.Send
            End With
            Set xMailItem = Nothing
        End If
    End If
    Next
    Set xOutApp = Nothing
End Sub

Notas: A linha Se CDate (xRgDateVal) - Data <= 7 E CDate (xRgDateVal) - Data> 0 Então, no código VBA, significa que a data de vencimento deve ser maior que 1 dia e menor ou igual a 7 dias. Você pode alterá-lo conforme necessário.

3. Pressione do Tecla F5 para executar o código. Na primeira aparição Kutools for Excel caixa de diálogo, selecione o intervalo da coluna de data de vencimento e clique no botão OK botão. Veja a imagem:

4. Então o segundo Kutools for Excel caixa de diálogo aparece, selecione o intervalo de coluna correspondente que contém os endereços de e-mail dos destinatários e clique no botão OK botão. Veja a imagem:

5. No último Kutools for Excel caixa de diálogo, selecione o conteúdo que deseja exibir no corpo do e-mail e clique no OK botão.

Em seguida, um e-mail será criado automaticamente com o destinatário, assunto e corpo especificados listados se a data de vencimento na coluna C for menor ou igual a 7 dias. Por favor clique no ENVIAR botão para enviar o e-mail.

Notas:

1. Cada e-mail criado corresponde a uma data de vencimento. Por exemplo, se houver três datas de vencimento que atendam aos critérios, três mensagens de e-mail serão criadas automaticamente.

2. Este código não será acionado se não houver datas que atendam aos critérios.

3. O código VBA só funciona quando você usa o Outlook como seu programa de e-mail.


Artigos relacionados:

Melhores ferramentas de produtividade de escritório

Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com Kutools for Excele experimente eficiência como nunca antes. Kutools for Excel Oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

guia kte 201905


Office Tab Traz a interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Comments (126)
Rated 4.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
I want to apply this macro to different sheets in my workbook, but each sheet is different. Adding a second module means the first one no longer works.

Could you advise me please?
This comment was minimized by the moderator on the site
Hi Annie,

The code can be applied to different worksheets, not just the current one. After running the code, select the desired worksheet tab and then the cell range.
This comment was minimized by the moderator on the site
Olá, eu trabalho com calibrações de equipamentos controlados pelo inmetro, eu fiz uma planilha com a data de vencimento da calibração de cada equipamento, é possível quando a data estiver chegando próximo ao vencimento tipo uns 30 dias, o excel enviar um email automático para que eu possa lembrar?
This comment was minimized by the moderator on the site
Bonjour , je suis nouveau sur VBA

Comment faire pour quand les dates change ?
This comment was minimized by the moderator on the site
Hi theo charvet,

Sorry I don't quite understand your question. For clarity, please attach a screenshot with your data and desired results.
This comment was minimized by the moderator on the site
Hallo Zusammen,

ich möchte an die generierte Email immer die gleiche Datei anhägen.
Ist das irgendwie machbar? Ich bedanke mich recht herzlich vorab.

Hello all,

I would like to attach always the same file to the generated email.
Is this somehow possible? Thank you very much in advance.
This comment was minimized by the moderator on the site
Hi Sandro,

You need to add the following line above the .Display line in the VBA code.
Please replace the file path with the file path of your own.
.Attachments.Add "D:\Work\Month\Dec\Word.docx"
This comment was minimized by the moderator on the site
Hallo Zusammen,

danke für den Code.

Ich möchte an die generierte Email, immer den gleichen Anhang setzten. Mit meinem primitiven Versuch:

.attachments.add "Pfad\Dateiname" bin ich leider nicht weiter gekommen.

Kann mir hier vielleicht wer helfen? :)
This comment was minimized by the moderator on the site
Hi ,

I was using this and everything goes well but after step 5 I didn't see send button , please help. I need this very urgently.
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
Hi Vani,
Does the new message window pop up? The Send button displays in the message window.
If there is no eligible date, the message will not be created.
This comment was minimized by the moderator on the site
Hi!
I am trialling and it seems that always need to open and run the module for the email to be created.
How do I automatically run this even if the worksheet is not open?
This comment was minimized by the moderator on the site
Hi Mychel,
Can you describe the problem more clearly? By the way, you can't run a macro if the workbook is not open.
This comment was minimized by the moderator on the site
Hi,

Can this code be amended where it will send two lines of information to one recipient? Say i have two due dates, rather than sending two emails to the same person, can they be merged into one?

Thanks
A
This comment was minimized by the moderator on the site
Hi,
Suppose there are two tasks are assiged to the same recipient. When the due dates of these two tasks meet the conditions, an email is generated that includes the corresponding information of the tasks in the email body. Please try the following VBA code. Hope I can help.

Public Sub CheckAndSendMail2()
'Updated by Extendoffice 2022/08/23
    Dim xRgDate As Range
    Dim xRgSend As Range
    Dim xRgText As Range
    Dim xRgDone As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xLastRow, xJ As Long
    Dim vbCrLf As String
    Dim xMailBody As String
    Dim xRgDateVal As String
    Dim xRgSendVal As String
    Dim xMailSubject As String
    Dim xStrMail, xStrFind As String
    Dim xBol As Boolean
    Dim i As Long
  ' On Error Resume Next
    Set xRgDate = Application.InputBox("Please select the due date column:", "KuTools For Excel", , , , , , 8)
    If xRgDate Is Nothing Then Exit Sub
    Set xRgSend = Application.InputBox("Please select the recipients?email column:", "KuTools For Excel", , , , , , 8)
    If xRgSend Is Nothing Then Exit Sub
    Set xRgText = Application.InputBox("Select the column with reminded content in your email:", "KuTools For Excel", , , , , , 8)
    If xRgText Is Nothing Then Exit Sub
    xLastRow = xRgDate.Rows.Count
    Set xRgDate = xRgDate(1)
    Set xRgSend = xRgSend(1)
    Set xRgText = xRgText(1)
  Set xOutApp = CreateObject("Outlook.Application")
    xStrMail = ""
    For i = 1 To xLastRow
        xRgDateVal = ""
        xRgDateVal = xRgDate.Offset(i - 1).Value
        xBol = True
        If xRgDateVal <> "" Then
        If CDate(xRgDateVal) - Date <= 7 And CDate(xRgDateVal) - Date > 0 Then
            xRgSendVal = xRgSend.Offset(i - 1).Value
            xStrFind = xRgSendVal & ";"
            If InStr(xStrMail, xStrFind) > 0 Then
                xBol = False
            End If
            If xBol Then
            xStrMail = xStrMail & xStrFind
            xMailSubject = xRgText.Offset(i - 1).Value & " on " & xRgDateVal
            vbCrLf = "<br><br>"
            xMailBody = "<HTML><BODY>"
            xMailBody = xMailBody & "Dear " & xRgSendVal & vbCrLf
            xMailBody = xMailBody & "Text : " & xRgText.Offset(i - 1).Value & vbCrLf
            For xJ = i + 1 To xLastRow
                If CDate(xRgDate.Offset(xJ - 1).Value) - Date <= 7 And CDate(xRgDate.Offset(xJ - 1).Value) - Date > 0 Then
                    If xRgSendVal = xRgSend.Offset(xJ - 1).Value Then
                        xMailBody = xMailBody & "Text : " & xRgText.Offset(xJ - 1).Value & vbCrLf
                    End If
                End If
            Next
            xMailBody = xMailBody & "</BODY></HTML>"
            Set xMailItem = xOutApp.CreateItem(0)
            With xMailItem
                .Subject = xMailSubject
                .To = xRgSendVal
                .HTMLBody = xMailBody
                .Display
                '.Send
            End With
            Set xMailItem = Nothing
        End If
        End If
    End If
    Next
    Set xOutApp = Nothing
End Sub
This comment was minimized by the moderator on the site
Hello, I have an additional column. 'Subject' column with different subject for each mail. How do I add the Subject Loop in the above VBA Code?
This comment was minimized by the moderator on the site
Hi Pranay,
The following code can do you a favor. Please give it a try.
Public Sub CheckAndSendMail()
'Updated by Extendoffice 20220729
    Dim xRgDate As Range
    Dim xRgSend As Range
    Dim xRgSubject As Range
    Dim xRgText As Range
    Dim xRgDone As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xLastRow As Long
    Dim vbCrLf As String
    Dim xMailBody As String
    Dim xRgDateVal As String
    Dim xRgSendVal As String
    'Dim xRgSubject As String
    Dim i As Long
    On Error Resume Next
    Set xRgDate = Application.InputBox("Please select the due date column:", "KuTools For Excel", , , , , , 8)
    If xRgDate Is Nothing Then Exit Sub
    Set xRgSend = Application.InputBox("Please select the recipients' email column:", "KuTools For Excel", , , , , , 8)
    If xRgSend Is Nothing Then Exit Sub
    Set xRgSubject = Application.InputBox("Please select the subject column:", "KuTools For Excel", , , , , , 8)
    If xRgSubject Is Nothing Then Exit Sub
    Set xRgText = Application.InputBox("Select the column with reminded content in your email:", "KuTools For Excel", , , , , , 8)
    If xRgText Is Nothing Then Exit Sub
    xLastRow = xRgDate.Rows.Count
    Set xRgDate = xRgDate(1)
    Set xRgSend = xRgSend(1)
    Set xRgSubject = xRgSubject(1)
    Set xRgText = xRgText(1)
    Set xOutApp = CreateObject("Outlook.Application")
    For i = 1 To xLastRow
        xRgDateVal = ""
        xRgDateVal = xRgDate.Offset(i - 1).Value
        If xRgDateVal <> "" Then
        If CDate(xRgDateVal) - Date <= 7 And CDate(xRgDateVal) - Date > 0 Then
            xRgSendVal = xRgSend.Offset(i - 1).Value
            xMailSubject = xRgSubject.Offset(i - 1).Value & " on " & xRgDateVal
            vbCrLf = "<br><br>"
            xMailBody = "<HTML><BODY>"
            xMailBody = xMailBody & "Dear " & xRgSendVal & vbCrLf
            xMailBody = xMailBody & "Text : " & xRgText.Offset(i - 1).Value & vbCrLf
            xMailBody = xMailBody & "</BODY></HTML>"
            Set xMailItem = xOutApp.CreateItem(0)
            With xMailItem
                .Subject = xMailSubject
                .To = xRgSendVal
                .HTMLBody = xMailBody
                .Display
                '.Send
            End With
            Set xMailItem = Nothing
        End If
    End If
    Next
    Set xOutApp = Nothing
End Sub
This comment was minimized by the moderator on the site
Merhaba, değer 50 nin altına düşünce otomatik olarak maili atıyor ama formül düşürünce maili otomatik olarak atmıyor bunun için ne yapabilirim?
This comment was minimized by the moderator on the site
Hi Metin,
I thought you may have gotten into the wrong article. To send an email automatically based on a cell value (contains a formula), you can try the following VBA code.
Here is the orginal article: How To Automatically Send Email Based On Cell Value In Excel?

Dim xRg As Range
'Update by Extendoffice 20220729
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xBoolean As Boolean
    Dim xItsRG As Range
    Dim xDDs As Range
    Dim xDs As Range
    On Error Resume Next
    xBoolean = False
    Set xRg = Range("D7")
    Set xItsRG = Intersect(Target, xRg)
    Set xDDs = Intersect(Target.DirectDependents, xRg)
    Set xDs = Intersect(Target.Dependents, xRg)
    If Not (xItsRG Is Nothing) Then
        xBoolean = True
    ElseIf Not (xDDs Is Nothing) Then
        xBoolean = True
    ElseIf Not (xDs Is Nothing) Then
        xBoolean = True
    End If

    If Not xBoolean Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
  'Set xRg = Intersect(Range("D7"), Target)
    'If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value < 50 Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2"
    On Error Resume Next
    With xOutMail
        .To = "Email Address"
        .CC = ""
        .BCC = ""
        .Subject = "send by cell value test"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations