Email to list of addresses in a named range

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
117
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have some code that used the active worksheet from which it would send out emails listed in a range of cells in the active worksheet.
Now I want to be able to do this but now I have the email addresses in another worksheet in a named range.
I am unable to write the VBA to get the addresses from the named range on anther worksheet.
Below is the current Piece of the VBA that was being used to get the email addresses.
The piece of code I think I need to change is in red Below
The named range is called "Others" on the "Email Addresses" work sheet
Cheers
NZAS

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
Dim Recipients, f
CurrentMonth = ""
' *****************************************************
' ***** You Can Change These Variables *********
EmailSubject = "Dispatch 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
Recipients = ActiveSheet.Range("R84:R86") 'Change this if you want to specify To email e.g. ActiveSheet.Range("H1") to get email from cell H1
For f = 1 To UBound(Recipients)
Email_To = Email_To & Recipients(f, 1) & "; "
Next f

Email_CC = "" 'Change this if you want to specify To email
Email_BCC = "" 'Change this if you want to specify To email
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Change this line

Recipients = ActiveSheet.Range("R84:R86")

For this:

Recipients = Range("Others")
 
Upvote 0
Solution
ok, then try this

VBA Code:
Recipients = Sheets("Email Addresses").Range("Others")
 
Upvote 0

Forum statistics

Threads
1,215,529
Messages
6,125,345
Members
449,220
Latest member
Edwin_SVRZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top