如何使用Excel VBA将导出的pdf文件附加到Outlook邮件? [英] How to attach exported pdf file to Outlook mail using Excel VBA?

问题描述

我的代码未找到要附加到电子邮件的导出文件.

My code is not finding an exported file to attach to an email.

我确定我在myattachments.add行中做错了事.

I'm sure I'm doing something wrong in the myattachments.add line.

根据每个客户端，文件名和文件目录将始终是不同的，这就是为什么我为每个引号中的文件名指定了一个单元格的原因.

The file name and file directory will always be different based on each client, that's why I have specified a cell specifically for the filename in each quote.

我要将此Excel文件复制到每个客户端文件夹，然后从那里运行代码.

I'm going to copy this Excel file to each client folder and run the code from there.

Sub sendremindermail()
ChDir ThisWorkbook.Path & "\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("'Costing'!C1"), Openafterpublish:=True

Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myattachments As Object

Set outlookapp = CreateObject("outlook.application")
Set outlookmailitem = outlookapp.createitem(0)
Set myattachments = outlookmailitem.Attachments

With outlookmailitem
.To = Range("'Costing'!C8")
myattachments.Add ThisWorkbook.Path & Range("'Costing'!C1") & ".pdf" ' it cant find the pdf in the same directory
'.send
.Display
End With

Set outlookmailitem = Nothing
Set outlookapp = Nothing

End Sub

我是VBA for Excel的新手.

I'm new to VBA for Excel.

推荐答案

我建议Outlook VBA不知道Excel VBA的范围.

I suggest Outlook VBA does not know Excel VBA's Range.

您可以像这样通过Range传递信息:

You could pass the info from Range like this:

Sub sendremindermail()

Dim fName As String
Dim pathFileName As String

ChDir ThisWorkbook.Path & "\"

fName = Range("'Costing'!C8")

'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("'Costing'!C1"), Openafterpublish:=True
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName ', Openafterpublish:=True

Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myattachments As Object

Set outlookapp = CreateObject("outlook.application")
Set outlookmailitem = outlookapp.createitem(0)
Set myattachments = outlookmailitem.Attachments

With outlookmailitem
    .To = fName
    pathFileName = ThisWorkbook.Path & "\" & fName & ".pdf"
    Debug.Print "With fName not Range: " & pathFileName
    myattachments.Add pathFileName
    '.send
    .Display
End With

Set outlookmailitem = Nothing
Set outlookapp = Nothing

End Sub

这篇关于如何使用Excel VBA将导出的pdf文件附加到Outlook邮件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！