vba 根据日期自动从excel发送电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31189863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sending email from excel automatically based on date
提问by Reg Robinson
I have an excel sheet with dated scheduling information. I would like to send daily updates on how many calls and how many appointments have been scheduled every day. The spreadsheet looks as follows:
我有一个带有日期调度信息的 Excel 表。我想每天发送关于每天有多少电话和有多少约会的更新。电子表格如下所示：
Date Scheduled Called Notes
07/06/2015 0 5 None
07/07/2015 5 12 None
07/08/2015 2 10 None
I am trying to write a program that, say on 7/06/2015, an email will be generated with that days scheduled, calls, and notes in the body and automatically sent. Is this possible?
我正在尝试编写一个程序，例如在 2015 年 7 月 6 日，将生成一封电子邮件，其中包含安排的日期、电话和正文中的注释并自动发送。这可能吗？
回答by Dportology
Here's what I think could be a solid start. You'll obviously have to resolve what email address the message should be sent to and how to format the body and whatnot.
这就是我认为可能是一个坚实的开始。显然，您必须解决消息应发送到的电子邮件地址以及如何格式化正文等等。
The range given to r was based on the sample data you provided, which occupied A2-A4, but change this to whatever is correct.
给 r 的范围基于您提供的样本数据，它占据了 A2-A4，但将其更改为正确的值。
Option Explicit
Sub email()
Dim r As Range
Dim cell As Range
Set r = Range("A2:A4")
For Each cell In r
If cell.Value = Date Then
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "subject"
Email_Send_From = "[email protected]"
Email_Send_To = "[email protected]"
Email_Cc = "[email protected]"
Email_Bcc = "[email protected]"
Email_Body = "body"
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.send
End With
End If
Next
Exit Sub
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
回答by Ali Z
This is somewhat late but can be helpful to you and others:
这有点晚了，但可以对您和其他人有所帮助：
- Use the above answer to define the notifications.
Create VB-script file with this code: (open notepad, paste code below, fit to your needs, save as: vbscript file)
Path="complete_file_path_of_your_excel_file.xlsm" Macro="email" Set objApp = CreateObject("Excel.Application.16") objApp.Visible = True Set wbToRun = objApp.Workbooks.Open(Path) objApp.Run MacroSet Windows Task Scheduler to open the VBscript file you created on a daily basis at a certain time.
You now have a set-and-forget notification bot.
- 使用上述答案来定义通知。
使用此代码创建 VB 脚本文件：（打开记事本，粘贴下面的代码，根据您的需要，另存为：vbscript 文件）
Path="complete_file_path_of_your_excel_file.xlsm" Macro="email" Set objApp = CreateObject("Excel.Application.16") objApp.Visible = True Set wbToRun = objApp.Workbooks.Open(Path) objApp.Run Macro将 Windows 任务计划程序设置为每天在特定时间打开您创建的 VBscript 文件。
您现在拥有了一个一劳永逸的通知机器人。
