• 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

Send PDF by email to recipent in a named range in excel|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Send PDF by email to recipent in a named range in excel|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 & MacrosSend PDF by email to recipent in a …
sp_PrintTopic sp_TopicIcon
Send PDF by email to recipent in a named range in excel
Avatar
Dennis Kent

New Member
Members
Level 0
Forum Posts: 2
Member Since:
August 6, 2015
sp_UserOfflineSmall Offline
1
February 8, 2018 - 11:38 am
sp_Permalink sp_Print

 Hi

I would like a VBA to send a PDF of a work sheet range to a list of recipients listed in a named range (Recipients) on the active worksheet.

I have tried add the range in the variables in red below form the code below but got error. I hope soem one can put me on the right track to get this working

 

 *****************************************************
' *****     You Can Change These Variables    *********

    EmailSubject = "Latest Dispach Sheet "   'Change this to change the subject of the email. The current month is added to end of subj line
    OpenPDFAfterCreating = False    'Change this if you want to open the PDF after creating it : TRUE = Yes or FALSE = No
    AlwaysOverwritePDF = False      'Change this if you always want to overwrite a PDF that already exists :TRUE or FALSE
    DisplayEmail = True 'Change this if you don't want to display the email before sending. True =Yes, False=No, Note,
        'you must have a TO email address specified for this to work
    Email_To = ActiveSheet.Range("Recipients")  'Change this if you want to specify To email e.g. ActiveSheet.Range("H1") to get email from cell H1
    Email_CC = ""   'Change this if you want to specify To email
    Email_BCC = "dennis.kent@pacificaluminium.com.au"  'Change this if you want to specify To email

************************************************************************************************************

 

This is the VBA I was using. with some minor changes to stop some actions working. You may recognise it form One I was copying from this site

Sub create_and_email_pdf()
'This creates a PDF file of active worksheet (or a rahge if named this one is PrintArea)
    'then will save it to a folder and then email to address stored in file
   
  ' 
  '
  ' Date 
  ' Create a PDF from the current sheet and email it as an attachment through Outlook

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 = ""

' *****************************************************
' *****     You Can Change These Variables    *********

    EmailSubject = "Latest Dispach Sheet "   'Change this to change the subject of the email. The current month is added to end of subj line
    OpenPDFAfterCreating = False    'Change this if you want to open the PDF after creating it : TRUE = Yes or FALSE = No
    AlwaysOverwritePDF = False      'Change this if you always want to overwrite a PDF that already exists :TRUE or FALSE
    DisplayEmail = True 'Change this if you don't want to display the email before sending. True =Yes, False=No, Note,
        'you must have a TO email address specified for this to work
    Email_To = ActiveSheet.Range("AB1:AB15")  'Change this if you want to specify To email e.g. ActiveSheet.Range("H1") to get email from cell H1
    Email_CC = ""   'Change this if you want to specify To email
    Email_BCC = "dennis.kent@pacificaluminium.com.au"  'Change this if you want to specify To email
          
' ******************************************************
   
    'Prompt for file destination
    'With Application.FileDialog(msoFileDialogFolderPicker)
       
    '    If .Show = True Then
       
    '        DestFolder = .SelectedItems(1)
           
    '    Else
       
    '        MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
               
    '        Exit Sub
           
    '    End If
       
    'End With
    DestFolder = "P:\METPROD\Warehouse\Domestic Disturbances\PDF Copies "

    'Current month/year stored in H6 (this is a merged cell)
    CurrentMonth = Format(Range("adate"), "ddmmyy")
   
    'Create new PDF file name including path and file extension
    PDFFile = DestFolder & CurrentMonth & ".pdf"

    'If the PDF already exists
    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 you want to overwrite the file then delete the current one
            'If OverwritePDF = vbYes Then
   
                Kill PDFFile
       
            Else
   
                '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"
               
                'Exit Sub
       
        End If

    Else
       
            'On Error Resume Next
            'Kill PDFFile
           
    End If
       
        '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"
               
          '  Exit Sub
       
           
   
  

    'Create the PDF
    With Sheets("Dom.Dispatch")
        .Columns("J:Q").EntireColumn.Hidden = True
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=OpenPDFAfterCreating
        .Columns("J:Q").EntireColumn.Hidden = False
    End With

        '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
    With OutlookMail
       
        .Display
        .To = Email_To
        .CC = Email_CC
        .BCC = Email_BCC
        .Subject = EmailSubject & CurrentMonth
        .Attachments.Add PDFFile
               
        If DisplayEmail = False Then
           
            .Send
           
        End If
       
    End With
   
 
End Sub

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
February 8, 2018 - 12:14 pm
sp_Permalink sp_Print sp_EditHistory

Hi Dennis

Email_To can only handle one address in the original code. If you have multiple addresses, you will need to add them one by one to Email_To.

Example (not tested)

DIM cel As Range

DIM rng as Range

Set rng  =  Range("Recipients")

For Each cel In rng

If Email_To ="" then

Email_To= cel.Value

Else

Email_To = Email_To & ";" & cel.Value

endif

Next

Now the Email_To will contain more than 1 email address

Hope this helps.

Sunny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: AndyC
Guest(s) 9
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
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
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.