Automatically email multiple PDF documents from one sheet that has a dropdown list|VBA & Macros|Excel Forum|My Online Training Hub

Hi Rob,

Attached is a workbook with code that does the job.  However, you'll need to incorporate it into your own workbook.  As it is it's not using your data validation and no data (of course) for it to email.

This code is based on my blog post here

Save Worksheet as PDF Then Email With Outlook

Please look through the code and familiarize yourself with it.  There are a number of straightforward customizations you can make like specifying a subject, BCC etc.  

You will also need to determine what the name of each created PDF is.  This can be dynamically generated using things like the sheet name or by picking up some value from the sheet.

When you run this code it will create the emails, but not send them.  This is just for you to make sure everything is working as you want.

When you are happy that things are working correctly, change the value of DisplayEmail from True to False.  You'll find this at the top of the VBA code under the line ' ***** You Can Change These Variables *********

When DisplayEmail is False, the VBA will create and send the emails.

Similarly there's a variable called AlwaysOverwritePDF.  This is currently set to False.  It controls whether Excel automatically overwrites a PDF with the same name after it's created them from your worksheet.  If you are happy to just overwrite the PDF's created on a previous run, before you email them, set AlwaysOverwritePDF to True.

I've added a shape on the sheet which you can click to run the code.  You can remove this if you like but you may want to figure out another way to run the VBA

Run VBA from a Shape, Object, or Shortcut

Let me know if you get stuck.

Regards

Phil

Hi Rob,

No worries, can you supply your workbook please, it's difficult to debug without the actual code.

regards

Phil

Hi Rob,

That's an XLSX file so there's no VBA in it.  You'll need to save the file as a .XLSM.

Phil

Hi Rob,

You don't have any DV lists set up.  In the code I sent you, and in the image posted above, the cell using the DV List is B6.

In the code you sent me last it's set to O6. 

Set DVCell = ActiveSheet.Range("O6")

Neither O6 not B6 are using DV so when the code tries this line

Set InputRange = Evaluate(DVCell.Validation.Formula1)

it throws an error.

Try setting up your DV list in O6 and see how it goes.

Regards

Phil

Hi Rob,

DV = Data Validation = Drop Down.  These are the lists you wanted to use to load different investors details into the sheet before creating the PDF.

Data Validation Lists in Excel

I see you have created a DV list for B6 on the Partner Statement sheet, but that list, which is loading data from the Investors sheet, is mostly blanks and this is causing errors.  You need to have your DV lists populated, so I've typed in some dummy data.

Likewise the VLOOKUP in R6 which returns the email to send to, was throwing an error because the lookup value from B6 was a blank.

The attached workbook contains dummy data that works for me.  You just need to fill it with the real data.

Also, this line had been changed

Set InputRange = Evaluate(DVCell.Validation.Formula1)

but it needs to stay as it is as it's the range that contains the DV value i.e. B6.

Cheers

Phil

No worries Rob.

I don't have a Mac so that makes it interesting.  It does appear that Mac's do not support Application.FileDialog which is being used to ask what folder to store the created PDF's in.

There's a workaround using Apple Script but I'm not familiar with that so what you can try is to hardcode the folder into the script.  The folder that PDF's are created in is stored in the variable DestFolder.

Look for this in the VBA, it's towards the top with all the other variables you can manually specify values for, and set it to whatever folder you want.

In this attachment I've commented out the code that asks for the destination folder.

Cheers

Phil

Ahh, you're using a Mac 🙁  Can't use this method to send email on a Mac I'm afraid.  Mac's do not support this.

I do not have a Mac nor have I ever done any development on one.

I can only point you to Ron de Bruin's code

https://www.rondebruin.nl/mac/.....ok2016.htm

and see if you can incorporate that.  Unfortunately as I do not possess a Mac I can't test this code for you either.

Sorry.

Phil

Hi Rob,

As I don't have a Mac there's no way for me to integrate my code with Ron's and test that it works.

You can try contacting Ron and seeing if he can help out.

regards

Phil