Categories: Ask a "how-to" question :

Automaticly hyperlink email addresses?

Showing 1-3 of 3 messages
Automaticly hyperlink email addresses? Firemaple 8/17/09 11:22 AM
I work with a local non-profit and designed a spreadsheet which is automatically populated by a form in gdocs. We use that form to collect submissions from potential volunteers. The form has the standard fields: name, phone number, email etc. What I would like to know is:

Is there any way to have docs automatically update any email address submitted to a mailto: link? We have volunteers that monitor the list and contact folks who have submitted information and I'd like for them to be able to click the email link and compose a message directly.
I've looked around and see that you can create hyperlinks by using the formula option, but that would require going back through each individual entry to create the link - not a timesaver at all.

Does anyone know how to do this, and if not are you aware of any workarounds that would still be faster than copy/pasting hundreds of names?

Re: Automaticly hyperlink email addresses? ahab 8/17/09 11:38 AM
Try this:
Suppose the e-mail addresses are submitted in column B and column X is free to create a clickable hyperlink.
First remove all empty rows below the current submits (not clear, remove!).
In X2 put the following formula:
X2: =Arrayformula( HYPERLINK("mailto:" &TRIM(B2:B) ; "Send e-mail"))

Note that when another form is submitted the spreadsheet automatically adds a row to accommodate for the submitted data and the formula in column X will auto-expand.
Re: Automaticly hyperlink email addresses? Firemaple 8/17/09 12:01 PM
I plugged in the formula you supplied and it worked fantastically, I wish I hadn't spent nearly an hour scouring the boards.

Thanks so much!