如何使用Excel VBA将导出的pdf文件附加到Outlook邮件

Question

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

我确信我在附件中做错了什么。

每个客户端的文件名和文件目录总是不同的，这就是为什么我为每个引号中的文件名指定了一个单元格。

我将把这个Excel文件复制到每个客户端文件夹，并从那里运行代码。

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的新手。

Answer 1

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

你可以这样传递来自Range的信息：

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

Answer 2

您可以参考以下代码：

Sub Mail_Workbook_1()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = "[e-mail]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hello World!"
.Attachments.Add ActiveWorkbook.FullName
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:test.txt")
' In place of the following statement, you can use ".Display" to
' display the mail.
.Send   
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

有关更多信息，请参阅此链接：

OfficeTalk:使用Excel对象模型通过Outlook的电子邮件发送工作簿和范围(第1部分，共2部分(