VLookup with VB?

[jsambrook]

I want to email suppliers a spreadsheet printed to PDF. I have all email addresses in a spreadsheet and I want to be able to do a vlookup from the spreadsheet within the macro to use the email address to send. I have the majority of the code sorted, its just the looking up of the email that I'm struggling with. My supplier spread has supplier name and email address and my spread has the Supplier name in a cell on the spreadsheet.
thanks

 

[Norie]

Are you looping through the supplier list when creating the worksheets to send out?

If you are then you could grab the email when you do that and use it to send the worksheet.

 

[prajul89]

ANS = Application.WorksheetFunction.VLookup(Range("F3").Value, Range("AP:AQ"), 2, 0)

How about this

 

[jsambrook]

Prajul89

that looks like it could work, except I need to reference another worksheet, where can I put the filepath etc?

Norie

I wish I could do that, but its a short term fix using somebody elses code from a datbase extraction and they have yet to insert the email field!

 

[prajul89]

ANS = Application.WorksheetFunction.VLookup(Workbooks(YOURFILE).Sheets(YOURSHEET).Range("F3").Value, Workbooks(YOURFILE).Sheets(YOURSHEET).Range("AP:AQ"), 2, 0)

Just put your FileName and SheetName and you are good to go.

 

[prajul89]

If the reference is from the same workbook then skip the Workbook part

 

[jsambrook]

thanks, will give it a go.

 

[jsambrook]

I want to run this macro from my personal workbook. Is there any way I can insert the spreadsheet with the suppliers names into my personal workbook somewhere? There are too many to hard code. Might just be best to open the workbook to reference from it and close it again.

 

[Norie]

Can't you create a list of suppliers/emails?

You've got all the data so that shouldn't be to hard.