Imported data not recognizing email address hyperlinks

Question

Hi,

I have a database that I import Excel data into. After importing, the email addresses do display as hyperlinks (the data type is hyperlink, and the text is blue with an underline), and they appear to act as a hyperlink - when I hover, the little pointing finger appears as I click the text. The problem is that the text is not an actual hyperlink. After it's clicked, nothing happens.

When I delete the imported hyperlink, and enter it manually, the hyperlink opens an email message and the address is inserted in the To: field.

What I have discovered is that the imported data is being entered in the "text to display" field, but not in the address field.

In the original Excel file, the data does not appear as a hyperlink - so, we run a macro that turns the text into email hyperlinks - after this, when we click one of the email addresses in Excel, an email message appears with the address in the To: field.

So, does anyone know why the email addresses are not importing correctly, and how I can fix this?

FYI - not sure if this matters, but the Excel file is not saved as a macro enabled file because we only needed the macro to change one column of information to hyperlinks. After the macro creates the hyperlinks, we save the Excel file as a regular .xls file. The hyperlinks work in Excel even after saving.

So, again, the hyperlinks work in Excel prior to the import. But, after importing inot Access, they appear to be hyperlinks to email addresses, but Access does not display a new email message when the link is clicked. Nothing happens.

Suggestions??

Thanks!

Tammy

Answer 1

Open your Excel workbook.

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Create the following function:

Function Link4Access(rng As Range) As String

    With rng.Hyperlinks(1)

        Link4Access = .TextToDisplay & "#" & _

            .Address & "#" & .SubAddress

    End With

End Function

Switch back to Excel.

Let's say the hyperlinks are in column C.

Insert a blank column in column D

Enter a title in D1, e.g. EmailAddress.

Enter the formula =Link4Access(C2) in D2.

Fill down to the end of the data.

Save the workbook (if you want to keep the VBA code, it must be a .xlsm, .xlsb or .xls workbook, not .xlsx)

Now import into Access, and use the new column as hyperlink. (You can skip the original column)

Answer 2

I don't understand that. Does that already happen in the column with the formulas in Excel?

Answer 3

Thanks, Hans,

After inserting the formula, the emails addresses display as follows (fyi, they are "fake" email addresses):

" Jennifer1234 @ yahoo.com " displays as " ennifer1234 @ yahoo.com# "

" chris.x.johnston @ gmail.com " displays as " ohnston @ gmail.com# "

Thanks,

Tammy

Answer 4

The code looks OK.

This forum masks e-mail addresses in posts to protect users' privacy.

Could you provide an example and insert spaces before and after the @ to prevent the addresses from being masked? Thanks!

Answer 5

Hi HansV,

Thank you so much for responding to my post. I ran through the steps above, but after entering the formula and copying down, all of the email addresses are cut off at the beginning of the address.

Something like ******@yahoo.com , now appears as ******@yahoo.com #

or ******@gmail.com now appears as ******@gmail.com #

Any sugestions? I copied and pasted your code above - here is what displays in the VB Editor:

Function Link4Access(rng As Range) As String

    With rng.Hyperlinks(1)

        Link4Access = .TextToDisplay & "#" & _

            .Address & "#" & .SubAddress

    End With

End Function

Any sugestions? Thanks for any help you can provide.

Tammy