Email Addresses

[Phil Walters]

Hi Guys

I have a question. I have saved an outlook template for use when evaluating suppliers and an Excel 2007 spreadsheet with supplier account details including email addresses. Ideally for this particular spreadsheet i would like to be able to click on anyone of the email addresses and have it open the template automatically. i have set up the email addresses using the hyperlink method which may or may not be correct.

any help would be very much appreaciated.

Phil

[Kyle123]

This works when double clicking the cell with the email address in. You'll need to change the path below to where your template is saved:

The Red is the path for the template
The Blue is the range where your email addresses are

You'll want to remove the hyperlinks from the sheet

This code goes into the worksheet module of the sheet with your email addresses in.

Right click on the sheet name at the bottom, right click > view code

Paste the below into the blank white screen that comes up

Please Login or Register  to view this content.

[Phil Walters]

Hi kyle

when i double click i get one of two things

1, i open a new email message with the correct address present
2, i have a flashing cursor where i happened to double click in the selected cell

keeping the faith

Thanks for your kind help.

Phil

[Kyle123]

Hi Phil

i open a new email message with the correct address present

Isn't that what you wanted?

[Phil Walters]

Hi Kyle

yes and no. I do want it to open outlook but using a saved template. when you say remove the hyperlink i have tried that on one of the cells then double clicked as you say but all it does is return it to a hyperlink. nothing happens to allow me to insert the code you have added.

[Kyle123]

Have you done this?

Right click on the sheet name at the bottom, right click > view code

Paste the below into the blank white screen that comes up

[Phil Walters]

Hi Kyle

you the man.

i am still however opening a normal email page as well as the template i require.

Any thoughts?

Phil

[Kyle123]

Have you changed this to the path of your template?

Please Login or Register  to view this content.

[Phil Walters]

Hi kyle

Ignore that.

It is working perfectly. Thankyou so much for your help.

Phil

[Phil Walters]

Hi Kyle

tried closing it and i am struglling as it says something about VB when i save with yes no cancel as a response.

How do i save it as when i re-opened it all the code has disappeared lol

So near yet so far.

|Phil

[Kyle123]

You may be using excel 2007 onwards which I believe has restrictions on which types of workbooks you can run macros in. Unfortunately I only have '03 so can't help, but I'm sure someone will

[Phil Walters]

Thanks Kyle

Can anyone else help here with regard to saving the information above. the file works great but i am unable to save it using 2007

Please help.

[Phil Walters]

Hi Guys

I have managed to to save the file as a Macro Enabled Workbook and all seems fine. The only issue i have now is that when i double click on the email address i require it opens 2 new email messages, one being the saved template i require and being a standard new mail message both for the same recipient.

Any ideas how to stop this would be greatly appreciated.

Phil

[Kyle123]

I asked here http://www.excelforum.com/the-water-...thread-23.html how to resolve this. I don't have 2007 but at a guess you need to Save as a macro workbook so the extension needs to be xlsm or xls (for backward compatibility). In 2003 I would go to file > save as > then change the bottom dropdown box to change the file type.

I know 2007 is different though, but I hope this helps

[Phil Walters]

For More information i have noticed that if i double click an email address which hasnt been clicked before the data works perfectly and only opens the email template i require. If i then go back to this address (which has subsequently turned blue and underlined) it opens both the template and a standard new message. Also just to add to my woes, not all of my email addresses turn blue after i double click them,

Thanks in anticipation

Phil

[Kyle123]

Morning Phil

You need to remove the automatic hyper links, in 2003, this is done in the auto-correct options, I'm guessing it's similar in 2007 onwards?

[Phil Walters]

Morning Kyle

I guess you know what the next question is going to be lol

Where will i find that?

Phil

[Kyle123]

No idea, try googling it.....

[Phil Walters]

Morning again

it would appear that i have sussed it out from your description. Slightly different way to do it in 2007 but i think it is right. i am going to save it all to the server reset the paths and see what happens. hopefully i will be adding a solved tag in a few minutes.

Anyway Thank you very much for your excellent knowledge Kyle and have a great Christmas.

Phil