I Have some VBA code to save a sheet as a pdf and then email it out, what i would like to know is how can i reference a cell on a different sheet on the same wrokbook to reference the email address
i thought of using
but it didnt work
Please Login or Register to view this content.
any ideas on what i can use?
If more than one workbook is open, make sure you're referencing the right one:Please Login or Register to view this content.
Last edited by leelnich; 02-22-2018 at 03:12 PM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
hi thanks for the reply
ive tried your suggestions, and the VBA runs without errors but the email address field remains empty
is there something im missing, should they be in quotation marks?
I just realized you're missing quotes in your range address:Do you have an On Error Resume Next statement in effect? Because that should have errored!Please Login or Register to view this content.
Last edited by leelnich; 02-22-2018 at 06:19 PM.
awesome, cheers dude
How do i reference a alarger range,
ie A2:A20, do they need to be in quotations?
Yes, the argument may be a string, or another range object. Here' s a method for finding all used cells in column A:http://www.excel-easy.com/vba/range-object.htmlPlease Login or Register to view this content.
Last edited by leelnich; 02-22-2018 at 06:28 PM.
Cheers
How would i reference that
would it be something like this
Please Login or Register to view this content.
Actually, setting an object variable = to a range includes the Parent Objects of that range. So, again make sure your referencing the right workbook/worksheet when defining MyRange. Otherwise it assumes ActiveWorkbook.ActiveSheet.Notice the periods leading "Range", "Cells", and "Rows". They indicate the object is a "member" of the With Object.Please Login or Register to view this content.
Last edited by leelnich; 02-22-2018 at 07:01 PM.
WOW that looks a little above my current understanding of VBA
Please Login or Register to view this content.
here is my code , would it be possible to add your code to it please, im a little unsure where it will go
That's just a snippet that demos using a loop to take some particular action on a bunch of cells. It doesn't appear to apply to your specific macro. Where in your code are you trying to reference a multi-cell range?
Please Login or Register to view this content.
thank you
Ok, you're trying to send the same email to a bunch of people, so you need to build a recipient list - a text string with addresses separated by ";". VBA's Join function is perfect for this. It takes an array of values - from a cell range in this case - and joins them using a specified delimiter. It's a little arcane, because Excel ranges are 2-dimensional arrays (rows AND columns), while Join needs a 1-dimensional array (row). So we have to use Excel's Transpose function to convert our column range into a row array. See below:Alternatively, you could loop through the range as the demo did, appending ";" and an address to the end of a string on each pass. But that's slower.Please Login or Register to view this content.
Last edited by leelnich; 02-22-2018 at 08:27 PM.
Thank you
in terms of this line
i thought it would have been something like thisPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by kobiashi; 02-22-2018 at 09:05 PM.
Sorry, you are correct. Assuming sheet "email" is part of the ActiveWorkbook, your changes should work as shown. Nice catch!
I've added it but it came up with a run time error 9, am i missing something
Please Login or Register to view this content.
Cancel that, its working
thank you for your help
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks