VBA到PDF然后将PDF附加到Outlook电子邮件 [英] VBA to PDF then Attach PDF to an outlook email

问题描述

 

我目前使用包含多个不同公司的交易数据的每日电子表格（在E栏中保存），我按每个公司名称手动对数据进行排序在一天结束时和PDF，然后将该PDF附加到电子邮件。 

I currently use a daily spreadsheet containing trade data for several different companies (held in column E) and I sort that data manually by each company name at the end of the day and PDF it,  then attach that PDF to an email. 

我试图找出一种方法使用VBA按公司名称对数据进行排序而不是必须逐个手动排序。  

I am trying to figure out a way to sort that data by company name using VBA instead of having to manually sort it out one by one.  

我们非常感谢任何帮助。 

Any help would be greatly appreciated. 

谢谢！ 

Thanks! 

推荐答案

你好，

你可以记录一个宏来获取代码排序公司名称并保存为PDF文件。要在Excel中记录宏，请访问

使用宏录制器自动执行任务 

You could record a macro to get the code sorting company name and saving to PDF file. To record macro in Excel, please visit Automate tasks with the Macro Recorder 

要自动化Outlook，请访问
从Visual Basic应用程序自动化Outlook 。

To automation Outlook, please visit Automating Outlook from a Visual Basic Application.

要添加附件，我们可以使用
Attachments.Add Method（Outlook）。

To add attachment, we could use Attachments.Add Method (Outlook).

例如

'sort with company name, sort order : A-Z
    Dim sortRng As Range
    Dim rowIndex As Integer
    Dim ws As Worksheet
    Set ws = ActiveSheet
    rowIndex = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    Set sortRng = ws.Range("E1:E" & rowIndex)
    
    ActiveWorkbook.Worksheets("CompanyTrade").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CompanyTrade").AutoFilter.Sort.SortFields.Add Key _
        :=sortRng, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("CompanyTrade").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    'Save as PDF file
    Dim filename As String
    filename = "C:\Users\User\Desktop\Test.pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        filename, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

    'For using Outlook,add reference Outlook Object Library
    'Add PDF to an outlook email
    Dim oApp As Outlook.Application
    Dim emailItem As Outlook.mailItem
    Set oApp = New Outlook.Application
    Set emailItem = oApp.CreateItem(olMailItem)
    emailItem.Attachments.Add filename
    emailItem.Display

问候，

Celeste

这篇关于VBA到PDF然后将PDF附加到Outlook电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！