Hi there,

In column f I have a formlae which uses a function to creae an email
address from a Excel hyperlink.
I then take the contents of column f and past values into column g - so
now within column g I have the actual email address - eg
(E-Mail Removed). Problem is the Excel is not recognising this as an
email address. If I press F2 to edit the cell, change nothing, and then
press enter it recognises it as an email address.

Question is, how can I automate the process for all entries in column G,
ie edit the cell, and then "press enter" without making a change to the
contents?

Kind Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

The only way you can do the auto-stuff is via the UI, which means sendkeys
(which is very bad indeed).

Instead you'd need to loop through the values and add the hyperlinks in the
format "private.php?do=newpm&u=" where links needed to be added.

Something like...

Set target = Sheet1.Range("c2")
Sheet1.Hyperlinks.Add target, "mailto:" & target.Value



"Darin Kramer" wrote:

> Hi there,
>
> In column f I have a formlae which uses a function to creae an email
> address from a Excel hyperlink.
> I then take the contents of column f and past values into column g - so
> now within column g I have the actual email address - eg
> (E-Mail Removed). Problem is the Excel is not recognising this as an
> email address. If I press F2 to edit the cell, change nothing, and then
> press enter it recognises it as an email address.
>
> Question is, how can I automate the process for all entries in column G,
> ie edit the cell, and then "press enter" without making a change to the
> contents?
>
> Kind Regards
>
> D
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Thanks for the reply - my VB skills are at the basic level... what would
I need next after the two lines you proposed...?

Regards

D



*** Sent via Developersdex http://www.developersdex.com ***

That's just a demo of how to do one cell. Here's the full code.

Sub SetHyperlinks()
Dim target As Range

For Each target In Sheet1.Range("G1:G65535").Cells
If target.Hyperlinks.Count = 0 Then
If InStr(target.Value, "@") > 0 Then
target.Hyperlinks.Add target, "mailto:" & target.Value
End If
End If
Next target
Set target = nothing
End Sub


"Darin Kramer" wrote:

> Thanks for the reply - my VB skills are at the basic level... what would
> I need next after the two lines you proposed...?
>
> Regards
>
> D
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>