Using Advanced Filter to extract email addresses
 

Hello, I have a database of email addresses hyperlinks, that a department
needs to do an advanced filter (extract) on so they can copy the data to a
new file to be emailed out. However when you extract the data using the
advanced filter, the extracted data is no longer hyperlinked to the email
address.

Ordinarily i would just use Hyperlink(extractedcolumn) to fix this, but I
wanted a more automated solution since the data needs to be copied to a new
workbook (book1, book2, etc) so they can save the new file with a new name.

Thanks in advance for any suggestions!

ITD3

Using Advanced Filter to extract email addresses
 

Hi Frank

You can use a macro to create the links if you want

This macro for column A

Sub test()
For Each myCell In Columns("A").Cells.SpecialCells(xlCellTypeConstant s)
If myCell.Value Like "?*@?*.?*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value
End If
Next
End Sub





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Frank Wood" <Frank wrote in message ...
Hello, I have a database of email addresses hyperlinks, that a department
needs to do an advanced filter (extract) on so they can copy the data to a
new file to be emailed out. However when you extract the data using the
advanced filter, the extracted data is no longer hyperlinked to the email
address.

Ordinarily i would just use Hyperlink(extractedcolumn) to fix this, but I
wanted a more automated solution since the data needs to be copied to a new
workbook (book1, book2, etc) so they can save the new file with a new name.

Thanks in advance for any suggestions!

ITD3

Using Advanced Filter to extract email addresses
 

Wow, thanks Ron! I'll give it a whirl and see if it will work for what I need.

Frank Wood
itd3

"Ron de Bruin" wrote:

Hi Frank

You can use a macro to create the links if you want

This macro for column A

Sub test()
For Each myCell In Columns("A").Cells.SpecialCells(xlCellTypeConstant s)
If myCell.Value Like "?*@?*.?*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value
End If
Next
End Sub





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Frank Wood" <Frank wrote in message ...
Hello, I have a database of email addresses hyperlinks, that a department
needs to do an advanced filter (extract) on so they can copy the data to a
new file to be emailed out. However when you extract the data using the
advanced filter, the extracted data is no longer hyperlinked to the email
address.

Ordinarily i would just use Hyperlink(extractedcolumn) to fix this, but I
wanted a more automated solution since the data needs to be copied to a new
workbook (book1, book2, etc) so they can save the new file with a new name.

Thanks in advance for any suggestions!

ITD3

Using Advanced Filter to extract email addresses
 

Wow! It works PERFECTLY! I modified it to the appropiate column letter and
inserted it into my macro. Shazam! Worked right out of the shoot!

Thanks a bunch Ron!

Frank Wood
ITD3

"Ron de Bruin" wrote:

Hi Frank

You can use a macro to create the links if you want

This macro for column A

Sub test()
For Each myCell In Columns("A").Cells.SpecialCells(xlCellTypeConstant s)
If myCell.Value Like "?*@?*.?*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value
End If
Next
End Sub





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Frank Wood" <Frank wrote in message ...
Hello, I have a database of email addresses hyperlinks, that a department
needs to do an advanced filter (extract) on so they can copy the data to a
new file to be emailed out. However when you extract the data using the
advanced filter, the extracted data is no longer hyperlinked to the email
address.

Ordinarily i would just use Hyperlink(extractedcolumn) to fix this, but I
wanted a more automated solution since the data needs to be copied to a new
workbook (book1, book2, etc) so they can save the new file with a new name.

Thanks in advance for any suggestions!

ITD3

Using Advanced Filter to extract email addresses
 

You are welcome

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Frank Wood" wrote in message ...
Wow! It works PERFECTLY! I modified it to the appropiate column letter and
inserted it into my macro. Shazam! Worked right out of the shoot!

Thanks a bunch Ron!

Frank Wood
ITD3

"Ron de Bruin" wrote:

Hi Frank

You can use a macro to create the links if you want

This macro for column A

Sub test()
For Each myCell In Columns("A").Cells.SpecialCells(xlCellTypeConstant s)
If myCell.Value Like "?*@?*.?*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value
End If
Next
End Sub





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Frank Wood" <Frank wrote in message ...
Hello, I have a database of email addresses hyperlinks, that a department
needs to do an advanced filter (extract) on so they can copy the data to a
new file to be emailed out. However when you extract the data using the
advanced filter, the extracted data is no longer hyperlinked to the email
address.

Ordinarily i would just use Hyperlink(extractedcolumn) to fix this, but I
wanted a more automated solution since the data needs to be copied to a new
workbook (book1, book2, etc) so they can save the new file with a new name.

Thanks in advance for any suggestions!

ITD3