前言
最近遇到一个需求,当excel中的某个单元格达到要求后自动发送邮件通知,省去人工核对表格信息的工作,这也是一种自动化。
通过网上查找资料,可以通过结合Excel的VBA(Visual Basic for Applications)和Outlook来实现。实现起来没太大的难度,有些细节还是要注意一下的。
需求分析
姓名 | 剩余时间 | 邮件状态 |
A | 4 | 已发送 |
B | 10 | |
C | 15 | |
D | 2 | 已发送 |
当剩余时间低于5的时候,自动发送邮件通知,并且将邮件状态改为“已发送”。因为要用到宏,确保将 Excel 文件保存为启用宏的格式(.xlsm)。
编写VBA代码发送邮件
在Excel中,按 ALT + F11
打开VBA编辑器,点击“插入” > “模块”,插入一个新的模块,输入以下代码:
Sub SendEmailIfValueLessThan5()
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim cellValue As Double
Dim Name As String
Dim Status As String
Dim newStatus As String ' 新状态,用于标记邮件已发送
' 设置工作表
Set ws = ThisWorkbook.Sheets("Sheet1") ' 确保工作表名称正确
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' 创建Outlook应用
Set OutlookApp = CreateObject("Outlook.Application")
' 定义新状态(已发送)
newStatus = "已发送"
' 遍历每一行
For i = 2 To lastRow ' 假设第一行是表头
Name = ws.Cells(i, 1).Value ' 读取姓名
cellValue = ws.Cells(i, 2).Value ' 读取要检查的单元格值
Status = ws.Cells(i, 3).Value ' 读取状态
' 检查值是否小于5且状态不为“已发送”(或者您可以根据需求调整这个条件)
If cellValue < 5 And Status <> newStatus Then ' 避免重复发送邮件
' 创建邮件
Set OutlookMail = OutlookApp.CreateItem(0) ' 0代表邮件
With OutlookMail
' 这里可以动态设置邮件地址,例如从工作表的另一列读取
.To = "1979007616@qq.com" ' 或者使用变量 emailAddress
.Subject = "警告:健康证有效期小于5天"
.Body = "注意: " & Name & "的健康证有效期小于5天,请尽快通知其续期。"
.Send ' 发送邮件
End With
' 更新状态为已发送
ws.Cells(i, 3).Value = newStatus
' 释放邮件对象
Set OutlookMail = Nothing
End If
Next i
' 清理
Set OutlookApp = Nothing
End Sub
上面就是我们的主体代码,遍历关键列信息,判断是否满足条件,如果满足条件则调用Outlook邮箱发送邮件到指定邮箱,同时定义了邮件内容。
设置Windows任务计划程序
在Excel表格处于关闭状态时,VBA 宏无法直接执行,因为 VBA 代码需要 Excel 环境处于打开状态才能运行。所以我们要使用 Windows 任务计划程序,然后调用批处理文件打开Excel表格。
新建一个计划任务,注意要选择【只在用户登录时运行】,因为只有选择了这个才能在桌面打开excel界面,不然都是在后台运行,会导致脚本关闭excel后,下次打开提示保存历史副本。
设置执行时间
选择批处理文件的路径
执行条件
到达预设时间后,windows会执行.bat
文件。.bat
内容如下:
@echo off
start "" "\\10.10.166.5\02.IT002\自动化运行\测试1.xlsm"
:: 设置倒计时时间(秒)
set countdown=35
:countdown_loop
:: 显示倒计时
echo.
echo CMD窗口将在 %countdown% 秒后关闭。
echo.
:: 使用 timeout 命令等待1秒并减少倒计时时间
timeout /t 1 /nobreak >nul
set /a countdown=%countdown%-1
:: 检查倒计时是否结束
if %countdown% gtr 0 (
goto countdown_loop
) else (
echo.
echo 正在关闭CMD窗口...
:: 使用 exit 命令关闭CMD窗口
taskkill /f /im EXCEL.EXE
exit
)
打开文件测试1.xlsm
,并且进行倒计时35秒,提示关闭CMD窗口,关闭窗口前结束EXCEL.EXE
进程。
VBA代码自动保存EXCEL
打开了Excel文件也是无法自动执行宏代码,需要把刚刚创建的函数名引用一下:
这样就能在excel打开时执行SendEmailIfValueLessThan5函数。发现有符合条件的列就会发送邮件,并且把【邮件状态】列修改成“已发送”,避免每次打开都重复发送邮件,这也引入了另一个问题,修改之后怎么保存,因为全程是没有人工干预的。
解决方法就是在Excel关闭前进行一次自动保存,这样结束进程后,就不会影响下一次打开。
插入模块,添加VBA代码:
Dim nextSaveTime As Date
Sub StartAutoSave()
' 设置下一次保存的时间为当前时间加30秒
nextSaveTime = Now + TimeValue("00:00:30")
' 调用AutoSave过程,并设置其在nextSaveTime时再次运行
Application.OnTime nextSaveTime, "AutoSave"
End Sub
Sub AutoSave()
' 检查是否处于手动干预或错误处理中而需要停止自动保存
' (此部分可根据需要添加逻辑)
' 保存当前活动工作簿
ThisWorkbook.Save
' 重新设置下一次保存的时间为当前时间再加30秒
' 并调用AutoSave过程以继续自动保存
nextSaveTime = Now + TimeValue("00:00:30")
Application.OnTime nextSaveTime, "AutoSave"
End Sub
Sub StopAutoSave()
' 取消定时保存的调度
On Error Resume Next ' 忽略错误,以防OnTime已经被取消
Application.OnTime nextSaveTime, "AutoSave", , False
On Error GoTo 0 ' 恢复正常的错误处理
End Sub
然后在ThisWorkbook中调用函数StartAutoSave()
,每过30S自动保存一次文件。避免因为强制关闭Excel进程,而导致邮件状态没有保存,下次打开重复发送邮件的问题。批处理文件.bat
是设置的35S钟强制关闭Excel进程,此时我们已经保存过一次该文件了。
在我们强制结束EXCEL.EXE进程后,不管是否有修改记录,下次打开都会提示恢复,如下图:
这个不会对文件产生影响,可以忽略,因为我们已经保存过修改记录,直接关闭就行。
强制结束EXCEL.EXE进程会关闭所有excel表,所以尽量使用闲置的电脑运行计划任务,避免影响正常工作的office文件。
网络共享盘宏不执行
如果宏文件是保存在网络共享盘中,你会发现宏不执行,提示不安全,这时需要做一个设置,添加信任站点。
- 点击“开始”按钮或 Windows 键,然后键入“Internet 选项”。
- 从搜索结果中选择“Internet 选项”,此时会显示“Internet 属性”对话框。
- 在“安全性”选项卡上,选择“受信任的站点”,然后选择“站点”。
- 键入包含 Microsoft 365 文件的站点或服务器的 URL,其中包含要运行的宏,然后选择“添加”。
关闭,保存就可以运行了。
拓展
关闭Excel默认弹窗
在ThisWorkbook中写入以下代码,可以跳过excel保存的默认弹窗,关闭就会自动保存,不会弹窗。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' 这里是当工作簿关闭之前要执行的代码
' 默认情况下,Excel会在关闭工作簿时询问是否保存更改
' 但我们可以通过以下代码来强制保存,而不显示提示
ThisWorkbook.Save
End Sub