Results 1 to 7 of 7

Emailing Individual Worksheets as a PDF using Outlook 2016

Threaded View

  1. #1
    Registered User
    Join Date
    06-19-2017
    Location
    London, England
    MS-Off Ver
    MS Office 365 - 2016
    Posts
    3

    Emailing Individual Worksheets as a PDF using Outlook 2016

    Hi All,

    I've managed to create a macro whereby it saves individual worksheets as a separate PDF but I need some help with the next step.

    Once the worksheet has been saved as a PDF, I would like Outlook to open up, create a new email with the individual attachment.

    This is what I have so far:

    Sub ExportToPDFs()
    
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet
    Dim oApp As Object
    Dim oMail As Object
    
        For Each ws In Worksheets
        ws.Select
        nm = ws.Name
        
        If Not ws.Name = "Master" _
        And Not ws.Name = "Lookups" _
        And Not ws.Name = "TB" _
        And Not ws.Name = "Store Lookups" Then
    
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            FileName:="X:\IT\Test\test\" & nm & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
            
        Set oApp = CreateObject("Outlook.Application")
        Set oMail = oApp.CreateItem(0)
        With oMail
            .To = [I1]
            .Subject = "test"
            .body = "This is a test"
            .Attachments.Add 'This is where the macro fails and I understand why but not to sure how to select the nearly created worksheet'
            
        End With
        End If
        
    Next ws
        
    Application.ScreenUpdating = True
        
    End Sub

    Any suggestions would be most grateful.

    Thanks
    Karima
    Last edited by karima_e; 06-19-2017 at 06:33 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA emailing using Outlook
    By scrizo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-22-2015, 10:22 AM
  2. Emailing individual files (with oft template)
    By MooKwia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2013, 10:32 PM
  3. [SOLVED] Emailing individual recipients using Outlook from an Excel data source
    By Shirley Munro in forum Excel General
    Replies: 2
    Last Post: 04-23-2012, 01:29 PM
  4. Excel 2007 : Emailing Individual Sheets of Workbook
    By jeanie in forum Excel General
    Replies: 1
    Last Post: 07-06-2010, 03:51 PM
  5. Emailing Excel to Outlook
    By stevewilde in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-12-2009, 07:55 AM
  6. emailing with outlook
    By DSJ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2008, 10:20 AM
  7. Macro Emailing an XLS via Outlook
    By ynissel in forum Excel General
    Replies: 5
    Last Post: 05-26-2005, 05:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1