将Excel工作表直接发送为PDF格式
提问于 2015-10-08 06:58:07
我的目标是能够点击一个按钮和我的Excel表格PDF的范围，我的电子表格和电子邮件到一个电子邮件地址，是在表格中的一个单元格。首先，我有可以将一系列单元格转换为PDF文件并允许我保存它的代码：
Option Explicit
Sub savePDF()
Dim wSheet As Worksheet
Dim vFile As Variant
Dim sFile As String
Set wSheet = ActiveSheet
sFile = Replace(Replace(Range("D11"), " ", ""), ".", "_") _
& "_" _
& Range("H11") _
& ".pdf"
sFile = ThisWorkbook.Path & "\" & sFile
With Excel.Application.FileDialog(msoFileDialogSaveAs)
Dim i As Integer
For i = 1 To .Filters.Count
If InStr(.Filters(i).Extensions, "pdf") <> 0 Then Exit For
Next i
.FilterIndex = i
.InitialFileName = sFile
.Show
If .SelectedItems.Count > 0 Then vFile = .SelectedItems.Item(.SelectedItems.Count)
End With
If vFile <> "False" Then
wSheet.Range("A1:BF47").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=vFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If
End Sub有人能操纵这个代码(附在一个按钮上)，这样它就会发送电子邮件地址，这个地址位于特定的单元格中，当按钮被点击时，作为额外的奖励，电子邮件的主题也会从电子表格中的一个单元格中得到？
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-10-08 08:21:15
我有一个解决办法如下。在通过进入页面支付并设置打印区域来设置打印区域后，我成功地将excel工作表发送为PDF文件：
Sub savePDFandEmail()
Dim strPath As String, strFName As String
Dim OutApp As Object, OutMail As Object
strPath = Environ$("temp") & "\" trailing "\"
strFName = ActiveWorkbook.Name
strFName = Left(strFName, InStrRev(strFName, ".") - 1) & "_" & ActiveSheet.Name & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strPath & strFName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = Range("CB4")
.CC = Range("CB6")
.BCC = ""
.Subject = Range("CB8")
.Body = Range("BW11") & vbCr
.Attachments.Add strPath & strFName
'.Display 'Uncomment Display and comment .send to bring up an email window before sending
.Send 'Keep this the same if you want to send the email address out on click of the button
End With
Kill strPath & strFName
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub我还需要在我的工作表中添加一个小的电子邮件工具，如下所示：
单击该按钮将发送带有PDF文件的电子邮件。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33019372
复制相关文章
点击加载更多
腾讯云开发者
