Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel with mailto (XP)

    The attached has two sheets - Contacts and Summary. The Summary allows me to type in the initial of last names in one cell and a drop down in the next cell allows me to select a user with the initial placed in the first cell. Then through the usage of index and match functions the remainder of the information ifrom the Contacts Sheet is placed into the Summary Sheet.

    The problem I have is that the hyperlinked mailto from the Contacts sheets loses the hyperlink when it is placed into the Summary sheet. Is there a way to have the hyperlink maintained?

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel with mailto (XP)

    Your Summary worksheet has a strange Worksheet_Change event procedure that messes up the data. Remove the formula from C13 and change the code to

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("C3") Then
    Sheets("Contacts").Range("C:C").Find(What:=Range(" C3"), _
    Lookat:=xlWhole).Offset(0, 4).Copy Destination:=Range("C13")
    End If
    End Sub

    This will copy the hyperlink into cell C13.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel with mailto (XP)

    Re the comment about messing up the data. What is messing it up? It works for all other data without problem.

    Note that the basis for this code is from www.contextures. I merely changed some lines in reference to the sheets involved and then added the extra index and match formulas as needed.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel with mailto (XP)

    Apparently, I don't understand what its purpose is. But did my reply help?

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel with mailto (XP)

    There is no real purpose as it presently set up. It is really just a way to quickly find information about clients from a large spreadsheet. I'm sure there are better ways, but I was interested and how Contexture used the code to be able to quickly jump to a certain location with in the database and then select the person's information. For example typing an S in the one cell shows only those names that begin with S in the second cell.

    As I mentioned, I found the original code at Contextures and then modified to use some information from Outlook Contacts to play around with the coding and the index/match functions. I'm just puzzled about why the mailto portion does not stay hyperlinked.

    Thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel with mailto (XP)

    A formula that refers to a cell with a hyperlink just returns text (the display text, to be precise), not a hyperlink.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •