CODEWITHSUNDEEP
excelvbaoutlook
Mal101
Mal101

Reputation: 1

Link currently selected email to selected cell of the active Excel sheet

I want to link the currently selected email to the selected cell of the active Excel sheet.

Error displayed:

Object doesn't support this property or method

pointing to:

rngSelection.Value = olItem.GetInspector.URL

Outlook code:

Sub CopyLinkToSelectedEmail()

Declare all variables
Dim olItem As Object
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim rngSelection As Excel.Range

' Get the currently selected item(email) in Outlook
Set olItem = Outlook.Application.ActiveExplorer.Selection(1)

' Check the type of the selected item
Select Case TypeName(olItem)
    Case "MailItem"
        ' Get the active Excel workbook and worksheet
        Set xlApp = GetObject(, "Excel.Application")
        Set xlWorkbook = xlApp.ActiveWorkbook
        Set xlWorksheet = xlWorkbook.ActiveSheet

        ' Get the currently selected cell in the excel worksheet
        Set rngSelection = xlApp.Selection

        ' Check the type of the selected cell
        If TypeName(rngSelection) = "Range" Then
            ' Copy the link to the email to the selected worksheet cell
            rngSelection.Value = olItem.GetInspector.URL

        Else
            ' Display a message if the selected cell is not a valid range
           MsgBox "A valid cell must be selected in the Excel worksheet to run this code.", vbOKOnly + vbExclamation
        End If

    Case Else
        ' Display a message and exit the macro if the selected item is not a mail item
        MsgBox "An email mail item must be selected to run this macro.", vbOKOnly + vbExclamation
        Exit Sub

End Select

End Sub

Upvotes: 0

Views: 51

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

In the following line of code:

rngSelection.Value = olItem.GetInspector.URL

The Inspector class doesn't provide the URL property. If you need to uniquely identify items in Outlook you may get the Outlook item instance by using the Inspector.CurrentItem property which returns an object representing the current item being displayed in the inspector.

Dim myItem As Object 
Set myItem = Application.ActiveInspector.CurrentItem

Then you can use properties and methods to identify items. From the first sight, you may consider using the EntryID property if you deal with a single account and only on your machine. But a better approach is to introduce your own ID which can be stored in a user property, see PropertyAccessor which allows getting and setting item-level properties that are not explicitly exposed in the Outlook object model

Upvotes: 0

Related Questions