CODEWITHSUNDEEP
excelvbaemailhyperlinkoutlook
Grymjack
Grymjack

Reputation: 539

Insert a link to an Outlook email within Excel

I am trying to create a hyperlink in an Excel cell to an existing Outlook email.

The user should be able to click on the link in the cell (could be a button with VBA code as well) to open the email.

I know that pst files have to be opened within Outlook(?), so let's assume Outlook is running with the relevant pst file already open.

I found nothing to provide a clickable way to open email.

Upvotes: 0

Views: 5577

Answers (2)

Ravi
Ravi

Reputation: 84

You need the Outlook:// protocol enabled. Microsoft provides default support for this protocol in Outlook application only now (See https://support.microsoft.com/en-us/help/929590/known-issues-when-you-develop-custom-solutions-for-office-outlook-2007)

However, you can do this manually for other apps in your computer. You need to edit the windows registry by adding the following entries to the registry:

[HKEY_CLASSES_ROOT\outlook]
"URL Protocol"=""
@="URL:Outlook Folders"

[HKEY_CLASSES_ROOT\outlook\DefaultIcon]
@="C:\\Program Files\\Microsoft Office\\Office15\\1033\\OUTLLIB.DLL,-9403"

[HKEY_CLASSES_ROOT\outlook\shell]
@="open"

[HKEY_CLASSES_ROOT\outlook\shell\open]
@=""

[HKEY_CLASSES_ROOT\outlook\shell\open\command]
@="\"C:\\Program Files\\Microsoft Office\\Office15\\OUTLOOK.EXE\" /select \"%1\""

One you have done this, you need to get the message ID for the individual messages.

the following code will get the message ID

Sub GetOutlookMessageLinkID()
    'This procedue returns the outlook message ID for a the currenlty open outlook message.
    'Caveat: this message ID will be invalid if the message is moved to a differnt forldder.

    Dim myolApp
    Dim linkToMsg As String

    Set myolApp = CreateObject("Outlook.Application")
    linkToMsg = "Outlook:" & myolApp.ActiveInspector.CurrentItem.EntryID
    'linkToMsg now has the hyper link. you can use this as a clickable link to access the message
    'Enable the "Outlook:" protocol on your machine


End Sub

Upvotes: 0

Tehscript
Tehscript

Reputation: 2556

I'm not quite sure I understand the whole thing but you can try the following:

Sub OpenMessage()
    Dim wb As Workbook, ws As Worksheet
    Dim mailOL As Outlook.Application, mailItems As Outlook.Items
    Dim mailFolder As Outlook.MAPIFolder, mail As Object

    Set wb = ActiveWorkbook
    Set ws = Sheets("Sheet1")

    Set mailOL = Outlook.Application
    Set mailFolder = mailOL.ActiveExplorer.CurrentFolder
    Set mailItems = mailFolder.Items

    For Each mail In mailItems 'search Cell A1 value among email subjects
         If InStr(mail.Subject, ws.Range("A1").Value) > 0 Then
            mail.Display 'if found display the email message
        End If
    Next

    Set wb = Nothing: Set ws = Nothing
    Set mail = Nothing: Set mailItems = Nothing
    Set mailFolder = Nothing: Set mailOL = Nothing
End Sub

As you stated, Outlook should be open to run this. You can set this macro to a button and have your key word (for example PO number) in Cell A1 to search among your inbox. You can improve this code to serve you dynamically. Let me know if I understand it correctly.

Upvotes: 1

Related Questions