Join Email Addresses Meeting Condition In Corresponding Cells

  • I am currently working with a team of people who are on connected to different networks, and frequently coming on and off the project. We hold a spreadsheet with everyones details, and some of these details are the mailing lists each person should be subscribed to. For example "System Downtime","Team Leads" etc.
    As people come and go, we dont want them recieving emails when they are not on the project.
    Attached is a greatly cut down version of what we have.
    For most users the data will be locked down - we want them to be able to copy the mailing lists to their clipboard via a dashboard so they can then paste it into their Outlook.


    Pseudo code:
    On clicking "Copy to your clipboard"
    Lookup mailing list selected in drop down (This dropdown validates on the MailingList range D2:I2)
    Look for the corresponding column for that mailing list
    Only look at rows where the individual is on the project ("Yes")
    For each person with "Yes" in the column for that list, concatenate their email address
    Copy the result to the users clipboard, ready for them to paste into the To: field in their browser

  • Re: Create Mailing Lists From A Spreadsheet


    Try from sheet Interface :

  • Re: Create Mailing Lists From A Spreadsheet


    Thanks, thats great. Much appriciated.


    What changes would I need to make the code if I were to take the drop down and command button onto a seperate sheet - So that we can hide the source data from the end user?


    I have tried without much luck!


    Thanks

  • Re: Create Mailing Lists From A Spreadsheet


    The command button and the drop down are on the "Interface" sheet so, you can hide the "data" sheet. In the attached file, the sheet is "veryhidden", that means that it can only be displayed by macro.
    For further protection, the workbook must be protected with a password, so that it has to be unprotected again before the sheet can be displayed.

  • Re: Join Email Addresses Meeting Condition In Corresponding Cells


    Consider pre-setting Custom Views Based on AutoFilters for List1, List2 etc and showing the view chosen from the Validation list. Then concatenate the visible emails.

  • Re: Join Email Addresses Meeting Condition In Corresponding Cells


    Great stuff,
    This has just saved me so much time! No more updating everyones mailing lists!


    Thank you both,


    Kind Regards,
    Tom

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!