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

Phil,

Thank you for the help - really appreciate it.  I apologize, but I am a beginner when it comes to macros.  Please find attached the issue I am running into when I attempt to run the code.  I have also taken a screenshot of the xls document so you can see the actual cell letters/numbers I am attempting to reference. 

Hi Rob,

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

regards

Phil

I'm also running into an issue here...

I have some confidential information.  I'll make some edits and send that workbook to you.

Here you go...

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

Ok, try this version.

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

Phil,

I'm not sure what DV lists are but I made some changes to this attached version.

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

Phil,

Really appreciate you assisting me with this.  I opened your sheet and when I attempted to run the macro, I received the run-time error '91' you see in the screenshot.  Any ideas?  FYI, I am using a Mac.  Not sure if that makes a difference.

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

Ok, looks like I got past that debug.  Now I'm on to this...