• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / Automatically email multiple PDF documents from one sheet that has a dropdown list|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosAutomatically email multiple PDF do…
sp_PrintTopic sp_TopicIcon
Automatically email multiple PDF documents from one sheet that has a dropdown list
Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
1
April 18, 2020 - 2:07 am
sp_Permalink sp_Print

I have a statement that changes fields based on changing the cell in the dropdown list on B6.  I found VBA code that automatically saves and prints every dropdown option in cell B6. Here is that code. But I also would like to be able to automatically email from Outlook every dropdown option in cell B6 to the email in O6 which will change based on changing the cell in B6.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
April 18, 2020 - 11:55 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
3
April 20, 2020 - 4:18 pm
sp_Permalink sp_Print

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 referenceScreen-Shot-2020-04-19-at-11.04.03-PM.pngImage Enlarger

Screen-Shot-2020-04-19-at-11.06.55-PM.pngImage Enlarger
Screen-Shot-2020-04-19-at-11.11.10-PM.pngImage Enlarger
. 

sp_PlupAttachments Attachments
  • sp_PlupImage Screen-Shot-2020-04-19-at-11.04.03-PM.png (298 KB)
  • sp_PlupImage Screen-Shot-2020-04-19-at-11.06.55-PM.png (219 KB)
  • sp_PlupImage Screen-Shot-2020-04-19-at-11.11.10-PM.png (272 KB)
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
April 20, 2020 - 4:25 pm
sp_Permalink sp_Print

Hi Rob,

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

regards

Phil

Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
5
April 20, 2020 - 4:47 pm
sp_Permalink sp_Print

I'm also running into an issue here...Screen-Shot-2020-04-19-at-11.45.32-PM.pngImage Enlarger

Screen-Shot-2020-04-19-at-11.45.40-PM.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Screen-Shot-2020-04-19-at-11.45.32-PM.png (169 KB)
  • sp_PlupImage Screen-Shot-2020-04-19-at-11.45.40-PM.png (315 KB)
Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
6
April 20, 2020 - 4:49 pm
sp_Permalink sp_Print

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

Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
7
April 20, 2020 - 4:59 pm
sp_Permalink sp_Print

Here you go...

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
8
April 20, 2020 - 5:08 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
9
April 20, 2020 - 5:16 pm
sp_Permalink sp_Print

Ok, try this version.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
10
April 20, 2020 - 7:40 pm
sp_Permalink sp_Print

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

Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
11
April 20, 2020 - 11:46 pm
sp_Permalink sp_Print

Phil,

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
12
April 21, 2020 - 8:17 am
sp_Permalink sp_Print

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

Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
13
April 21, 2020 - 8:34 am
sp_Permalink sp_Print

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.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
14
April 21, 2020 - 8:49 am
sp_Permalink sp_Print

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

Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
15
April 21, 2020 - 9:11 am
sp_Permalink sp_Print

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
16
April 22, 2020 - 3:37 pm
sp_Permalink sp_Print

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

Avatar
Rob Perkins
Member
Members
Level 0
Forum Posts: 10
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
17
April 22, 2020 - 5:57 pm
sp_Permalink sp_Print

Phil,

That website was very helpful - much appreciated!  So I now can email my document from excel with Ron de Bruin's code, which I have attached.  The only thing I can't do is email the various dropdown options.  Do you know how I might be able to incorporate the dropdown portion of your macro into Ron's code?

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
18
April 28, 2020 - 11:25 am
sp_Permalink sp_Print sp_EditHistory

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.