Vlookup to different workbook

[ScottyWm]

I'm trying to send an email as part of a macro using an email address in a different workbook (that is also open). VLookup is using a Public variable (supplier) to look at the next column for the email address. I've tried lots of different syntaxes, searched this forum for hours, but can't get it to work! I can't figure out how to get it to address the other workbook. What am I doing wrong?

Public Sub SendEmail()

Dim OutApp As Object
Dim OutMail As Object
Dim Dist As String

Dist = worksheetfunction.vlookup(Supplier,[Scar Form.xls]("Supplier Information")!range(a9:z1000), 2)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = Dist
.CC = ""
.BCC = ""
.Subject = "SCAR #" & (Worksheets("Scar").Cells(8, 29))
.Body = "Please review the attached Supplier Corrective Action Request (SCAR) and respond in a timely manner." & _
vbCrLf & vbCrLf & "" & _
vbCrLf & "Best regards,"

.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing

End Sub

 

[erik.van.geit]

'[Scar Form.xls]Supplier Information'!$A9:$Z1000

this is not a "code-like" syntax but a "sheet-like" syntax
you will use this when you insert formulas in cells or with EVALUATE (see helpfiles)