夜雨聆风学习资料网

ARTICLE · 1359760

用 AI 写 Excel VBA ⑨:定时自动生成日报并邮件发出

用 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 自动执行

邮件收件人

从单元格读取(方便随时改)

邮件主题

销售日报 - 2026年11月15日

邮件正文

含关键数据摘要(自动填入)

附件

当日日报文件(另存为带日期的副本)

发送方式

直接发送(不用手动点)


🤖 这一讲用的提示词(直接抄)

【角色】你是资深 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 Worksheet    Dim olApp As Object, olMail As Object    Dim saveFolder As String, savePath As String, fileName As String    Dim recipient As String, ccRecipient As String    Dim totalSales As Double, orderCount As Long    Dim mailBody As String    Dim todayStr As String    On Error GoTo ErrorHandler    Application.ScreenUpdating = False    Set 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 = "" Then        MsgBox "「配置」表 A1 未填写收件人邮箱!", vbExclamation        GoTo CleanUp    End 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 saveFolder    fileName = "日报_" & Format(Date, "yyyy-mm-dd") & ".xlsx"    savePath = saveFolder & fileName    '复制日报表到新工作簿再保存(避免带上其他表和宏)    wsReport.Copy    ActiveWorkbook.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook    ActiveWorkbook.Close SaveChanges:=False    '---- 构建邮件正文 ----    mailBody = "各位好," & vbCrLf & vbCrLf    mailBody = mailBody & "附件为 " & todayStr & " 销售日报,核心数据如下:" & vbCrLf & vbCrLf    mailBody = mailBody & " 今日总销售额:" & Format(totalSales, "#,##0.00") & " 元" & vbCrLf    mailBody = mailBody & " 今日订单数:" & orderCount & " 单" & vbCrLf & vbCrLf    mailBody = mailBody & "详细数据见附件。" & vbCrLf & vbCrLf    mailBody = mailBody & "(本邮件由 Excel 自动发送)"    '---- 创建 Outlook 邮件(Late Binding)----    Set olApp = CreateObject("Outlook.Application")    Set olMail = olApp.CreateItem(0)  ' 0 = olMailItem    With olMail        .To = recipient        If ccRecipient <> "" Then .CC = ccRecipient        .Subject = "销售日报 - " & todayStr        .Body = mailBody        .Attachments.Add savePath        .Send   ' 直接发送(用 .Display 则弹出窗口手动确认)    End With    '---- 记录发送时间 ----    wsConfig.Range("B1").Value = "最后发送:" & Now    Application.ScreenUpdating = True    '如果在定时任务中静默运行,不弹窗    If Application.RunMode = True Then        ' 静默模式不弹窗    Else        MsgBox "日报已发送至:" & recipient, vbInformation    End If    Exit SubErrorHandler:    Application.ScreenUpdating = True    MsgBox "发送失败:" & 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 + 1    Application.OnTime EarliestTime:=nextRunTime, _                       Procedure:="定时任务入口", _                       Schedule:=True    MsgBox "定时任务已设置!" & vbCrLf & _           "下次执行时间:" & Format(nextRunTime, "yyyy-mm-dd hh:nn:ss"), vbInformationEnd SubSub 定时任务入口()    '先执行发送    生成并发送日报    '再安排下一次(循环)    nextRunTime = nextRunTime + 1    Application.OnTime EarliestTime:=nextRunTime, _                       Procedure:="定时任务入口", _                       Schedule:=TrueEnd SubSub 取消定时任务()    On Error Resume Next    Application.OnTime EarliestTime:=nextRunTime, _                       Procedure:="定时任务入口", _                       Schedule:=False    On Error GoTo 0    MsgBox "定时任务已取消。", 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:=False

Worksheet.Copy 不带参数时,会把这张表复制到一个新的工作簿里。这样保存出来的文件:

  • 只有"日报"一张表,干净
  • 不带宏(.xlsx 格式),收件人打开无安全提示
  • 不影响当前工作簿

3).Send vs .Display

写法

效果

适用场景

.Send

直接发出

定时任务、无人值守

.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 Sub

OnTime 是一次性的——执行完就没了。所以在入口宏末尾再安排下一次,就形成了"每天自动循环"。

⚠️ 定时邮件的 5 个真·坑

现象

根因

解决

到点没执行

Excel 文件或工作簿被关了

必须保持 Excel 打开(可最小化),关机/休眠就失效

取消定时任务报错

nextRunTime 变量被重置过

变量必须声明在模块顶部;取消时加 On Error Resume Next

Outlook 弹安全警告"程序正在访问邮件"

Outlook 安全策略、杀毒软件拦截

用 Late Binding 可缓解;企业环境可配置信任设置;或用 .Display 手动点发送

附件中文名乱码/找不到

路径含特殊字符

用 ThisWorkbook.Path 拼接,避免手动输入路径

收件人收到空白邮件

B2/B3 单元格是文本或空

用 Val() 转换,发送前检查数据有效性


🛠️ 让 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 邮件+附件

Worksheet.Copy 技巧

另存干净副本不带宏

OnTime 定时器

到点自动执行

循环续期模式

每天自动跑不用人工重置

HTML 正文

让邮件像正式报告

排错清单

Outlook 安全警告/变量失效一眼定位

核心认知:自动化的终极形态不是"我点一下就完成",是"我不在它也完成"。OnTime + Outlook 这对组合,就是 Excel 通往无人值守的第一道门。


📢 下节预告

第 10 讲|把 10 张图表的刷新变成一次点击

日报发出去了,但里面的图表还是上次的数据?下一讲教你用一个宏刷新所有数据透视表、所有外部数据连接,再 Calculate 一遍全表——点一次,全 workbook 的数据和图表全部更新到最新。


💬 互动时间

你现在每天/每周固定要发的报表是什么?描述你的场景(什么数据、发给谁、什么频率),我帮你把提示词精修成生产级版本。

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料