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
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: