My VLookup is not working

E

Eric Penfold

Active Member
Joined
Nov 19, 2021
Messages
431
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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 Question
My Table created by VBA
2 3
Replies
24
Views
1K
Candyman8019
C
N
  • Question Question
VBA Code - add emails to outlook BCC from list (that updates)
Replies
1
Views
416
NormChart55
N
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
322
Patriot2879
P
E
  • Solved
Change the font on Emails
2
Replies
11
Views
859
Eric Penfold
E
J
  • Question Question
Send bulk email attachment error
Replies
3
Views
117
JOHATUP
J

Forum statistics

Threads
1,224,875
Messages
6,181,514
Members
453,050
Latest member
Obil

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