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

 

[farnuckl]

Hello, have you stepped through the code to see where the problem is? Most instances of the worksheetfunction object I've seen have Application. in front...

-farnuckl

 

[ScottyWm]

I tried that, but still get a syntax error in this part:
'[Scar Form.xls]Supplier Information'!$A:$Z,2



Dist = Application.worksheetfunction.VLOOKUP(Supplier,'[Scar Form.xls]Supplier Information'!$A:$Z,2,FALSE)

 

[ScottyWm]

It's driving me crazy!

I can't get VB to accept any syntax I've tried. Among many others, I've tried these:

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

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

Dist = VLookup(Supplier, '[Scar Form]Supplier Information'!,a9:z65536, 2,)

Dist = application.worksheetfunction.VLookup(Supplier, '[Scar Form]Supplier Information'!,a9:z65536, 2,)

Dist = application.worksheetfunction.VLOOKUP(Supplier,A9:Z1000,2,0)
^^ You'd think this ^ would work, but it highlights the : and says "expected list separator or )".

Please help before I smash my keyboard!!

 

[farnuckl]

Hmm, I'm afraid I've never used the worksheetfunction object...

I would try putting the VLOOKUP function in a worksheet and make sure it works, try to isolate where the problem is...

If nothing else, you could do a loop through the range, and use offset to find your email address instead of the worksheetfunction method. Kind of a code way of doing the vlookup.

-farnuckl

 

[ScottyWm]

Okay, got this to work... It isn't as pretty as I think it should be, but it works:

'First have to window over to the other file where the data is
Windows("SCAR Form.xls").Activate
Sheets("Supplier Information").Select
' then set up a range where the VLookup will look
Dim MailRange As Range
Set MailRange = Worksheets("Supplier Information").Range("a9:z1000")
' then use that named range in the VLookup
Dist = Application.WorksheetFunction.VLookup(Supplier, MailRange, 3)
'then window back to the first file (to be attached to the email)
Windows(Fname & ".xls").Activate
' then continue with the rest of the code to send the email...

Would be happy to improve on this if anyone has any suggestions.

 

[erik.van.geit]

Hello,

did you consider this syntax ?

Dist = WorksheetFunction.VLookup("abc", Workbooks("Scar Form").Sheets("Supplier Information").Range("a9:z1000"), 2, 0)

kind regards,
Erik

 

[ScottyWm]

Hello,

did you consider this syntax ?

Dist = WorksheetFunction.VLookup("abc", Workbooks("Scar Form").Sheets("Supplier Information").Range("a9:z1000"), 2, 0)

kind regards,
Erik

Erik,
That is exactly what I was looking for, thanks!! I have trouble figuring out when you have to use 'workbooks','sheets','range' and when you don't... Is there a good resource for that, I don't find the VB help files to be very good with this either.

I did have to add the .xls extension to the workbook name to avoid a runtime error. Thanks for the help guys!!

 

[erik.van.geit]

I have trouble figuring out when you have to use 'workbooks','sheets','range' and when you don't... Is there a good resource for that, I don't find the VB help files to be very good with this either.

how could your machine know wether you're talking about a sheet or a workbook ?
you always need the syntax workbooks(....) or sheets(....)
for "range" there are some abbreviations like [A1] or there is another syntax: Cells(rownumber, columnnumber) (columnlabel is accepted too: Cells(1, "A"))
resources: I've only got the helpfiles and this board (and some other sites) and a good friend which helps me quite well when searching for syntax:
the macro recorder

I did have to add the .xls extension to the workbook name to avoid a runtime error.

depends on whether it has been saved or not

best regards,
Erik

 

[ScottyWm]

Well, I mean sometimes you can use this:
'[Scar Form.xls]Supplier Information'!$A9:$Z1000

and sometimes you have to use:
Workbooks("Scar Form").Sheets("Supplier Information").Range("a9:z1000")

I do frequently use the macro recorder to see the syntax, but it isn't always the same once you modify it to put into the 'real' macro you're working on. But I'm learning more every day. And you guys on this board ROCK!! I wish we had one of you working in our company.