My VLookup is not working

E

Eric Penfold

Active Member
Joined
Nov 19, 2021
Messages
424
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
For some reason my VLookup line does not work.

VBA Code: 
For i = 1 To LRow
        strStaffEmails = Application.WorksheetFunction.VLookup(CLng(Me.Staff_Names), pws.Range("Lookup"), 2, 0)
        Next i

VBA Code: 
Private Sub POEmailStaff()

    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim pwb    As Workbook
    Dim pws    As Worksheet
    Dim LRow   As Long
    Dim Rng    As Range
    Dim strTable As String
    Dim strStaffEmails As String
    Dim FormatRuleInput As String
    Dim Result As String
    Dim i      As Integer
    Dim intNumRows As Integer
    Dim intRow As Integer
    Dim EmailApp As Object
    Dim EmailItem As Object
    Dim xMailbody As Object

    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Sheet")
    Set pwb = Workbooks("MyPersonal.xlsb")
    Set pws = pwb.Sheets("Contacts")
    LRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    On Error Resume Next
    Set Rng = Application.InputBox( _
         Title:="EmailRange", _
         Prompt:="Select Range to Email", _
         Type:=8)
    If Rng Is Nothing Then Exit Sub
    On Error GoTo 0
    
    On Error Resume Next
    intNumRows = Rng.Rows.Count
    
    strTable = "<table border=1>"
    strTable = strTable & "<tr>"
    strTable = strTable & "<th>Entered By</th><th>Supplier Code</th><th>Supplier Name</th><th>Branch</th><th>Due Date</th><th>Notes</th><th>Net Amount</th></tr>"
    
    For intRow = 1 To intNumRows
        strTable = strTable & "<tr>"
        strTable = strTable & "<td>" & Rng(intRow, 1).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 2).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 3).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 4).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 5).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 6).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 7).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 8).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 9).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 10).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 11).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 12).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 13).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 14).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 15).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 16).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 17).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 18).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 19).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 20).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 21).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 22).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 23).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 24).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 25).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 26).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 27).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 28).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 29).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 30).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 31).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 32).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 33).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 34).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 35).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 36).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 37).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 38).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 39).Value & "</td>"
        strTable = strTable & "<td>" & Rng(intRow, 40).Value & "</td>"
        strTable = strTable & "<tr>"
    Next
    
    strTable = strTable & "</table>"
    
    For i = 1 To LRow
        strStaffEmails = Application.WorksheetFunction.VLookup(CLng(Me.Staff_Names), pws.Range("Lookup"), 2, 0)
        Next i
    
    Select Case Time
        Case Is < TimeValue("12:00:00")
            xMailbody = "Good Morning"
        Case Is < TimeValue("17:00:00")
            xMailbody = "Good Afternoon"
    End Select
    
    With EmailItem
        
        Result = strStaffEmails
        
        Select Case Result
            
            Case Is = "NJ"
                .To = strStaffEmails
                .BCC = "Purchase@drainfast.co.uk"
                .Subject = "OverHeadsReport"
                .HTMLBody = xMailbody & "," & " <p> Can you confirm these in if we have received it?" _
                          & "<br><br> Or move the due date if not accurate</p> " & "<p>" & strTable & "</p>" & _
                            "<br><br>Kind Regards,"
                EmailItem.Display
                Exit Sub
            Case Is = "TJ"
                .To = strStaffEmails
                .BCC = ""
                .Subject = "Purchase@drainfast.co.uk"
                .HTMLBody = xMailbody & "," & " <p> Can you have a look and update/supply </p> " & "<p>" & strTable & "</p>" & _
                            "<br><br>Kind Regards,"
                EmailItem.Display
                Exit Sub
            Case Is = "TB"
                .To = strStaffEmails
                .BCC = ""
                .Subject = "Purchase@drainfast.co.uk"
                .HTMLBody = xMailbody & "," & " <p> Can you have a look at these Pos and update the due date/supply as needed. Thank you </p> " & "<p>" & strTable & "</p>" & _
                            "<br><br>Kind Regards,"
                EmailItem.Display
                Exit Sub
            Case Is = "PMc"
                .To = strStaffEmails
                .BCC = ""
                .Subject = "Purchase@drainfast.co.uk"
                .HTMLBody = xMailbody & "," & " <p> Can you have a look at these POs and update/supply if necessary. thanks</p> " & "<p>" & strTable & "</p>" & _
                            "<br><br>Kind Regards,"
                EmailItem.Display
                Exit Sub
            Case Is = "TB"
                .To = strStaffEmails
                .BCC = ""
                .Subject = "Purchase@drainfast.co.uk"
                .HTMLBody = xMailbody & "," & " <p> Can you have a look at these Pos and update the due date/supply as needed. Thank you</p> " & "<p>" & strTable & "</p>" & _
                            "<br><br>Kind Regards,"
                EmailItem.Display
                Exit Sub
            Case Is = "LJK"
                .To = strStaffEmails
                .BCC = ""
                .Subject = "Purchase@drainfast.co.uk"
                .HTMLBody = xMailbody & "," & " <p> Can you have a look at these Pos and update the due date/supply as needed. Thank you</p> " & "<p>" & strTable & "</p>" & _
                            "<br><br>Kind Regards,"
                EmailItem.Display
                Exit Sub
            Case Is = "JH"
                .To = strStaffEmails
                .BCC = ""
                .Subject = "Purchase@drainfast.co.uk"
                .HTMLBody = xMailbody & "," & " <p> Can you have a look and update/supply these Pos</p> " & "<p>" & strTable & "</p>" & _
                            "<br><br>Kind Regards,"
                EmailItem.Display
                Exit Sub
        End Select
        
    End With
    
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Trying to loop through the range to use vlookup email address`s in pws worksheet using the value of the listbox.
 
Upvote 0
Does Staff_Names contain a numeric value?

Just wondered why it was wrapped in CLng
 
Upvote 0
So this code is from a Userform module
Maybe Staff_Names is the value from a textbox; do you need to convert it to a Long? Ie: your vlookup colun in pws.Range("Lookup") is a Number?

But again: what is the purpose of the Loop? It repeat X times the same VLookup, getting therefore the same (the same) result everytime you execute the vlookup.

When your needs will be clear we shall discuss about using Application.VLookup (and manage the possible Error returned by a failing lookup) vs Application.WorksheetFunction.VLookup (that rise a Runtime error if the lookup fails)
 
Upvote 0
Don`t worry now works forgot to change the work sheet name to the new one??
 
Last edited:
Upvote 0
You mean that the line Application.WorksheetFunction.VLookup is executed without any run time error but the variable strStaffEmails shows "", whereas column B in "pws" is not empty?

Edit: I see that you use On Error Resume next; remove that line so that you can see all the errors the code generates
My guess is that you will receive a run time error on the vlookup, to mean that the string is not found in the lookup table; but we cannot go on with best guesses
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
My Table created by VBA
2 3
Replies
24
Views
717
Candyman8019
C
E
  • Solved
Change the font on Emails
2
Replies
11
Views
546
Eric Penfold
E
E
  • Question
This code is to put a excel range onto a email
Replies
3
Views
414
I_know_nuffin
I
E
  • Question
If Active cell = ASKWH and cell same row but offset 2 columns = Depots listed
Replies
0
Views
228
Eric Penfold
E
L
  • Question
Combining two codes by Using a Messagebox like Form with yes/no or alike
Replies
1
Views
120
lojanica
L

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top