Going to email from Excel celll

Looking for "how to" or a utility, that will allow me to select / click / right click / etc. on a cell that has an email address in it that takes me right into composing an email (Outlook 2003). Tried creating a macro, no luck..... 

GDG 

A hyperlink will do that.

In xl2003 I just typed in my email addy and it automatically create a mailto hyperlink, which when clicked launches the Outlook mail item. 

I see I can post without login in first, which is why my post is from Anonymous. 

It's not working for me! Is my Excel too old? (Excel 97 SR-1). Niether insert hyperlink option works...

Which means what?....

I think Excel 97 is too old to support hyperlinks

There is another way. You can trap the event when users select a cell containing a hyperlink (which I have assumed is any text containing @), then use the Shell command in VBA to run "Mailto:" plus the email address to launch the default mail program. There is one problem - Shell needs the executable name, eg it won't run Word if you just ask it to run a .DOC file, so it's not going to run mailto either. There is a way round, using what are called API functions, so I've provided you with code below to do this.

In the VBA editor, you need to find the name of the sheet containing the email addresses, in the list on the left. Doubleclick it to bring up the code module for that sheet. Then replace whatever is there, with everything below, and then go back to your sheet and start clicking email addresses.

(The reason the code needs to be behind your email sheet, is that this is how we capture the user clicking on cells in that sheet).

CODE STARTS BELOW 

Option Explicit

Private Type SHELLEXECUTEINFO
    cbSize        As Long
    fMask         As Long
    hWnd          As Long
    lpVerb        As String
    lpFile        As String
    lpParameters  As String
    lpDirectory   As String
    nShow         As Long
    hInstApp      As Long
    lpIDList      As Long     'Optional; ignore
    lpClass       As String   'Optional; ignore
    hkeyClass     As Long     'Optional; ignore
    dwHotKey      As Long     'Optional; ignore
    hIcon         As Long     'Optional; ignore
    hProcess      As Long     'Optional; ignore
End Type

Private Const SEE_MASK_INVOKEIDLIST = &HC
Private Const SEE_MASK_NOCLOSEPROCESS = &H40
Private Const SEE_MASK_FLAG_NO_UI = &H400
Private Const SW_SHOWNORMAL = 1

Private Declare Function ShellExecuteEx Lib "shell32" Alias "ShellExecuteExA" (sei As SHELLEXECUTEINFO) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

Const SYNCHRONIZE = &H100000

Sub Shellx(strFilename As String, tWait As Boolean)

      Dim lngReturn As Long
      Dim Retval As Long
      Dim sei As SHELLEXECUTEINFO

      With sei
          .cbSize = Len(sei)
          .fMask = SEE_MASK_NOCLOSEPROCESS
          .hWnd = Application.hWnd
          .lpVerb = "open"
          .lpFile = strFilename
          .lpParameters = vbNullChar
          .lpDirectory = vbNullChar
          .nShow = SW_SHOWNORMAL
      End With
      lngReturn = ShellExecuteEx(sei)

If tWait = False Then Exit Sub

Retval = WaitForSingleObject(sei.hProcess, -1)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If InStr(Target.Text, "@") > 0 Then
    Shellx "mailto:" & Target.Text, False
  End If
End Sub

CODE ENDS

I was able to insert a hyperlink in xl97-SR2