Macro in Outlook to look up data in Closed Excel File
I'd like to make a macro that essentially does a VLookUp from Outlook into an Excel file. Instead of actually using the VLOOKUP function I just searched for the data in one column, and then got the data from the same row one column over.
I'm not sure if this works or not, yet, because I don't know how to get Outlook to access the Excel file itself. Any help?
Code:
Sub LookUpIndexNumber()
Dim objExcel As Excel.Application
Dim exWb As Excel.Workbook
Dim ExcelFileName As String
Dim ColumnA As String
Dim ColumnB As String
ExcelFileName = ("U:\Project Support\Local Database\TortMattersLitigation.xlsx")
Set exWb = objExcel.Workbooks.Add(ExcelFileName)
ColumnA = InputBox("Enter LM#")
ColumnB = exWb.Worksheets("Sheet1").Range("A:A").Find(ColumnA).Offset(0, 1).Value
MsgBox = ColumnB
End Sub