Results 1 to 10 of 10

Save worksheets as PDF then email

Threaded View

zplugger Save worksheets as PDF then... 01-04-2012, 07:38 PM
john55 Re: Save worksheets as PDF... 01-04-2012, 08:32 PM
zplugger Re: Save worksheets as PDF... 01-04-2012, 08:41 PM
john55 Re: Save worksheets as PDF... 01-04-2012, 08:45 PM
zplugger Re: Save worksheets as PDF... 01-04-2012, 08:46 PM
john55 Re: Save worksheets as PDF... 01-04-2012, 08:47 PM
zplugger Re: Save worksheets as PDF... 01-04-2012, 08:56 PM
john55 Re: Save worksheets as PDF... 01-04-2012, 09:05 PM
zplugger Re: Save worksheets as PDF... 01-04-2012, 09:35 PM
john55 Re: Save worksheets as PDF... 01-04-2012, 09:44 PM
  1. #1
    Forum Contributor
    Join Date
    03-05-2009
    Location
    usa
    MS-Off Ver
    Excel 2016 32Bit
    Posts
    1,173

    Save worksheets as PDF then email

    trying to use this code to email worksheets in pdf but getting error?, not sure how to make this do 2 sheets?

    Sub Button2_Click()
    Dim sh As Worksheet
        Dim wb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim OutApp As Object
        Dim OutMail As Object
     
        TempFilePath = Environ$("temp") & "\"
     
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
     
     Set OutApp = CreateObject("Outlook.Application")   This is the ERROR  
    OutApp.Session.Logon     
    
    
        For Each sh In ThisWorkbook.Worksheets
            If sh.Range("a1").Value Like "?*@?*.?*" Then
     
                TempFileName = TempFilePath & sh.Name & ".pdf"
     
                'We Publish to PDF
                On Error Resume Next
                sh.ExportAsFixedFormat _
                        Type:=xlTypePDF, _
                        Filename:=TempFileName, _
                        Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, _
                        IgnorePrintAreas:=False, _
                        OpenAfterPublish:=False
                On Error GoTo 0
     
                Set OutMail = OutApp.CreateItem(0)
                On Error Resume Next
                With OutMail
                    .To = sh.Range("a1").Value
                    .CC = ""
                    .BCC = ""
                    .Subject = "hi there"
                    .Body = "hi there."
                    .Attachments.Add TempFileName
                    '.Send   'or use .Display
                    
                    .display
                    On Error GoTo 0
                End With
                Set OutMail = Nothing
     
                If Dir(TempFileName) <> "" Then Kill TempFileName
            End If
        Next sh
     
        Set OutApp = Nothing
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    Last edited by zplugger; 01-04-2012 at 08:26 PM.

Thread Information

Users Browsing this Thread

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

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