Auto Create Hyperlinks

Question

So, here's my question.  I have a Excel spreadsheet. On that spreadsheet, I plug in various names in columns.  I want to be able to type in a name and have it convert to an e.mail address so that I can then click on the hyperlink and send an e.mail right from Excel. I can figure out how to create hyperlinks in cells, but I can't figure out how to do it when I randomly insert names.

For example, if I enter the name:

Smith.  I want to have it auto create a hyperlink to: ******@xyz.com and then if I enter someone elses name under Smith's, I want it to then do the same.

How can this be done?  Thanks in advance

Answer 1

So you got this far

=HYPERLINK("mailto:" &B6,C6)

B6 = ******@outlook.com

C6 = Dude

So need to keep going with the following:

First, You need to have a spreadsheet that has a column of all the names in one column and all the email addresses in another column. Unfortunately, there was something call SmartTags that would have done exactly what you want in Office 2007, but it was eliminated in 2010.

No matter what you do this spreadsheet, this will help you.

If you have all of these listed in your contact list

http://www.howto-outlook.com/howto/exportcontacts.htm

2 options for macro

You could use Event macro. This is a macro that is active all the time. You place this macro in the workbook module not in a regular macro module. Everytime you hit enter, it will check to see if there is a match between the value you entered and the names in the list. If you only put the names in one column like column A, then you can set the macro to only work when you enter data in column A

Your other macro option is just keep typing names until you are finished typing names, then select those names to apply the macro.

Here is an example of using such a list in Excel for a word document. That should give you a good idea of something to start with.

http://answers.microsoft.com/en-us/office/forum/office_2007-word/looking-for-how-to-create-an-excel-object-to-use/1fec69c1-e55e-453d-84b8-b874ea50f4a5

Now your other issue is that you want to automatically send an email.

That is another issue. If you look at the link above. That is an example of Word calling Excel to use a list. To send the email you need to call an Outlook object in Excel.

There are some examples out there. You need to make sure that in VBA that you set up a reference to Outlook or it will not work.

http://www.jpsoftwaretech.com/excel-vba/send-email-from-excel/ 

Scroll down to

Mail Worksheet Using Outlook

Look at

Sub MailSheet(wksht ``As Excel.Worksheet, recipAddress ``As String``, _

              ``msgBody ``As String``, msgSubject ``As String``)

You would call this macro in another macro with recipAddress=ActiveCell.value which is the one with the email address.

Answer 2

One way will be to use AutoCorrect option

Under file menu >> options >> proofing>> auto correct option  (Excel 2013)

eg Replace @ with @abc.com

Then once you type smith@ it will replace it with ******@abc.com