Email Message in Excel (Excel 2003)

Topic

New Reply

One of my users whats to put a “hyperlink” in her workbook that if she clicks on it is should take her to the email. Are there any functionality already built in to do this.

TIA

Reply | Quote

Replies

If Excel is set to convert internet and network paths to hyperlinks in the AutoFormat As You Type tab of Tools | AutoCorrect Options (see screenshot), an e-mail address such as bill@microsoft.com will automatically be converted to a hyperlink that will create a new message to the e-mail address.

Reply | Quote

Thanks Hans for that info. My problem is I want to put a referance in excel to a message I’ve received. If I then press/select the cell it should open Outlook and show this “old” message.

TIA

Reply | Quote

I don’t think that is possible, but perhaps someone else will come up with a bright idea.

Reply | Quote

Hi Mario,
Yes you can, with certain limitations. To link to a message with subject ‘Vista: The Saga Continues’ in my Inbox, I would choose Insert-Hyperlink from the menu and then enter in the address field:

exactly as it appears here. You have to include the <
and >
symbols and you precede the item subject with ~

If you have more than one item with the same subject, you will get the first one. You can get round this if you can get the EntryID by using:

HTH

Reply | Quote

Aha! I didn’t know that, so thanks!

Reply | Quote

I should print this out and frame it for posterity then!
I suspect that with Exchange Server you can probably use some complicated LDAP syntax but I may be wrong.

Reply | Quote

Great, Magic

Thanks

Reply | Quote

Hi Rory,

This doesn’t seem to work for me. How do you find the Entry ID of the message?

Error msg show : Unable to display selected folder or item…. (Don’t know how to insert the error picture here)

If I want to show msg that resides in sub folders, is it possible?

Thanks

Reply | Quote

You can select an item in the Inbox (without opening it), press Alt+f11 to open the VBEditor, then Ctrl+G to goto the Immediate window, then type:
?activeexplorer.Selection.Item(1).entryid
and it should return something like:
000000002D7C90DFCF850D41826F36B496DBBEF807006774116D4FC45B4B97DFA8C81AE83108000000F686EC00006774116D4FC45B4B97DFA8C81AE83108000001F9FF210000

If you want to do items in subfolders, just include the entire path in the link – e.g.:
Outlook:Inbox/Personal/~Message subject>

HTH

Reply | Quote

Now redundant, but see post 481,271.

Reply | Quote

No wonder I didn’t see that – you were talking to yourself!

Reply | Quote

Hey, I was asking for review, but nobody paid any attention! My best work goes unrecognized, while my mistakes are found instantly. Grumble, mumble …

Reply | Quote

Hi John

Thanks for the link. Where do I put the codes of your? I have put it in the Outlook’s ThisOutlookSession but when I try to run it, it show an error, Compile Error : User define type not defined and highlighted Dim objData As DataObject. How does the codes work.?

Thanks

Reply | Quote

Open the Visual Basic Editor
Select Tools | References…
Locate Microsoft Forms 2.0 Object Library in the list and tick its check box.
Click OK.

VBA should now recognize DataObject.

BTW you should put the code in a standard module (created by Insert | Module in the Visual Basic Editor).

Reply | Quote

Hi Hans,

Thanks for the pointer. Strange is that my References does not have this Object???
Where can I find this? Does this come together with Office 2003?

Reply | Quote

It’s a standard library, there’s an easy way to get it set: in the VBE select Insert | UserForm, and the library reference will be added automatically for you. Then right click on the blank userform you just created, and remove it. Or, the long way, select Tools | References | Browse, and browse to it at ‘c:windowssystem32FM20.dll’ (some installations use ‘winnt’ instead of ‘windows’).

Reply | Quote

Hi John,

Thanks for the pointer. I found it by the 2nd method and am puzzle as to the short cut method don’t work.
Can you tell me how to identify which dll is for what references.

BTW, the method from Rory on the sub folder works only sometimes.

Reply | Quote

Francis, I don’t see where Rory showed the Method to get the folder path. To get the exact folder path, for items use:

ActiveInspector.CurrentItem.Parent.FolderPath

and for folders use:

ActiveExplorer.CurrentFolder.FolderPath

These can be used in the Intermediates window (preceded by ‘?’), and you can see how they are used as alternatives in the code in post 481271.

Not sure where to find a master list of dlls used in reference libraries, I usually find them by Googling as needed. I’m also not sure why yours aren’t readily available, but you might try going through your Office installation via the Control Panel | Add or Remove Programs | Microsoft Office | Change | Add or Remove features, and making sure you have added all the VB related setup options.

Reply | Quote

Note that links to items in your personal PST will not be available to other users. For this reason it may be better to save the Outlook message to disk, then embed it into the spreadsheet as an object. (In cases where you are responding to some management request, and want to be able to show all users that you did something because the CFO told you to! )

Reply | Quote