Vlookup to different workbook

ScottyWm

Board Regular
Joined
Jan 10, 2004
Messages
105
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

farnuckl

Board Regular
Joined
Dec 16, 2003
Messages
127
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

Board Regular
Joined
Jan 10, 2004
Messages
105
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

Board Regular
Joined
Jan 10, 2004
Messages
105
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

Board Regular
Joined
Dec 16, 2003
Messages
127

ADVERTISEMENT

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

Board Regular
Joined
Jan 10, 2004
Messages
105
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Hello,

did you consider this syntax ?
Code:
Dist = WorksheetFunction.VLookup("abc", Workbooks("Scar Form").Sheets("Supplier Information").Range("a9:z1000"), 2, 0)

kind regards,
Erik
 

ScottyWm

Board Regular
Joined
Jan 10, 2004
Messages
105
erik.van.geit said:
Hello,

did you consider this syntax ?
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Jan 10, 2004
Messages
105
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,118
Messages
5,570,282
Members
412,316
Latest member
JabirS
Top