Auto updating the copied/pasted output of a formula rather than formula itself

I am creating a press contacts list and want to be able to copy and paste the email addresses from the spreadsheet into an email in a oner. The excellent advice on the forums has allowed me to combine all the email addresses in column B of the attached to a single cell in Column D. I then used the 'values' option in 'paste special' to copy and paste the content in column D to A cell in column E so i can just copy and paste the text across.

All good so far - except that the cell in column D auto updates when I add to column B but, of course, column E doesn't as it is just a copy / paste.

This is to be used by folk with even less Excel knowledge than me so I would like the 'output' cell with all the copy-able email addresses in to auto update too. So that, each time, folk can just go in and copy the email addresses in a oner and paste them straight into an email>

Is there a way of doing this?

Thanks

G

You could use this in E4:

=SUBSTITUTE(D4,CHAR(10)," ")

You could then lock the cell and create a macro assigned to a button to copy the contents to the clipboard.

No, they aren't, believe me. All you need to do is record a series of keystrokes that unlock the sheet, copy the text to the clipboard, then lock the sheet again. That's it. You can then assign the macro to a button.