VBA调用Outlook发送Excel工作表为PDF时弹出保存提示如何解决

弹窗原因 #

你将工作表复制生成临时工作簿aWb后，仅执行了PDF导出操作，没有对该临时Excel工作簿做过保存，执行aWb.Close True时，True参数代表要保存工作簿更改，Excel就会弹出保存提示框。你实际仅需要导出的PDF文件，不需要保留该临时Excel工作簿，因此修改关闭参数即可解决问题。
另外原有代码存在一处变量笔误：你定义了clc变量存储原计算模式，后续恢复时误写为cld，会导致代码运行后Excel计算模式无法正常恢复，也同步做了修复。

修正后完整代码 #

Sub PDFMailVersand()

Dim OL As Object
Dim IsCreated As Boolean
Dim Wb As Workbook
Dim aWb As Workbook
Dim Ws As Worksheet
Dim An As String
Dim Cc As String
Dim From As String
Dim Subject As String
Dim Dpfad As String
Dim Body As String
Dim strDateiname As String
Dim clc

Set Wb = ThisWorkbook
With Application
    clc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    ' 额外禁用系统警告，进一步避免弹窗
    .DisplayAlerts = False
End With

On Error Resume Next
Set OL = GetObject(, "Outlook.Application")
If Err Then
    Set OL = CreateObject("Outlook.Application")
    IsCreated = True
End If
On Error GoTo 0

For Each Ws In Wb.Worksheets
    Ws.Copy
    Set aWb = ActiveWorkbook
    strDateiname = ThisWorkbook.Path & "\" & ActiveSheet.Name & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strDateiname, _
                     IncludeDocProperties:=False, _
                     IgnorePrintAreas:=False, _
                     OpenAfterPublish:=False
    An = aWb.Worksheets(1).Range("AB2").Value
    Cc = aWb.Worksheets(1).Range("AF2").Value
    Body = aWb.Worksheets(1).Range("AB5").Value
    From = aWb.Worksheets(1).Range("AB4").Value
    Subject = aWb.Worksheets(1).Range("AB3").Value
    ' 核心修改：关闭临时工作簿时不保存更改，跳过保存提示
    aWb.Close SaveChanges:=False
    With OL.CreateItem(0)
        .SentOnBehalfOfName = From
        .To = An
        .Cc = Cc
        .Body = Body
        .Subject = Subject
        .Attachments.Add strDateiname
        .Send
    End With
    Kill strDateiname
    Set aWb = Nothing
Next

If IsCreated Then OL.Quit
With Application
    ' 修复变量笔误，将原错误的cld改为正确的clc
    .Calculation = clc
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
Set OL = Nothing
Set Wb = Nothing
Set Ws = Nothing
End Sub

主要修改点 #

• 将aWb.Close True改为aWb.Close SaveChanges:=False，直接不保存关闭临时工作簿，从根源消除保存提示
• 修复变量笔误：将末尾恢复计算模式的.Calculation = cld修正为.Calculation = clc，确保代码运行后Excel计算模式正常恢复
• 新增Application.DisplayAlerts开关，额外屏蔽运行过程中可能出现的其他系统提示

内容的提问来源于stack exchange，提问作者Imzadi76