March 1, 2019
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.
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:
Dim ws As Worksheet
For Each ws In Worksheets
Refresh Workbook on open
'ActiveSheet.EnableCalculation = True
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.email@example.com"
Email_CC = "Robert.firstname.lastname@example.org"
Email_BCC = "email@example.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
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"
On Error Resume Next
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"
'Create the PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
'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
.To = Email_To
.CC = Email_CC
.BCC = Email_BCC
.Subject = EmailSubject & CurrentMonth
If DisplayEmail = False Then
October 5, 2010
Please see the attached workbook. By moving the code that loops though each workbook, you can create a PDF for each and then add each of them to the same email.
Please note : The value for EmailSubject is set once at the top off the Sub and takes the value of ActiveSheet.Range("A1"). So you wouldn't want to set this every time you moved to another sheet.
Regarding the scheduling of this, you should use a workbook open event, not Auto_Open and then just set the Windows scheduler to open the workbook.