• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Hyperlink an E-Mail address to Send Mail

In a Table that is being created, the last column is the e-Mail address of the individual that we may either want to notify that their experiment is finished, or to possibly send them a notice that payment is now due.

What is the best way to apply a hyperlink to a cell address, that when clicked, will bring up an "Outlook" mail window?  We'll end up doing more with this later, but just trying to get the basics down first.

Thanks....
0
Cook09
Asked:
Cook09
  • 12
  • 12
  • 4
1 Solution
 
Chris BottomleyCommented:
Have you considered pressing enter at the end of the cell data entry?

Chris
0
 
zorvek (Kevin Jones)ConsultantCommented:
What Chris is trying to say is that when you enter an email address in a cell, Excel automatically creates a "mailto" link for you.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
And if that does not happen, change the cell's (or column's) format to General.

Kevin
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Cook09Author Commented:
I pull the e-mail addresses from the network and insert it into a cell.  When I press Enter in that cell, nothing happens.  I did combine a name with the @address and it turned into the blue hyperlink.  But, the first way is a lot easier to manage, if it can still be converted to a Mail hyperlink.
0
 
Chris BottomleyCommented:
The key point being pressing the enter key with the data in the cell rather than for example tabbing onto the next cell

Chris
0
 
zorvek (Kevin Jones)ConsultantCommented:
So the imported value is NOT really an email address but just the name.

You will have to use the HYPERLINK function:

   =HYPERLINK("mailto:"&A1&"@domain.com")

Kevin
0
 
Cook09Author Commented:
Well there are two issues.
1. VBA is placing the e-mail into the cell, sans human contact.  As this is a running log,someone would have to be tasked with going down list and pressing enter.
2. The format is General and still nothing happens when I press Enter.  Do I need to set the Enter to stay in the cell instead of moving to the next one?
0
 
zorvek (Kevin Jones)ConsultantCommented:
>1. VBA is placing the e-mail into the cell, sans human contact.  As this is a running log,someone would have to be tasked with going down list and pressing enter.

What is the data being placed in the cell? Please give an example.

>2. The format is General and still nothing happens when I press Enter.  Do I need to set the Enter to stay in the cell instead of moving to the next one?

No.

Kevin
0
 
Cook09Author Commented:
Is there a VBA method of taking the name and making it hyperlink?
0
 
zorvek (Kevin Jones)ConsultantCommented:
Yes. But please post an example "email address" first. I suspect it isn't really an email address based on your comments above.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
IF the email address is a complete and valid address then the VBA code looks like:

   Cell.Hyperlinks.Add Cell, "mailto:name@domain.com"

Kevin
0
 
Cook09Author Commented:
When the "Mail" address is pulled from a GetObject(adspath)...for brevity sake.  One of the objects is the eMail ("Mail") address of the individual.

It could pull up mine as Cook09@University.edu and that is what gets placed into the cell.
0
 
Chris BottomleyCommented:
Assuming VBA is ok then, Try right click of the tab and select view code.  Now type the following snippet into the code module.

Edit the column number '1' to your column of interest:

    Set rng = Intersect(Columns(1), Target)

Edit the domain ... '"@fred.com"' to your domain

            cel.Parent.Hyperlinks.Add Anchor:=cel, Address:= _
                "mailto:" & cel.Text & "@fred.com"

Chris
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cel As Range

    Set rng = Intersect(Columns(1), Target)
    If rng Is Nothing Then Exit Sub
    For Each cel In rng
        If cel.Hyperlinks.Count = 0 Then
            cel.Parent.Hyperlinks.Add Anchor:=cel, Address:= _
                "mailto:" & cel.Text & "@fred.com"
        Else
            'nothing
        End If
    Next
End Sub

Open in new window

0
 
Cook09Author Commented:
So, instead of just placing the full email address into the cell, I would use VBA and:

Range("A1") =  Cell.Hyperlinks.Add Cell, "mailto:name@domain.com"

Is that correct?  And if it is, then when clicked what type of Mail window will "poop-up?"
0
 
Chris BottomleyCommented:
Note the above assumes the domain is NOT in the column!
0
 
zorvek (Kevin Jones)ConsultantCommented:
Interesting. And yet editing that cell and pressing ENTER does NOT automatically create a mailto link. Something is amiss. Perhaps there are some extra spaces in the text?

In any event, this will work:

   Cell.Hyperlinks.Add Cell, "mailto:name@domain.com"

Or, assuming the name is in x where x is "GetObject(adspath).Field("Mail")... or whatever wacky syntax is being used, then:

   Cell.Hyperlinks.Add Cell, "mailto:" & x

Will do the deed.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
>So, instead of just placing the full email address into the cell, I would use VBA and: Range("A1") =  Cell.Hyperlinks.Add Cell, "mailto:name@domain.com" Is that correct?  And if it is, then when clicked what type of Mail window will "poop-up?"

No. I don't support poop-up windows.

The Hyperlinks.Add method is not a function. Just this:

   Cell.Hyperlinks.Add Cell, "mailto:name@domain.com"

or

   Cell.Hyperlinks.Add Cell, "mailto:" & x

Kevin
0
 
Cook09Author Commented:
Since I'm using 2007 Tables, my intersections are more like:

.Cells([nUserRow],[nMailCol])  
0
 
zorvek (Kevin Jones)ConsultantCommented:
Or even more specifically using your example range:

   Range("A1").Hyperlinks.Add Range("A1"), "mailto:name@domain.com"

or

   Range("A1").Hyperlinks.Add Range("A1"), "mailto:" & x

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Then:

   .Cells([nUserRow],[nMailCol]).Hyperlinks.Add .Cells([nUserRow],[nMailCol]), "mailto:name@domain.com"

or

   .Cells([nUserRow],[nMailCol]).Hyperlinks.Add .Cells([nUserRow],[nMailCol]), "mailto:" & x

Kevin
0
 
Cook09Author Commented:
Well, if windows are not "pooped up" I'm assuming that code can then be written to  automatically send a message if either that cell or another cell has data entered into it and the Event would trigger a pre-defined message to be sent?
0
 
Cook09Author Commented:
That seems easy enough to implement, just change how it is entered.  Since several pieces of data about an individual is entered at one time, it would just mean changing the prefix so the "X" is at the end.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Yes, but remember that "x" is really GetObject(adspath).Field("Mail").blahdeblahblah... or, in other words, the actual email address being stuffed in the cell.

Kevin
0
 
Cook09Author Commented:
Kevin,
Yes, I got both to work and a Poop Up Window occurs when they are clicked.  The Cell that they are on reads:

mailto:XXXX.   I tried it without it reading out the mailto, but it seems that is needed to tell Excel what is being requested of it.

Ron
0
 
zorvek (Kevin Jones)ConsultantCommented:
That can be fixed:

   .Cells([nUserRow],[nMailCol]).Hyperlinks.Add .Cells([nUserRow],[nMailCol]), "mailto:name@domain.com", TextToDisplay:="name@domain.com"

or

   .Cells([nUserRow],[nMailCol]).Hyperlinks.Add .Cells([nUserRow],[nMailCol]), "mailto:" & x,  TextToDisplay:=x

Kevin
0
 
Cook09Author Commented:
Excellent...I appreciate you being up to assist me with this.

Ron.
0
 
Cook09Author Commented:
Not only did i get a good first start, but it'll now look a little more professional.

Thanks
0
 
Cook09Author Commented:
Kevin,

I don't fully understand it, but when I use the [nUserName] & [@Mail] with the TextToDisplay, it works fine.  If I use the single domain Name [Cook@Mail], it will work and display the:
mailto:Cook@Mail, but it won't work with TextToDisplay  in displaying just the Cook@Mail.  It remains Blank.

I can revert back to {Cook] &[@Mail] and use the TextToDisplay or to let people know that's what needs to be clicked to generate an eMail, and just stick with the single [Name@Domain].  But, would you know why the single won't work with the TextToDisplay?    When I use the Immediate Window the eMail is correct, it's just puzzling.

Ron
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 12
  • 12
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now