+ Reply to Thread
Results 1 to 15 of 15

Teacher wants quick email address list!

  1. 12-11-2018, 01:01 PM #1
    QBert14001
    QBert14001 is offline
    Registered User
    Join Date
    12-07-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Office 365
    Posts
    17

    Teacher wants quick email address list!

    I need a way to put a list of email addresses together based on a list of individual students that are missing an assignment. Pete_UK was great and helped me build my dynamic missing work list, and now I would like to be able to humbly ask for a way to put all their email addresses together into one cell separated by a semicolon and a space (for pasting into Outlook).

    If there's an easier way to do this than individually copying and pasting each address from a list, please let me know! Thank you all for your help!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-11-2018, 01:09 PM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,447

    Re: Teacher wants quick email address list!

    Why do you want to paste it in Outlook?
    Reply With QuoteRegister To Reply
  3. 12-11-2018, 01:14 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Teacher wants quick email address list!

    One way...

    Please Login or Register  to view this content.
    How to install your new code
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    Then use an array formula:
    =ConcatAll(X2:X31,"; ")


    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 12-11-2018, 08:29 PM #4
    QBert14001
    QBert14001 is offline
    Registered User
    Join Date
    12-07-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Office 365
    Posts
    17
    Quote Originally Posted by Pepe Le Mokko View Post
    Why do you want to paste it in Outlook?
    So I can email parents. It’s our district’s email provider.
    Reply With QuoteRegister To Reply
  5. 12-11-2018, 08:30 PM #5
    QBert14001
    QBert14001 is offline
    Registered User
    Join Date
    12-07-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Office 365
    Posts
    17
    Thanks, I’ll take a look when I’m next at my computer!
    Last edited by AliGW; 12-12-2018 at 12:50 PM. Reason: Unnecessary quotation reomved.
    Reply With QuoteRegister To Reply
  6. 12-11-2018, 09:23 PM #6
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,728

    Re: Teacher wants quick email address list!

    Please don't quote whole posts when you reply - it's just clutter. Use the Reply button, rather than Reply with Quote.

    Pete
    Reply With QuoteRegister To Reply
  7. 12-12-2018, 11:44 AM #7
    QBert14001
    QBert14001 is offline
    Registered User
    Join Date
    12-07-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Teacher wants quick email address list!

    Sorry, was replying from my mobile device. Shows only a "reply" button. I'll try to delete the quote part working on this device next time!
    Last edited by QBert14001; 12-12-2018 at 04:32 PM.
    Reply With QuoteRegister To Reply
  8. 12-12-2018, 04:48 PM #8
    QBert14001
    QBert14001 is offline
    Registered User
    Join Date
    12-07-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Teacher wants quick email address list!

    Glenn Kennedy, I tried the code, and it puts all of the emails into one cell separated by a semi-colon, but it doesn't change when I change the assignment I'm looking at. So if Student 1, 3, and 5 are missing assignment 1, I'd like to select assignment 1 from my dropdown menu (which gives me the list including Students 1, 3, and 5) and have the email addresses listed in one cell separated by a semicolon with a space so I can paste that into Outlook to email those parents. When I change to look at Assignment 2, I might have Student 2, 13 and 15-19 missing it. I'd like the email addresses cell to update to match those students' emails. I think there's a missing piece to link what your code does to what I already have. Does that make sense?
    Reply With QuoteRegister To Reply
  9. 12-12-2018, 05:01 PM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Teacher wants quick email address list!

    It wouldn't... as you didn't ask for that
    I'll be back in the UK morning.
    Reply With QuoteRegister To Reply
  10. 12-12-2018, 05:12 PM #10
    QBert14001
    QBert14001 is offline
    Registered User
    Join Date
    12-07-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Teacher wants quick email address list!

    Ah, I was not as explicit as I could have been. I guess I was more implicit when I mentioned the dynamic list than I needed to be, my apologies! BUT I think I figured it out. I set up a VLOOKUP function next to the dynamic list to return the email addresses of the students missing a given assignment, then changed your formula to match the new range location and it works! Thank you for your help!

    Since I have no idea how to use VBA (so if I lose the file or this post I'll have to start all over), is there a non-macro way to do the Conconating without having to separate each cell location with "; "? As in =concat(A1, "; ",A2,"; "...). Is there a way to select an entire range without VBA and have a semicolon/space put between them all? As in =concat(A1:A32,"; ")?

    Thanks again!
    Attached Files Attached Files
    Last edited by QBert14001; 12-12-2018 at 05:29 PM.
    Reply With QuoteRegister To Reply
  11. 12-12-2018, 05:26 PM #11
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Teacher wants quick email address list!

    Great job. You're welcome.


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Reply With QuoteRegister To Reply
  12. 12-13-2018, 01:01 AM #12
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Teacher wants quick email address list!

    An afterthought.

    Since you have Office 365 try this.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  13. 12-13-2018, 07:33 AM #13
    QBert14001
    QBert14001 is offline
    Registered User
    Join Date
    12-07-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Office 365
    Posts
    17
    Thanks for your reply, FlameRetired, I’ll try it the next chance I get. That sounds a lot easier than concat!
    Reply With QuoteRegister To Reply
  14. 12-14-2018, 06:13 AM #14
    QBert14001
    QBert14001 is offline
    Registered User
    Join Date
    12-07-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Teacher wants quick email address list!

    Textjoin is exactly what I was looking for! Thank you, FlameRetired!
    Reply With QuoteRegister To Reply
  15. 12-14-2018, 12:06 PM #15
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Teacher wants quick email address list!

    You are welcome. Glad to help. Thank you for the feedback, rep and marking your thread Solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Email a List based on a criteria to the Email address in another workbook
    By mikemakany in forum Excel General
    Replies: 0
    Last Post: 10-03-2017, 08:22 AM
  2. [SOLVED] vba email send from excel - ignore second email address if "ENTER EMAIL ADDRESS"
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2016, 07:22 AM
  3. Macro to email attachments (excel file/s), copy body and lookup email address from a list
    By Sae12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2013, 04:46 AM
  4. [SOLVED] Teacher Needs help email progress reports
    By pvanderlinden in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 12-21-2012, 09:59 AM
  5. Email selected print section to address located in a cell instead of email address in code
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2012, 01:57 PM
  6. send email from XL list of email address
    By pauluk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2009, 11:36 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1