如果到期日期，如何在达到截止日期时发送电子邮件自动发送电子邮件，如果达到截止日期，则每十天提醒一次并提醒三次？ [英] How to send email automated email when due date is going to be reached, has been reached and three times reminder every ten days if the due date has been reached?

问题描述

我对VBA代码非常新，并且真的需要帮助，所以如果
从现在开始10天后我想要自动发送电子邮件截止日期将是达到，并且
s 如果他们在截止日期尚未发送项目时再次结束，则再次发送
三到期日后每十天一次，但
如果他们已经发送了他们的项目，则停止发送电子邮件以及如何将这些电子邮件发送给某人和CC发送给他/她小组中的其他人的电子邮件？我为此项目提供了
两个工作表，第一个工作表包含有关
项目编号，项目名称，费用，
截止日期，状态（收到与否，如果收到的电子邮件将不会被发送），
领导者的电子邮件。

I am very new with VBA code and really need help about this, so I want to send email automatically if 10 days from now a due date is going to be reached, and send again if they haven't send their projects when the due date has been reached, and send again three times every ten days after the due date has been reached, but stop sending email if they already sent their projects, and how to send these emails to a person and CC the email to everyone else in his/her group? I have two worksheet for this project, first worksheet contains columns about project number, project name, cost, due date, status(received or not, if received email will not be sent), leader's email.

电子邮件正文：

您好，

我想要提醒您的项目：

项目编号

项目名称

费用

截止日期

您的截止日期即将在十天内完成（首次提醒）

您的截止日期已到达（第二次提醒）

您的截止日期已经延迟了十天（第三次提醒）

您的截止日期已经延迟了20天（第四天）提醒）

您的截止日期已经过了e三十天（第五次提醒）

谢谢

第二个工作表包含有关领导者电子邮件的专栏，以及他/她小组中的其他3封电子邮件。

Second worksheets contains column about leader's email, and 3 other emails in his/her group.

 我真的希望有人可以帮我解决这个问题，谢谢

 I really hope somebody can help me with this problem, thank you

真诚地

Tomuko Sitorus

Tomuko Sitorus

[e-mail]

[e-mail]

推荐答案

您好
Tomuko Sitorus，

以下是您可能尝试参考的详细示例。

Below is a detailed example you may try to refer.

以下是Sheet1。

下面是Sheet2。

以下是基于您的要求的VBA代码。

Below is a VBA code based on your requirement.

Sub demo()
Dim i As Long
Dim sht, sht2 As Worksheet
Dim LastRow, LastRow1 As Long
Dim days, project_no As Integer
Set sht = Sheets(1)
Set sht2 = Sheets(2)
 LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
 LastRow1 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
project_no = sht.Cells(i, 1).Value
'sht.Range("F" & i).Value = DateDiff("d", Date, sht.Range("D" & i).Value)
days = DateDiff("d", Date, sht.Range("D" & i).Value)
If days = 10 And sht.Range("E" & i).Value = "not received" Then
    Call demo2(project_no)
ElseIf days = 0 And sht.Range("E" & i).Value = "not received" Then
    Call demo2(project_no)
   
ElseIf days = -10 And sht.Range("E" & i).Value = "not received" Then
     Call demo2(project_no)
    
ElseIf days = -20 And sht.Range("E" & i).Value = "not received" Then
     Call demo2(project_no)
     
ElseIf days = -30 And sht.Range("E" & i).Value = "not received" Then
     Call demo2(project_no)
     
Else

End If
Next i

End Sub

Sub demo2(pro_no As Integer)
Dim i As Long
Dim sht2 As Worksheet
Dim LastRow1 As Long
Dim days, project_no As Integer
Dim mail_to, cc, body As String
Set sht2 = Sheets(2)

 LastRow1 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
 For i = 2 To LastRow1
    project_no = sht2.Range("A" & i).Value
    If project_no = pro_no Then
    mail_to = sht2.Range("B" & i).Value
    cc = sht2.Range("C" & i).Value
    body = "Try to check the due date of your project number " & pro_no
    Call send_mail(mail_to, cc, body)
    End If
 Next i
 
End Sub

Sub send_mail(ByVal mail_to As String, ByVal cc As String, ByVal body As String)
Dim aOutlook As Object
Dim aEmail As Object


Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)

aEmail.Importance = 2
'Set Subject
aEmail.Subject = "Project Reminder Mail"
'Set Body for mail
aEmail.body = body
'Set attachment
'aEmail.ATTACHMENTS.Add ActiveWorkbook.FullName
'Set Recipient
aEmail.to = mail_to
'Set cc
aEmail.cc = cc
'Send Mail
aEmail.Send

End Sub

如果你想在检查和发送邮件时想要运行它，你需要将所有子项放入一个模块。

You need to put all the sub in to one module if you want to run it when you want to check and send mail.

其他选项，您可以使用工作簿事件并选择所需的事件来运行上面的代码。

Other option you can use Workbook events and choose desired event to run the code above.

此外，您可以尝试理解代码，您可以根据需要修改代码和邮件正文。

Further, You can try to understand the code and you can modify the code and mail body according to your need.

如果你在某些地方停留并需要帮助来实施代码，请告诉我们。

If you stuck some where and need help to implement the code then let us know about that.

我们会尽力为您提供进一步的建议解决问题。

We will try to provide you further suggestions to solve the issue.

问候

Deepak

这篇关于如果到期日期，如何在达到截止日期时发送电子邮件自动发送电子邮件，如果达到截止日期，则每十天提醒一次并提醒三次？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！