none
VBA to PDF then Attach PDF to an outlook email RRS feed

  • Question

  • Hi,  

    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. 

    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! 

    Thursday, May 4, 2017 4:33 PM

All replies

  • Hello,

    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 

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

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

    E.g.

    '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
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 5, 2017 3:19 AM
    Moderator