ARTICLE · 1359760
用 AI 写 Excel VBA ⑨:定时自动生成日报并邮件发出
系列名称:用 AI 写 Excel VBA|20 节零基础实战课
适用环境:Microsoft 365 / Excel 2016+ 桌面版,Windows + Outlook 桌面客户端
阅读时长:约 12 分钟
难度:⭐⭐⭐⭐(实战进阶篇)
🎯 一句话成果
今天你拿到两段代码:定时触发器 + 自动发邮件宏。设好时间,每天到点 Excel 自动刷新数据、生成日报、通过 Outlook 发给指定收件人——你人不在电脑前,报表照样准时送达。

📌 场景痛点
你是不是每天都有这个固定动作:
下午 4:50,打开销售总表 刷新数据透视表 复制关键数据到日报模板 保存为"日报_2026-11-15.xlsx" 打开 Outlook,新建邮件,写收件人,写正文,附附件 点发送 松一口气,看一眼时间——5:20 了
每天 30 分钟,一年就是 180 小时。 更烦的是:出差、请假、开会错过时间,领导没收到报表,微信问你"今天的日报呢?"
VBA 有两个功能组合起来正好解决这个问题:
Application.OnTime:到点自动执行指定宏 - Outlook 对象
:用代码新建邮件、加附件、发送
两个一拼,就是一套无人值守的日报系统。
🧠 先定方案(让 AI 明确架构)
项目 | 设定 |
|---|---|
数据源 | 当前工作簿的"日报"工作表(已排版好) |
定时任务 | 每天 17:00 自动执行 |
邮件收件人 | 从单元格读取(方便随时改) |
邮件主题 |
|
邮件正文 | 含关键数据摘要(自动填入) |
附件 | 当日日报文件(另存为带日期的副本) |
发送方式 | 直接发送(不用手动点) |
🤖 这一讲用的提示词(直接抄)
【角色】你是资深 Excel VBA 专家,擅长定时任务和 Outlook 自动化。【场景】当前工作簿有一个"日报"工作表(第1行标题,第2行起数据),已排版完成。另有一个"配置"工作表,A1放收件人邮箱,A2放抄送邮箱。还有一个"汇总"工作表,B2是今日总销售额,B3是今日订单数。【动作】写两个宏:宏1「生成并发送日报」:1. 读取"汇总"表的今日数据2. 把"日报"表另存为带日期的文件(如 日报_2026-11-15.xlsx),存到"日报存档"文件夹3. 通过 Outlook 新建邮件:- 收件人:读"配置"表A1- 抄送:读"配置"表A2- 主题:销售日报 - 2026年11月15日- 正文:含今日总销售额、订单数的摘要文字- 附件:刚保存的日报文件4. 直接发送5. 发送成功后在"配置"表记录发送时间宏2「设置定时任务」:1. 每天17:00自动执行「生成并发送日报」2. 执行完自动安排第二天的任务(循环)【规则】- 使用 Late Binding(CreateObject),不引用 Outlook 库- 附件路径含中文要正确处理- 发送前检查收件人是否为空- 带错误处理,出错时弹窗提示- 提供「取消定时任务」的宏【输出】完整可运行代码,中文注释,On Error 兜底。
📋 AI 给的代码(完整可运行)

宏1:生成并发送日报
Sub 生成并发送日报()'========================================'功能:保存日报副本 → Outlook 发送带附件的邮件'前提:有"日报""汇总""配置"三个工作表'========================================Dim wsReport As Worksheet, wsSummary As Worksheet, wsConfig As WorksheetDim olApp As Object, olMail As ObjectDim saveFolder As String, savePath As String, fileName As StringDim recipient As String, ccRecipient As StringDim totalSales As Double, orderCount As LongDim mailBody As StringDim todayStr As StringOn Error GoTo ErrorHandlerApplication.ScreenUpdating = FalseSet wsReport = ThisWorkbook.Sheets("日报")Set wsSummary = ThisWorkbook.Sheets("汇总")Set wsConfig = ThisWorkbook.Sheets("配置")'---- 读取配置 ----recipient = Trim(CStr(wsConfig.Range("A1").Value))ccRecipient = Trim(CStr(wsConfig.Range("A2").Value))If recipient = "" ThenMsgBox "「配置」表 A1 未填写收件人邮箱!", vbExclamationGoTo CleanUpEnd If'---- 读取数据 ----totalSales = Val(wsSummary.Range("B2").Value)orderCount = Val(wsSummary.Range("B3").Value)todayStr = Format(Date, "yyyy年m月d日")'---- 保存日报副本 ----saveFolder = ThisWorkbook.Path & "\日报存档\"If Dir(saveFolder, vbDirectory) = "" Then MkDir saveFolderfileName = "日报_" & Format(Date, "yyyy-mm-dd") & ".xlsx"savePath = saveFolder & fileName'复制日报表到新工作簿再保存(避免带上其他表和宏)wsReport.CopyActiveWorkbook.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbookActiveWorkbook.Close SaveChanges:=False'---- 构建邮件正文 ----mailBody = "各位好," & vbCrLf & vbCrLfmailBody = mailBody & "附件为 " & todayStr & " 销售日报,核心数据如下:" & vbCrLf & vbCrLfmailBody = mailBody & " 今日总销售额:" & Format(totalSales, "#,##0.00") & " 元" & vbCrLfmailBody = mailBody & " 今日订单数:" & orderCount & " 单" & vbCrLf & vbCrLfmailBody = mailBody & "详细数据见附件。" & vbCrLf & vbCrLfmailBody = mailBody & "(本邮件由 Excel 自动发送)"'---- 创建 Outlook 邮件(Late Binding)----Set olApp = CreateObject("Outlook.Application")Set olMail = olApp.CreateItem(0) ' 0 = olMailItemWith olMail.To = recipientIf ccRecipient <> "" Then .CC = ccRecipient.Subject = "销售日报 - " & todayStr.Body = mailBody.Attachments.Add savePath.Send ' 直接发送(用 .Display 则弹出窗口手动确认)End With'---- 记录发送时间 ----wsConfig.Range("B1").Value = "最后发送:" & NowApplication.ScreenUpdating = True'如果在定时任务中静默运行,不弹窗If Application.RunMode = True Then' 静默模式不弹窗ElseMsgBox "日报已发送至:" & recipient, vbInformationEnd IfExit SubErrorHandler:Application.ScreenUpdating = TrueMsgBox "发送失败:" & Err.Description & vbCrLf & _"请检查 Outlook 是否已登录。", vbCriticalEnd Sub
宏2:设置 / 取消定时任务
'===== 模块顶部声明(必须放在所有过程之前)=====Dim nextRunTime As DateSub 设置定时任务()'设定每天 17:00 执行nextRunTime = Date + TimeValue("17:00:00")'如果今天 17:00 已过,顺延到明天If nextRunTime <= Now Then nextRunTime = nextRunTime + 1Application.OnTime EarliestTime:=nextRunTime, _Procedure:="定时任务入口", _Schedule:=TrueMsgBox "定时任务已设置!" & vbCrLf & _"下次执行时间:" & Format(nextRunTime, "yyyy-mm-dd hh:nn:ss"), vbInformationEnd SubSub 定时任务入口()'先执行发送生成并发送日报'再安排下一次(循环)nextRunTime = nextRunTime + 1Application.OnTime EarliestTime:=nextRunTime, _Procedure:="定时任务入口", _Schedule:=TrueEnd SubSub 取消定时任务()On Error Resume NextApplication.OnTime EarliestTime:=nextRunTime, _Procedure:="定时任务入口", _Schedule:=FalseOn Error GoTo 0MsgBox "定时任务已取消。", vbInformationEnd Sub
🔍 逐段拆解(核心 5 处)
1)CreateObject("Outlook.Application") —— Late Binding
Set olApp = CreateObject("Outlook.Application")Set olMail = olApp.CreateItem(0) ' 0 = olMailItem
为什么不引用 Outlook 库(Early Binding)?
Early Binding 需要在 VBE 里"工具→引用"勾选 Outlook 对象库 换台电脑、换 Outlook 版本,引用可能失效,代码直接报错 Late Binding 用 CreateObject动态创建,换电脑不用改一行代码
CreateItem(0) 里的 0 就是邮件类型(olMailItem)。Late Binding 下没有常量名,用数字代替。
2)wsReport.Copy 另存干净副本
wsReport.Copy
ActiveWorkbook.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close SaveChanges:=FalseWorksheet.Copy 不带参数时,会把这张表复制到一个新的工作簿里。这样保存出来的文件:
只有"日报"一张表,干净 不带宏( .xlsx格式),收件人打开无安全提示不影响当前工作簿
3).Send vs .Display
写法 | 效果 | 适用场景 |
|---|---|---|
| 直接发出 | 定时任务、无人值守 |
| 弹出窗口手动确认 | 调试阶段、重要邮件需人工审核 |
建议:先用 .Display 调试几次,确认内容无误后再改成 .Send。
4)Application.OnTime 定时器
Application.OnTime EarliestTime:=nextRunTime, _Procedure:="定时任务入口", _Schedule:=True
四个要点:
EarliestTime:什么时候执行(类型是 Date)Procedure:执行哪个宏(名字必须用字符串,不能带括号) Schedule:=True:安排任务; Schedule:=False:取消任务nextRunTime必须是模块级变量(声明在所有过程外面),否则取消时找不到时间
5)循环续期——让任务每天自动跑
Sub 定时任务入口()
生成并发送日报
nextRunTime = nextRunTime + 1
Application.OnTime nextRunTime, "定时任务入口", True
End SubOnTime 是一次性的——执行完就没了。所以在入口宏末尾再安排下一次,就形成了"每天自动循环"。

⚠️ 定时邮件的 5 个真·坑
现象 | 根因 | 解决 |
|---|---|---|
到点没执行 | Excel 文件或工作簿被关了 | 必须保持 Excel 打开(可最小化),关机/休眠就失效 |
取消定时任务报错 |
| 变量必须声明在模块顶部;取消时加 |
Outlook 弹安全警告"程序正在访问邮件" | Outlook 安全策略、杀毒软件拦截 | 用 Late Binding 可缓解;企业环境可配置信任设置;或用 |
附件中文名乱码/找不到 | 路径含特殊字符 | 用 |
收件人收到空白邮件 |
| 用 |
🛠️ 让 AI 改版的常用追问
基础版跑通后,可以直接追:
"改成每周五发周报, Weekday(Date) = 6判断""改成每月1号发月报" "如果今日销售额为 0,不发邮件并弹窗提醒我检查数据" "正文用 HTML 格式,加表格和颜色"( .HTMLBody属性)"附件超过 3 个时自动压缩成 zip" "发送失败时重试 3 次,间隔 5 分钟" "发送成功后把记录写入日志 sheet(时间、收件人、状态)"
HTML 正文进阶(老板看了都说专业)
olMail.HTMLBody = "<html><body>" & _"<h2style='color:#003366;'>销售日报 - " & todayStr & "</h2>" & _"<tableborder='1'cellpadding='8'style='border-collapse:collapse;'>" & _"<trstyle='background:#003366;color:white;'><th>指标</th><th>数值</th></tr>" & _"<tr><td>总销售额</td><td><b>" & Format(totalSales, "#,##0.00") & "</b> 元</td></tr>" & _"<tr><td>订单数</td><td><b>" & orderCount & "</b> 单</td></tr>" & _"</table></body></html>"
🐍 Python 对比教学(顺手多学一手)

Python 版用 smtplib 发邮件,不依赖 Outlook:
import smtplib, schedule, timefrom email.mime.multipart import MIMEMultipartfrom email.mime.text import MIMETextfrom email.mime.base import MIMEBasefrom email import encodersfrom datetime import datetimedef send_report():msg = MIMEMultipart()msg["From"] = "your@email.com"msg["To"] = "boss@company.com"msg["Subject"] = f"销售日报 - {datetime.now():%Y年%m月%d日}"msg.attach(MIMEText("附件为今日销售日报,请查收。", "plain", "utf-8"))with open("日报存档/日报.xlsx", "rb") as f:part = MIMEBase("application", "octet-stream")part.set_payload(f.read())encoders.encode_base64(part)part.add_header("Content-Disposition", "attachment", filename="日报.xlsx")msg.attach(part)with smtplib.SMTP_SSL("smtp.qq.com", 465) as server:server.login("your@email.com", "授权码")server.send_message(msg)# 每天17:00执行schedule.every().day.at("17:00").do(send_report)while True:schedule.run_pending()time.sleep(60)
对比:
VBA 版:依赖 Outlook 登录状态,但零部署、企业内网友好 Python 版:不依赖 Outlook,可部署到服务器 7×24 跑,但要配 SMTP 授权码
📌 本讲小结
你今天拿到了 | 能力 |
|---|---|
自动发邮件宏 | Late Binding 创建 Outlook 邮件+附件 |
| 另存干净副本不带宏 |
| 到点自动执行 |
循环续期模式 | 每天自动跑不用人工重置 |
HTML 正文 | 让邮件像正式报告 |
排错清单 | Outlook 安全警告/变量失效一眼定位 |
核心认知:自动化的终极形态不是"我点一下就完成",是"我不在它也完成"。OnTime + Outlook 这对组合,就是 Excel 通往无人值守的第一道门。
📢 下节预告
第 10 讲|把 10 张图表的刷新变成一次点击
日报发出去了,但里面的图表还是上次的数据?下一讲教你用一个宏刷新所有数据透视表、所有外部数据连接,再 Calculate 一遍全表——点一次,全 workbook 的数据和图表全部更新到最新。
💬 互动时间
你现在每天/每周固定要发的报表是什么?描述你的场景(什么数据、发给谁、什么频率),我帮你把提示词精修成生产级版本。















































































