I got this code from another thread but am having trouble modifying it.
The code is in module1 on worksheet.xlsm but I want to look up something on
an ALREADY OPEN workbook CreateManifestEmail_V1.xlsm

I get "Subscript out of range" at <-- ERROR shown below in code.

Your help is appreciated in advance.

~~~~~~~~~~~~

Function LookupEmailNames (sServer As String) As String
' looks at CreateManifestEmail_V1.xlsm (eMail Names) for sServer in Col A
' returns TO names from Col B

Dim OFFICE As Workbook
Dim Result As String
Dim EmailRange As Variant 'it can return a blank

Set OFFICE = Workbooks("CreateManifestEmail_v1.xlsm") <- ERROR
With OFFICE.Worksheets("eMail Names")
Set EmailRange = .Range("A1:B1000")
End With

Result = Application.VLookup(sServer, EmailRange, 2, False)

If IsError(Result) Then
MsgBox "Not found"
Else
MsgBox Result, vbOKOnly
End If

End Sub

--
Thanks for your reply & assistance.
Jimbo213