采购订单（P.O.）系统PDF邮件附件功能故障求助

我正在搭建一套采购订单（P.O.）系统用于追踪采购流程：用户在「Open P.O. Form」标签页通过下拉选项填写信息，完成后点击创建按钮生成唯一PO编号，数据将保存至数据库标签页，同时生成PDF文件存储到网络驱动器，PDF命名规则为「Cont Vrac P.O. 420-10000x」（x逐次递增，首单为420-100001）。目前无法将PDF附加到邮件，推测系统无法识别需获取的文件，相关VBA代码如下：

Sub Create_Save_And_Send_PDF()

Dim xOutlookObj As Object
Set xOutlookObj = CreateObject("Outlook.Application")

Dim xEmailObj As Object
Set xEmailObj = xOutlookObj.CreateItem(0)

'This makes "Open PO Form" as the active worksheet
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Open P.O. Form")

'This designates the range on Open PO Form that will be included in the PO PDF
Dim PDF_Range As Range
Set PDF_Range = WS.Range("A1:F44")

'This is designating the PO Number to be used in the saved file name and Email Subject
Dim PO_Number As Range
Set PO_Number = WS.Range("B7")

'This identifies the name of the PDF File
Dim PO_File As String
PO_File = "Cont Vrac P.O. " & PO_Number

'This Identifies the email adress in the Open P.O. Form
Dim Email As Range
Set Email = WS.Range("B11")

'This is the Scripy in the body of the Email
Dim BodyA As String
BodyA = "Hello" & vbNewLine & vbNewLine & "You Have Successfully Create a P.O." & vbNewLine & vbNewLine & "A copy has been included as an attachment in this Email" & vbNewLine & vbNewLine & "Thank You"

'This Designates where the file will be saved "N:\CVR\La Prairie Shared\commun\Accounting\MONTH END\Mark Test P.O\" + "Cont Vrac P.O. " is the file path on the drive and  + "Cont Vrac P.O. " & PO_Number.Value
'is the name of the file. The saved file will be called "Cont Vrac P.O. & the PO Number

Dim PDF_Path As String
PDF_Path = "N:\CVR\La Prairie Shared\commun\Accounting\MONTH END\Mark Test P.O\" + "Cont Vrac P.O. " & PO_Number.Value + ".pdf"

'Dim PDF_Attach As Object
'Set PDF_Attach = "N:\CVR\La Prairie Shared\commun\Accounting\MONTH END\Mark Test P.O\" + "Cont Vrac P.O. " & PO_Number.Value

'align Range in PDF
WS.PageSetup.CenterHorizontally = True

'This is optional can be activated by switching False to true. it will open the PDF once the command is sent
PDF_Range.ExportAsFixedFormat Type:=xlTypePDF, _
  Filename:=PDF_Path, openAfterPublish:=False

'create email
With xEmailObj
    .display
    .to = Email.Value
    .cc = ""
    .bcc = ""
    .Subject = "Cont Vrac P.O. " & PO_Number.Value
    .Body = BodyA

    .Attachements.Add 'THIS IS WHERE I CANNOT CONTINUE. I DO NOT KNOW WHAT TO INCLUDE HERE

    xEmailObj.send

End With
End Sub

问题修复方案 #

1. 核心问题：附件方法参数缺失 #

.Attachments.Add必须传入完整的PDF文件路径，你已经定义了PDF_Path变量，直接传入即可。另外注意代码里的拼写错误：.Attachements多写了一个e，正确写法是.Attachments。

2. 路径拼接语法修正 #

VBA中字符串拼接要用&而非+，+仅在双方都是字符串时有效，若PO_Number.Value是数值类型会报错，修正路径代码：

Dim PDF_Path As String
PDF_Path = "N:\CVR\La Prairie Shared\commun\Accounting\MONTH END\Mark Test P.O\" & "Cont Vrac P.O. " & PO_Number.Value & ".pdf"

3. 修复后的邮件创建代码 #

替换原邮件部分的代码：

'create email
With xEmailObj
    .To = Email.Value
    .CC = ""
    .BCC = ""
    .Subject = "Cont Vrac P.O. " & PO_Number.Value
    .Body = BodyA
    ' 添加PDF附件
    .Attachments.Add PDF_Path
    .Send
End With

额外优化建议 #

• 增加文件存在检查，防止PDF导出失败导致附件添加出错：

' 导出PDF后检查文件是否存在
If Dir(PDF_Path) = "" Then
    MsgBox "PDF文件生成失败，请检查路径权限或导出范围", vbExclamation
    Exit Sub
End If

• 若频繁触发Outlook安全提示，可通过工具→引用→勾选Microsoft Outlook xx.x Object Library来启用早期绑定，替代CreateObject的晚期绑定。

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