Macro in Outlook to look up data in Closed Excel File

[SamT]

Something like this?

Option Explicit

Public Function FindLM() As String
Dim objExcel As Object ' Excel.Application
Dim exWb As Object     ' Excel.Workbook
Dim ExcelFileName As String
Dim LM As String
Dim Result As String

    ExcelFileName = "U:\Project Support\Local Database\TortMattersLitigation.xlsx"
    LM = InputBox("Enter LM#")
    
    On Error Resume Next
     Set objExcel = GetObject(, "excel.application")
    On Error GoTo 0
    If objExcel Is Nothing Then Set objExcel = New Excel.Application
    
    Set exWb = objExcel.Workbooks.Open(ExcelFileName)
    
    Result = exWb.Worksheets("Sheet1").Range("A:A").Find(LM).Offset(, 1).Value
    MsgBox = Result
    
FindLM = Result
End Function

Private Sub Test_FindLM()
Dim X
X = FindLM
End Sub

OR, Something like this:

Option Explicit

Public Function FindLM(Optional LM As String) As String
   If LM = "" Then 
      MsgBox "Must use proper LM Number"
      Exit Function
   End If

Dim objExcel As Object ' Excel.Application
Dim exWb As Object     ' Excel.Workbook
Dim ExcelFileName As String
Dim Result As String

    ExcelFileName = "U:\Project Support\Local Database\TortMattersLitigation.objExcelsx"
    
    On Error Resume Next
     Set objExcel = GetObject(, "excel.application")
    On Error GoTo 0
    If objExcel Is Nothing Then Set objExcel = New Excel.Application
    
    Set exWb = objExcel.Workbooks.Open(ExcelFileName)
    
    Result = exWb.Worksheets("Sheet1").Range("A:A").Find(LM).Offset(0, 1).Value
    MsgBox = Result
    
FindLM = Result
End Function

Private Function WhichLM() As String
Dim LM As String
SartOver:
    LM = InputBox("Enter LM#")
    
    If Len(LM) <> nn Then GotTo StartOver
    If CLng(LM) < nnnn Or CLng(LM) > nnnn Then GoTo StartOver
    'More Tests As desired
    
'If all tests pass then...
WhichLM = LM
End Function

Private Sub Test_WhichLM()
Dim X
X = WhichLM
End Sub
Private Sub Test_FindLM()
Dim X
X = FindLM(WhichLM)
End Sub