Hi,
I need help with the following VBA, At the minute the VBA can print page break to pdf and name them according to the name found in cell A and has loop to pint all page breaks as pdf. I was wondering if there' was a way, it could also email the pdfs generated to individual email address. I have roughly 20 page breaks and all those need to be emailed to different email addresses according to the names.
Sub exportPages()
Set Sht = Worksheets("Sheet1")
' The variable Sht now holds the sheet that should be printed, so we can use that in the macro instead of repeating Worksheets("Sheet1")
ExportDir = "C:UsersDesktop"
' A string/text with the export directory, should end with a
NrPages = Sht.HPageBreaks.Count + 1
' Using the Sht object, count the number of horizontal page breaks and add one to know the number of pages
For p = 1 To NrPages
' Loop though the pages, actually the loop is only there as a kind of "counter"
If p = 1 Then
RwStart = 1
' The first page starts at the top and has no page break before it, so the row with your name is 1
Else
RwStart = Sht.HPageBreaks(p - 1).Location.Row
' Page 2 starts after Pagebreak 1, this code finds the row of the page break (=the row directly after it)
End If
FoundName = Sht.Range("A" & RwStart).Value
ExportName = FoundName & p & ".pdf"
' Create a variable with the name in the sheet
Sht.ExportAsFixedFormat Type:=xlTypePDF, filename:=ExportDir & ExportName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=p, to:=p, OpenAfterPublish:=False
' Export the current page with the current name to the default location
Next
Set Sht = Nothing
' Clean up variables
End Sub
Hi Arun,
the code for emailing PDF's here should help you
Regards
Phil
Hi Phil,
Thanks for your reply, I am new to VBA, is there a chance you could help me modify the above code? I can add the email address in one of the cells in each page break so VBA picks up the email address for that particular pdf?
Thanks in advance,
Kind Regards,
Arun
Hi Arun,
Please see attached workbook. This sends email using Outlook so you'll need that installed. Emails are sent 'silently' - they aren't displayed before being sent, but you can change this if you want. So you'll have to check your Sent Items to see what was sent.
Your code picks up the value in the top most cell of the page e.g. A1, so I am using the next cell to the right e.g. B1 for the email address. You can change this to whatever you like.
The line that controls where the email is picked up is
Email_To = Sht.Range("A" & RwStart).Offset(0, 1).Value
The Offset is what actually references the cell with the email address. It sets Email_To to whatever is in the cell 0 rows down and 1 column across from Sht.range("A"&RwStart)
Tip : You should always use Option Explicit when writing code https://www.myonlinetraininghub.com/more-tips-for-debugging-vba
Cheers
Phil
Hi Philip,
Brilliant, that works like a charm.
Thank you so much for your help, you are a STAR.
Kind Regards,
Arun
No worries 🙂
