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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
376
NormChart55
N
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
304
Patriot2879
P
E
  • Solved
Change the font on Emails
2
Replies
11
Views
854
Eric Penfold
E
D
  • Question Question
VBA to Create Outlook Email Not Working for All Users
Replies
9
Views
313
Logit
Logit

Forum statistics

Threads
1,223,939
Messages
6,175,531
Members
452,651
Latest member
wordsearch

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