Using Macro how to create email link for the email addresses in aRange or Selection

[Satish]

Hi
Can anyone help me in writing a macro on how to create email link
for the email addresses in a Range or Selection.As i am new to excel i
am manually selecting each cell which is having email address , 'Press
F2' and then "Press ENTER".I want to automate the process and save the
precious time.

 

[Dave Peterson]

If your data is in A1:A10 (say), you could add some formulas in B1:B10:

Put this in B1:
=hyperlink("mailto:" & a1,"Click to send email")
and drag down to B10

 

[Eduardo]

Hi,
in another column enter

=HYPERLINK(D3&";")

Change D3 for the cell you have the email

 

[Satish]

Thanks for the reply, but can you help in writing a macro where i want
to do it for a range.

Regards
Satish

 

[Dave Peterson]

What does this mean?

I figured that a cell would contain a single email address. So each cell would
require its own hyperlink.

 

[Satish]

Hi Dave,
Yes each cell contains single email address.In a range,
the cells which are having email address should be hyperlinked.

Regards
Satish

 

[Dave Peterson]

And how did it not work?

What was in the cell with the address (the exact text)?
What was the address of that cell?
What was the formula you used in the adjacent cell?
What was the address of the cell with the formula?

 

[Satish]

Hi Dave,
Thanks for the reply ...the solution which you mentioned
worked for me successfully.But the problem is , every time i has to
type this formula on the adjacent cell and do it.

But i need a solution for the emails to be hyperlinked in the range
(A1: G10) using macro?

Regards
Satish

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim myCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
For Each myCell In .Range("a1:g10").Cells
If myCell.Value = "" Then
'do nothing
Else
.Hyperlinks.Add Anchor:=myCell, _
Address:="mailto:" & myCell.Value
End If
Next myCell
End With

End Sub