Access中使用CDO发送邮件，如何无需指定路径附加报表PDF？

我们原本在Access里用嵌入式宏EMailDatabaseObject实现内部用户提交表单后发送带报表PDF附件的邮件，但因个人配置问题导致邮件接收异常，现在切换为基于CDO的VBA事件流程。目前邮件能正常发送，但报表PDF附件无法正确附加：尝试用DoCmd.OutputTo生成PDF到SharePoint路径后，使用.AddAttachment只能得到指向SharePoint根的HTML附件。想问能不能通过DoCmd运行并选中报表，让CDO直接附加这个选中的报表？

Private Sub Command3_Click()
Dim Mail As CDO.message
Dim Config As CDO.Configuration
Set Mail = CreateObject("CDO.Message")
Set Config = CreateObject("CDO.Configuration")

Config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
Config.Fields(cdoSMTPServer).Value = "smtp.MYORG.org"
Config.Fields(cdoSMTPServerPort).Value = 25
Config.Fields.Update
Const ForReading = 1

DoCmd.OutputTo acOutputReport, "rptMYREPORT", acFormatPDF, "REPORTNAME" & ".pdf"
DoCmd.SelectObject acReport, "rptMYREPORT", True

Set Mail.Configuration = Config

With Mail
.Subject = "Ready to Review"
.To = "ME@MYORG.org"
.From = Format(DLookup("Email", "tblSubmit"))
.CC = Format(DLookup("Email", "tblSubmit"))
.TextBody = "Entries are complete." & vbNewLine & vbNewLine & Format(DLookup("SubmissionNotes", "tblSubmit"))
Call .AddAttachment("https://MYORG.sharepoint.com/:f/r/personal/FIRSTNAME_LASTNAME_MYORG_org/Documents/Documents/REPORTNAME.pdf")
.Send
End With

Set Config = Nothing
Set Mail = Nothing

MsgBox ("Your entries have been submitted.")
End Sub

CDO无法直接附加Access中“选中”的报表对象，必须使用本地文件路径或可被CDO识别的有效文件URL。你遇到的问题核心是：SharePoint的Web URL不属于CDOAddAttachment支持的直接文件路径格式（CDO仅兼容本地磁盘路径或UNC网络路径，而非网页端的SharePoint URL）。

可以按以下思路修改代码：

• 先将PDF导出到本地临时路径，绕过SharePoint URL的格式限制
• 使用本地临时文件路径添加附件，发送完成后可选择删除临时文件
• 若需同步到SharePoint，发送邮件后再将本地PDF上传至指定位置

修改后的示例代码：

Private Sub Command3_Click()
Dim Mail As CDO.Message
Dim Config As CDO.Configuration
Dim tempPDFPath As String

' 配置本地临时PDF存储路径（用户文档文件夹）
tempPDFPath = Environ("USERPROFILE") & "\Documents\Temp_REPORTNAME.pdf"

Set Mail = CreateObject("CDO.Message")
Set Config = CreateObject("CDO.Configuration")

' CDO邮件服务器配置
Config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
Config.Fields(cdoSMTPServer).Value = "smtp.MYORG.org"
Config.Fields(cdoSMTPServerPort).Value = 25
Config.Fields.Update

' 将报表导出到本地临时文件
DoCmd.OutputTo acOutputReport, "rptMYREPORT", acFormatPDF, tempPDFPath, False

Set Mail.Configuration = Config

With Mail
    .Subject = "Ready to Review"
    .To = "ME@MYORG.org"
    .From = DLookup("Email", "tblSubmit") ' 无需Format函数，直接读取字段值
    .CC = DLookup("Email", "tblSubmit")
    .TextBody = "Entries are complete." & vbNewLine & vbNewLine & DLookup("SubmissionNotes", "tblSubmit")
    .AddAttachment tempPDFPath ' 使用本地路径添加附件
    .Send
End With

' 清理临时文件（可选）
If Dir(tempPDFPath) <> "" Then
    Kill tempPDFPath
End If

Set Config = Nothing
Set Mail = Nothing

MsgBox "Your entries have been submitted."
End Sub

关键注意点 #

• AddAttachment不支持SharePoint网页URL，必须用本地文件或\\服务器地址\共享文件夹\文件.pdf这类UNC路径
• 导出到本地临时文件可避免权限和格式兼容问题，发送后删除临时文件能保持本地整洁
• 移除了不必要的Format函数，DLookup直接返回字段值即可，减少额外转换可能引发的错误

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