none
Email address in hyperlink field RRS feed

  • Question

  • I have a table with a hyperlink field for the email address for each person in the table. Some addresses I had entered by hand, but many were in a spreadsheet, so I wrote a program to take the email address from the spreadsheet and do an edit then update on the table with the email field. When I click on the ones that were imported in, they don't act like hyperlinks, i.e., Outlook doesn't open with a new email.  When I right-click on the hyperlink and choose Edit, the Email address field is blank.  If I copy what is in the Text to Display field and paste it in the Email address field, it shows mailto:johndoe@email.com, then the hyperlink works properly.

    Is there some code I could write that would go through the entire table and paste what is in the Text to Display field into the Email address field?  I'm using Access 2010.


    Jill Marlow

    Friday, May 19, 2017 4:10 PM

Answers

  • A hyperlink field is just a specially constructed text field with a flag that says to treat it like a hyperlink.  The ones you programmatically inserted weren't properly constructed.  You can try an update query like this to fix them:
     
    UPDATE [Your Table] SET [Your Table].[YourEmailField] = [YourEmailField] & "#mailto:" & [YourEmailField] & "#"
    WHERE ([Your Table].[YourEmailField] Not Like "*[#]mailto:*[#]"

    Of course, make sure you back up the table or the database before running a mass-update query like this.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Jill Marlow Friday, May 19, 2017 6:44 PM
    Friday, May 19, 2017 5:30 PM

All replies

  • A hyperlink field is just a specially constructed text field with a flag that says to treat it like a hyperlink.  The ones you programmatically inserted weren't properly constructed.  You can try an update query like this to fix them:
     
    UPDATE [Your Table] SET [Your Table].[YourEmailField] = [YourEmailField] & "#mailto:" & [YourEmailField] & "#"
    WHERE ([Your Table].[YourEmailField] Not Like "*[#]mailto:*[#]"

    Of course, make sure you back up the table or the database before running a mass-update query like this.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Jill Marlow Friday, May 19, 2017 6:44 PM
    Friday, May 19, 2017 5:30 PM
  • That worked great!  Thanks.

    Jill Marlow

    Friday, May 19, 2017 6:44 PM