Excel单元格满足条件自动发送电子邮件
本文最后更新于45 天前,其中的信息可能已经过时,如有错误请发送邮件到1979007616@qq.com

前言

最近遇到一个需求,当excel中的某个单元格达到要求后自动发送邮件通知,省去人工核对表格信息的工作,这也是一种自动化。

通过网上查找资料,可以通过结合Excel的VBA(Visual Basic for Applications)和Outlook来实现。实现起来没太大的难度,有些细节还是要注意一下的。

需求分析

姓名剩余时间邮件状态
A4已发送
B10
C15
D2已发送
数据表

当剩余时间低于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文件。

网络共享盘宏不执行

参考:已阻止有潜在危险的宏 – Microsoft 支持

如果宏文件是保存在网络共享盘中,你会发现宏不执行,提示不安全,这时需要做一个设置,添加信任站点。

  1. 点击“开始”按钮或 Windows 键,然后键入“Internet 选项”。
  2. 从搜索结果中选择“Internet 选项”,此时会显示“Internet 属性”对话框。
  3. 在“安全性”选项卡上,选择“受信任的站点”,然后选择“站点”。
  4. 键入包含 Microsoft 365 文件的站点或服务器的 URL,其中包含要运行的宏,然后选择“添加”。

关闭,保存就可以运行了。

拓展

关闭Excel默认弹窗

在ThisWorkbook中写入以下代码,可以跳过excel保存的默认弹窗,关闭就会自动保存,不会弹窗。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 这里是当工作簿关闭之前要执行的代码
    ' 默认情况下,Excel会在关闭工作簿时询问是否保存更改
    ' 但我们可以通过以下代码来强制保存,而不显示提示
    ThisWorkbook.Save
    
End Sub

测试附件

excel自动发送email | AList

小提示:您可以通过RSS订阅本站文章更新,订阅地址:https://blog.xmhweb.cn/feed
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇