VBA Error when Saving range to Pdf or trying to email range

Trowo

New Member
Joined
Dec 15, 2014
Messages
22
I keep getting hung up on the red line below and the same line in the macro below it. Please HELP! Many thanks in advance

Sub ToPDF()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim Path As String
Dim Fname As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Home")
Set rng = ws.Range("AU1:BC70")
Path = "C:\Users\Jsimps\OneDrive\Spreadsheets\Palmer Q2'23 Statements\"
Fname = ws.Range("AR9").Value & " " & Format(Now(), "MM-DD-YYYY hhmmss") & ".pdf"

rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & Fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

End Sub

Sub Email_Statement()
Dim saveLocation As String
Dim attachmentName As String
Dim rng As Range
Dim EmailApp As Object
Dim EmailItem As Object
saveLocation = "C:\Users\Jsimps\OneDrive\Spreadsheets\Palmer Q2'23 Statements\"
attachmentName = "Statement" & " " & "-" & " " & Range("AR9") & ".pdf"
Set rng = Range("AU1:BC70")
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.PrintArea = "AU1:BC70"
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
End With
rng.ExportAsFixedFormat Type:=xlTypePDF, IgnorePrintAreas:=False, Filename:=attachmentName
Set EmailApp = CreateObject("Outlook.application")
Set EmailItem = EmailApp.CreateItem(0)
With EmailItem
.To = "email"
.Subject = "Palmer Partners" & " " & "Q2'23 Statement"
.Body = "Please find attached your recent quarterly statement " & Range("AY10")
.Attachments.Add attachmentName
.Display
End With
Set EmailItem = Nothing
Set EmailApp = Nothing
End Sub
 
Last edited by a moderator:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
What error are you getting? Are you sure that the folder exists? Maybe this will help...

VBA Code:
Option Explicit

' ----------------------------------------------------------------
' Procedure Name: SaveRangeToPDF
' Purpose: Save the specified cells range to a pdf file.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter prRange (Range): Range to export to the PDF.
' Parameter psPath (String): Path/folder where the file is to be created.
' Parameter psFileName (String): File name of the pdf file.
' Parameter pbOpenAfter (Boolean): Flag specifying if the file is opened when created.
' Author: Jim
' Date: 6/15/2022
' ----------------------------------------------------------------

Function SaveRangeToPDF( _
    prRange As Range, _
    psPath As String, _
    psFileName As String, _
    Optional pbOpenAfter As Boolean = False) As Boolean
    
    If Right(psPath, 1) <> "\" Then psPath = psPath & "\"
    
    If UCase(Right(psFileName, 4)) <> ".PDF" Then psFileName = psFileName & ".pdf"
    
    On Error Resume Next
    Kill psPath & psFileName
    On Error GoTo 0
    
    SaveRangeToPDF = False
    
    If Dir(psPath, vbDirectory) <> "" _
     Then
        
        prRange.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=psPath & psFileName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=True, _
            OpenAfterPublish:=pbOpenAfter
            
            SaveRangeToPDF = True
    Else
    
        MsgBox "The specified folder does not exist." & Chr(10) & psPath, vbInformation
    
    End If

End Function
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top