MACRO以PDF格式保存，并以电子邮件的形式打印和发送（展望） [英] MACRO TO SAVE AS PDF WITH NAMES AS PER CELL VALUES AND PRINT AND SEND BY EMAIL (OUTLOOK)

问题描述

MACRO通过自动提供来自CELL VAUES的名称（B1，C1，D1），将文件作为PDF文件保存在一个文件夹中。 CELL C1 CONTAINS  DATE  （任何选择日期不是格式DD MM YYYY）。

MACRO TO SAVE SHEET AS PDF FILE IN A GIVEN FOLDER BY AUTOMATICALLY GIVING NAME FROM CELL VAUES(B1,C1,D1). CELL C1 CONTAINS  DATE  (ANY CHOOSEN DATE NOT TODAY OF FORMAT DD MM YYYY).

自动打印和

通过电子邮件发送

推荐答案

嗨SUBINIGEORGE，

Hi SUBINIGEORGE,

您可以使用ExportAsFixedFormat将工作表保存为PDF。使用Sheets.PrintOut方法打印工作表。

You could use ExportAsFixedFormat to save the Worksheet as PDF. Use Sheets.PrintOut Method to print the worksheet.

我想知道你想如何通过电子邮件发送？是否要通过Outlook将单个工作表作为附件发送？

I'm wondering how do you want to send by email? Do you want to send the single worksheet as attachments via Outlook?

如果您需要将工作表保存到新工作簿，然后通过电子邮件发送新工作簿。

If you so you need save the worksheet to a new workbook and then send the new workbook via email.

以下是示例。

Sub Test()

'need add reference to Microsoft Outlook object library

Application.ScreenUpdating = False

'combine pdf name

Dim ws As Worksheet

Set ws = ActiveSheet

B1 = ws.Range("B1")

C1 = ws.Range("C1")

C1 = Format(C1, "YYYYMMDD")

D1 = ws.Range("D1")

Filename = B1 & C1 & D1

'save folder path

FOLDERPATH = ActiveWorkbook.Path

'save as pdf

ws.ExportAsFixedFormat xlTypePDF, FOLDERPATH & "\" & Filename

'print worksheet

ws.PrintOut

'save the single worksheet to a new workbook

Dim tempWb As Workbook

Dim tempWs As Worksheet

tempfile = FOLDERPATH & "\" & "Temp.xlsx"

If Dir(tempfile) <> "" Then

Kill tempfile

End If

Set tempWb = Application.Workbooks.Add

Set tempWs = tempWb.Worksheets(1)

ws.Cells.Copy tempWs.Cells

tempWs.Name = ws.Name

tempWb.SaveAs tempfile

tempWb.Close

'create outlook mail item

Dim olApp As Outlook.Application

Dim olMail As Outlook.MailItem

Set olApp = CreateObject("Outlook.Application")

Set olMail = olApp.CreateItem(olMailItem)

olMail.To = "[e-mail]"

olMail.Subject = "A test mail"

olMail.Attachments.Add (tempfile)

olMail.Display

Set olMail = Nothing

Set olApp = Nothing

Kill tempfile

Application.ScreenUpdating = True

End Sub

如果我误解了任何事情，请随时告诉我。

If I misunderstand anything, please feel free to let me know.

最好的问候，

Terry

这篇关于MACRO以PDF格式保存，并以电子邮件的形式打印和发送（展望）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！