Hello. I use the variable strbody in the body of an e-mail. The variable
collects that data from the worksheet nicely. I was hoping to add a vlookup
formula to it.

So for example, the variable picks up the PO numbers in a given row. On the
same sheet (but in a hidden range) I have a table with all PO numbers and
their description. I would love the variable to concatenate the PO number
with the PO description, and set THAT as the variable.

Thank you for your help!

Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
strbody = strbody & cell.Value & vbNewLine
Next

Guessing that Cell contains the PO Numbers
Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
desc = application.Vlookup(cell.Value,Range("A1:C100"),2, 0)
if iserror(desc) then
sStr1 = ""
else
sStr1 = ", " & desc
end if
strbody = strbody & cell.Value & sStr1 & vbNewLine
Next


--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hello. I use the variable strbody in the body of an e-mail. The variable
collects that data from the worksheet nicely. I was hoping to add a
vlookup
formula to it.

So for example, the variable picks up the PO numbers in a given row. On
the
same sheet (but in a hidden range) I have a table with all PO numbers and
their description. I would love the variable to concatenate the PO number
with the PO description, and set THAT as the variable.

Thank you for your help!

Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
strbody = strbody & cell.Value & vbNewLine
Next

Something like

Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
PODesc=Application.VLOOKUP(cell.Value,Range("PODes cs"),2,False)
strbody = strbody & cell.Value & " " & PODesc & vbNewLine
Next

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Hello. I use the variable strbody in the body of an e-mail. The variable
collects that data from the worksheet nicely. I was hoping to add a
vlookup
formula to it.

So for example, the variable picks up the PO numbers in a given row. On
the
same sheet (but in a hidden range) I have a table with all PO numbers and
their description. I would love the variable to concatenate the PO number
with the PO description, and set THAT as the variable.

Thank you for your help!

Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
strbody = strbody & cell.Value & vbNewLine
Next

Thanks Tom!!

"Tom Ogilvy" wrote in message
...
Guessing that Cell contains the PO Numbers
Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
desc = application.Vlookup(cell.Value,Range("A1:C100"),2, 0)
if iserror(desc) then
sStr1 = ""
else
sStr1 = ", " & desc
end if
strbody = strbody & cell.Value & sStr1 & vbNewLine
Next


--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hello. I use the variable strbody in the body of an e-mail. The
variable
collects that data from the worksheet nicely. I was hoping to add a
vlookup
formula to it.

So for example, the variable picks up the PO numbers in a given row. On
the
same sheet (but in a hidden range) I have a table with all PO numbers and
their description. I would love the variable to concatenate the PO
number
with the PO description, and set THAT as the variable.

Thank you for your help!

Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
strbody = strbody & cell.Value & vbNewLine
Next