Generate PDF and Email Automatically - Easy To Use and Run!

This macro makes weekly reports a breeze, as it automatically stores and sends the report. You have it easy, while your recipients have it consistent and on time!

Before you begin, check out the Macro Scheduling Tutorial. This allows you to automatically schedule this macro. If you don't, it's still a simple click of the "run macro" button!

 

Steps

1. Create Report

2. Adjust Settings

3. Copy In VA

4. Done

 

 

1. Setup

This macro saves a range as a PDF to a specified location, then attaches it to an email and sends according to your settings. This is a two for one tutorial, as the auto generating/saving PDF is a great tool by itself!

Make sure you have Outlook running - chances are if you have Excel you have Outlook. 

 

2. Creating the PDF

1. Create Folders and Excel Workbook
On your desktop, create a folder called "PDF Sender and Saver". Inside that, create another folder called "PDF Records". 
Finally, create a macro enabled workbook called "Report". 

createfolderandworkbook.png

2. Inside the workbook, create two sheets "Report" and "Mail". 

3. Create your PDF report in the sheet "Report" 

This can be anything you want. Naturally if it prints well, it will look great in a PDF. 
I created a one pager KPI type weekly report. I fiddled with the print settings to get the correct margins and fit.

PDFReport.png

 

What's important here is the name of your PDF. Mine is called "KPI Report - Week 24". This is created using the following formula: 
="KPI Report - Week "&ISOWEEKNUM(TODAY())
If the name remains the same when you run the macro, it will overwrite the old report. This way when I run it weekly, the name changes and I don't get an overwrite. 

 

3. Settings

1. Configure Mail Settings

Now it's time to create your email settings and tell it the location of your folders. Head on over to the "Mail" sheet. 
I strongly recommend you download the Excel template using the form below, and just copy the "Mail" sheet over to your workbook. Then all you need to do is update your username in the links. 

However if not, 

C1 = Name Of Report - for example "KPI Report - Week 24"
C3 = Location Of Report - for example "C:\Users\username\Desktop\PDF Sender and Saver\PDF Records\KPI Report - Week 24.pdf"
C5 = Recipient Email Address - for example "email@gmail.com;email@outlook.com"
C6 = Any CC - leave blank if nothing
C7 = Subject - for example "KPI Report - Week 24"
C8 = Body Message
C9 = Location Of Report (should be the same as C3). 

My calculator (to make the above easy) looks like this:

emailsettings.png

It doesn't really matter where you put these, or what sheet you use. As long as everything is there, you will just have to manually change the VBA code to match. Which is no problem at all. 

4. VBA Time

1. Open up VBA and create a new Module (name of the module is irrelevant). 

vbamodulepdf.png

2. Copy In The Following VBA

Sub PDFSaver()

report = Sheets("Mail").Range("C3").Value 'Location and name of PDF you are saving

'Update Sheet name and range
Sheets("Report").Range("B2:N53").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
report, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

End Sub


Sub Emailer()


Dim mainWB As Workbook
Dim SendID
Dim CCID
Dim Subject
Dim Body

Set otlApp = CreateObject("Outlook.Application")
Set olMail = otlApp.CreateItem(olMailItem)
Set mainWB = ActiveWorkbook

SendID = mainWB.Sheets("Mail").Range("C5").Value 'Send To
CCID = mainWB.Sheets("Mail").Range("C6").Value 'CC
Subject = mainWB.Sheets("Mail").Range("C7").Value 'Subject
Body = mainWB.Sheets("Mail").Range("C8").Value 'Message
With olMail
    .To = SendID
    If CCID <> "" Then
      .CC = CCID
    End If
    .Subject = Subject
    .Attachments.Add Sheets("Mail").Range("C9").Value 'Attachment

  
    .Body = Body

    
    .HTMLBody = .HTMLBody _
    & "<br>Cheers, <br>Excellen</font></span>" 'Change to your sig
               
               
                
.Send
           
               
End With

End Sub

Sub CallReportInfo() 'Change to Auto_Open if you want to schedule



Call PDFSaver
Call Emailer


End Sub

There are three macros here, PDF creator, Auto Email, and a Call macro that combines both of the previous macros into one. 

3. Change the VBA code

If you nailed the placement in the following steps, the only thing you need to change is the line near the start:
Range("B2:N53").Select 'Range to PDF
Change it to the range of your created PDF. 

Also change the signature line down the bottom:
& "<br>Cheers, <br>Excellen</font></span>" 'Change to your sig

 

All of this is highlighted with comments - this is what you change if you didn't put your settings into the cells specified above:

VBAtoreview.png

 

4. Run Macro

You now run "CallReportInfo" and you will get a PDF generated and an email with it attached! Any issues will be down to mistakes in your specified locations or VBA looking in the wrong cell. Very easy to debug! Download the below template if you truly get stuck.

AutoPDFEmailer.png

 

6. That's it

It really is that easy. You now can just change the "CallReportInfo" macro to "Auto_Open()" and it will fire when the workbook is opened. Read the Macro Scheduling tutorial on how to then automatically schedule it at specified times. 

This is a great way around issues with formatting Excel content in emails, as discussed in the Email Range Excel Tutorial. You have a professional report in perfect formatting ready to send to anyone.  

 

7. Download

More templates