Automate Email Address From Hyperlink Formatted Ce

Topic

New Reply

Hi Everyone,

It’s been some time since I’ve been here, and I’ve missed you all!

I need to understand how to automate manual steps I am now taking in Excel 2007.

Here’s my scenario:

• I receive a workbook that has at least 100 rows of data. Sometimes, many more rows.
• In each of those rows one cell contains a hyperlink formatted cell that displays a client’s name. For example: Abraham Lincoln.
• The hyperlink is Abe’s email address, abe_lincoln@logcabin.com
• Currently, I am clicking each hyperlink, open a new email in Outlook, then I copy the email address, and paste the email address back into the workbook in a different cell, not formatted as a hyperlink.
  [/list]I need a way to automate this process, so that I don’t have to open 100+ new emails and do all these manual copy and paste steps.

  Any suggestions?

  Cheers, and Thanks,
  Rich

Reply | Quote

Replies

Welcome back!

You could run this macro, after adjusting the constants at the beginning. SourceCol is the column containing the e-mail hyperlinks, and TargetCol is the column where you want the e-mail addresses.

Sub ExtractAddresses()
Const SourceCol = “A”
Const TargetCol = “B”
Dim r As Long
Dim m As Long
m = Range(SourceCol & Rows.Count).End(xlUp).Row
For r = 1 To m
Range(TargetCol & r) = Mid(Range(SourceCol & r).Hyperlinks(1).Address, 8)
Next r
End Sub

Reply | Quote

Hi Hans,

Cheers, and thanks for your quick reply.

I know I can create this macro in Excel 2003.

Can I create a macro like this in Excel 2007?

If so, how would I create the macro and run it?

Cheers,
Rich

Reply | Quote

The macro should work in Excel 2007 too – the object model hasn’t changed all that much, at least as far as the basics are concerned.

I don’t have Office 2007, but I assume that macros, the Visual Basic Editor etc. can be reached from the Developer tab of the Ribbon. *goes off*

*returns* Yes, see Create or delete a macro.

Reply | Quote

Hi Hans,

Yes, after I asked you, I went to Office Online Excel 2007 Help & How-to and found out that you can do this in Excel 2007!

Like you, I haven’t yet installed Excel 2007. Eventually, I’ll be forced to that, but until then…….!!!

My client was thrilled with your solution, and will save them tons of time!

Thanks and Cheers,
Rich

Reply | Quote