Macro to convert email address to web address?

[Victor Delta]

I have an Excel (2003) spreadsheet in which I record client data - one row
per client. Two adjacent columns contain respectively their email address
(Col K) and web address (Col L).

Sometimes the web address is missing, although it is often obvious from the
email address (i.e. say the email address is (e-mail address removed) and so the web
address would be www.bloggs.com). I would therefore like to have a macro
which when run, with the appropriate empty web address cell (say L6)
selected, will convert the email address in K6 to the appropriate web
address in L6.

Can anyone please tell me how to achieve this?

Many thanks.

 

[Ron Rosenfeld]

I have an Excel (2003) spreadsheet in which I record client data - one row
per client. Two adjacent columns contain respectively their email address
(Col K) and web address (Col L).

Sometimes the web address is missing, although it is often obvious from the
email address (i.e. say the email address is (e-mail address removed) and so the web
address would be www.bloggs.com). I would therefore like to have a macro
which when run, with the appropriate empty web address cell (say L6)
selected, will convert the email address in K6 to the appropriate web
address in L6.

Can anyone please tell me how to achieve this?

Many thanks.

This first will look to the left of the selected cell if the selected
cell is in column L to construct a web address.

=================================
Sub WebAddressSelect()
Dim c As Range
Set c = Selection
If c.Column <> 12 Then Exit Sub
If c(1, 0).Hyperlinks.Count = 1 Then
c.Hyperlinks.Add c, "www." & _
Mid(c(1, 0).Text, InStr(c(1, 0).Text, "@") + 1)
End If
End Sub
===============================



The following will go down the column of email addresses in "L". If
there is no hyperlink (web address) in the adjacent cell, it will
construct one and place it in that address:

=============================
Option Explicit
Sub WebAddress()
Dim rg As Range, c As Range
Dim i As Long
i = 1
Do
Set rg = Cells(i, "K")
i = i + 1
Loop Until rg.Hyperlinks.Count = 1
Set rg = Range(rg, Cells(Cells.Rows.Count, "K").End(xlUp))

For Each c In rg
If c.Hyperlinks.Count = 1 And c(1, 2).Hyperlinks.Count = 0 Then
c.Hyperlinks.Add c(1, 2), "www." & Mid(c.Text, InStr(c.Text,
"@") + 1)
End If
Next c
End Sub
===================================

 

[Victor Delta]

This first will look to the left of the selected cell if the selected
cell is in column L to construct a web address.

=================================
Sub WebAddressSelect()
Dim c As Range
Set c = Selection
If c.Column <> 12 Then Exit Sub
If c(1, 0).Hyperlinks.Count = 1 Then
c.Hyperlinks.Add c, "www." & _
Mid(c(1, 0).Text, InStr(c(1, 0).Text, "@") + 1)
End If
End Sub
===============================



The following will go down the column of email addresses in "L". If
there is no hyperlink (web address) in the adjacent cell, it will
construct one and place it in that address:

=============================
Option Explicit
Sub WebAddress()
Dim rg As Range, c As Range
Dim i As Long
i = 1
Do
Set rg = Cells(i, "K")
i = i + 1
Loop Until rg.Hyperlinks.Count = 1
Set rg = Range(rg, Cells(Cells.Rows.Count, "K").End(xlUp))

For Each c In rg
If c.Hyperlinks.Count = 1 And c(1, 2).Hyperlinks.Count = 0 Then
c.Hyperlinks.Add c(1, 2), "www." & Mid(c.Text, InStr(c.Text,
"@") + 1)
End If
Next c
End Sub
===================================

Ron

Very many thanks for those. I'll probably stick to using the first macro as
some email addresses reflect client's isps etc rather than their websites eg
(e-mail address removed) etc

V

 

[Ron Rosenfeld]

Ron

Very many thanks for those. I'll probably stick to using the first macro as
some email addresses reflect client's isps etc rather than their websites eg
(e-mail address removed) etc

V

Glad to help. Thanks for the feedback.

 

[Victor Delta]

Glad to help. Thanks for the feedback.

Ron

Macro 1 works well and does exactly what was requested. However it has minor
shortcoming. When the web address is produced, it appears as a hyperlink
(i.e. blue and underlined). However, when you click on it, an error message
comes up "Cannot open the specified file" and hovering over the hyperlink
shows it is trying to open a file in the c drive with the name of the web
address.

Is there a way round this please?

V

 

[Ron Rosenfeld]

Ron

Macro 1 works well and does exactly what was requested. However it has minor
shortcoming. When the web address is produced, it appears as a hyperlink
(i.e. blue and underlined). However, when you click on it, an error message
comes up "Cannot open the specified file" and hovering over the hyperlink
shows it is trying to open a file in the c drive with the name of the web
address.

Is there a way round this please?

V

Try this: (note the addition of http://)

=========================
Option Explicit
Sub WebAddressSelect()
Dim c As Range
Set c = Selection
If c.Column <> 12 Then Exit Sub
If c(1, 0).Hyperlinks.Count = 1 Then
c.Hyperlinks.Add c, "http://www." & _
Mid(c(1, 0).Text, InStr(c(1, 0).Text, "@") + 1)
End If
End Sub
============================

 

[Victor Delta]

Try this: (note the addition of http://)

=========================
Option Explicit
Sub WebAddressSelect()
Dim c As Range
Set c = Selection
If c.Column <> 12 Then Exit Sub
If c(1, 0).Hyperlinks.Count = 1 Then
c.Hyperlinks.Add c, "http://www." & _
Mid(c(1, 0).Text, InStr(c(1, 0).Text, "@") + 1)
End If
End Sub
============================

Ron

Many thanks. That certainly does the trick but is there any way to hide the
http:// as none of the other (pasted in) web addresses have, or need, that?

I notice that just clicking on the cell (containing a www address) and
pressing enter seems to convert it into a web ready hyperlink. Is there a
way of doing this in the original macro perhaps?

V

 

[Ron Rosenfeld]

Ron

Many thanks. That certainly does the trick but is there any way to hide the
http:// as none of the other (pasted in) web addresses have, or need, that?

I notice that just clicking on the cell (containing a www address) and
pressing enter seems to convert it into a web ready hyperlink. Is there a
way of doing this in the original macro perhaps?

V

Yes, you just change the "text to display" property:

==========================
Option Explicit
Sub WebAddressSelect()
Dim c As Range
Dim s As String
Set c = Selection
If c.Column <> 12 Then Exit Sub
If c(1, 0).Hyperlinks.Count = 1 Then
s = "www." & Mid(c(1, 0).Text, InStr(c(1, 0).Text, "@") + 1)
c.Hyperlinks.Add c, "http://" & s
c.Hyperlinks(1).TextToDisplay = s
End If
End Sub
==============================

 

[Victor Delta]

Yes, you just change the "text to display" property:

==========================
Option Explicit
Sub WebAddressSelect()
Dim c As Range
Dim s As String
Set c = Selection
If c.Column <> 12 Then Exit Sub
If c(1, 0).Hyperlinks.Count = 1 Then
s = "www." & Mid(c(1, 0).Text, InStr(c(1, 0).Text, "@") + 1)
c.Hyperlinks.Add c, "http://" & s
c.Hyperlinks(1).TextToDisplay = s
End If
End Sub
==============================

You're a genius! Many thanks,

V

 

[Ron Rosenfeld]

You're a genius! Many thanks,

V

Not a genius, but I am tenacious.

Thanks for the feedback. Glad to help.