Forum

VBA help to email p...
 
Notifications
Clear all

VBA help to email printed pagebreak to an email address

6 Posts
2 Users
0 Reactions
89 Views
(@ak102)
Posts: 3
Active Member
Topic starter
 

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

 
Posted : 25/12/2018 6:50 pm
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Hi Arun,

the code for emailing PDF's here should help you

https://www.myonlinetraininghub.com/error-creating-relationship-in-vba-userform-code-to-create-pdf-from-excel-worksheet-then-email-it-with-outlook

Regards

Phil

 
Posted : 25/12/2018 7:35 pm
(@ak102)
Posts: 3
Active Member
Topic starter
 

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 

 
Posted : 25/12/2018 9:05 pm
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

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

 
Posted : 27/12/2018 11:07 pm
(@ak102)
Posts: 3
Active Member
Topic starter
 

Hi Philip, 

Brilliant, that works like a charm.

Thank you so much for your help, you are a STAR.

Kind Regards,

Arun

 
Posted : 28/12/2018 11:56 pm
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

No worries 🙂

 
Posted : 29/12/2018 12:18 am
Share: