vba to create pdf from excel worksheet then email|VBA & Macros|Excel Forum|My Online Training Hub

Hi there! 

I'm working with some sample code posted by Phillip a while back. For the most part it is working but being so new to this I'm having troubles when customizing it to fit our needs more. 

Right now the code send one email of one sheet to the specified recipients but I would like for the script to refresh the workbook then loop through all of the sheets to create the pdf's, attach the 5 pdf's to one email and then send to their final destination. If thats not possible or too much hassle 5 separate emails are fine as long as I can figure out the rest... I found the code I believe I need for this I just cant for the life of me figure out how to write it correctly. Any help would be greatly appreciated. Think im going to take a couple beginner courses online once this project gets wrapped up. 

Oh, I would also like to set up this to be ran with the task scheduler. I found a great tutorial I just literally dont know how to write the code with what I have below. 

Task Scheduler:

https://www.excell-en.com/exce.....g-tutorial

Sub Auto_Open()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

MsgBox "Excellen" 'Replace this with your macro code!


Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
_______________________________________________________________

Loop through all sheets:

Sub SendAllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
   ws.Activate
   create_and_email_pdf
Next
 End Sub

_______________________________________________________________

Refresh Workbook on open

'ActiveSheet.EnableCalculation = True

_______________________________________________________________

My Code

Sub create_and_email_pdf()
ActiveSheet.EnableCalculation = True

Dim EmailSubject As String, EmailSignature As String
Dim CurrentMonth As String, DestFolder As String, PDFFile As String
Dim Email_To As String, Email_CC As String, Email_BCC As String
Dim OpenPDFAfterCreating As Boolean, AlwaysOverwritePDF As Boolean, DisplayEmail As Boolean
Dim OverwritePDF As VbMsgBoxResult
Dim OutlookApp As Object, OutlookMail As Object
CurrentMonth = ""

EmailSubject = ActiveSheet.Range("A1")
OpenPDFAfterCreating = False
AlwaysOverwritePDF = True
DisplayEmail = False
Email_To = "Robert.rogozinski@clearsourcebpo.com"
Email_CC = "Robert.rogozinskislc@gmail.com"
Email_BCC = "zinski89@gmail.com"
DestFolder = "C:\DailyWfmPDF"

CurrentMonth = ActiveSheet.Range("A2")

PDFFile = DestFolder & Application.PathSeparator & ActiveSheet.Range("A1") & ".pdf"

If Len(Dir(PDFFile)) > 0 Then

If AlwaysOverwritePDF = False Then

OverwritePDF = MsgBox(PDFFile & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", vbYesNo + vbQuestion, "File Exists")

On Error Resume Next

If OverwritePDF = vbYes Then

Kill PDFFile

Else

MsgBox "OK then, if you don't overwrite the existing PDF, I can't continue." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"

Exit Sub

End If

Else

On Error Resume Next
Kill PDFFile

End If

If Err.Number <> 0 Then

MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"

Exit Sub

End If

End If

'Create the PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=OpenPDFAfterCreating

'Create an Outlook object and new mail message
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)

'Display email and specify To, Subject, etc
With OutlookMail

.Display
.To = Email_To
.CC = Email_CC
.BCC = Email_BCC
.Subject = EmailSubject & CurrentMonth
.Attachments.Add PDFFile

If DisplayEmail = False Then

.Send

End If

End With

End Sub