workshhet name as vlookup seeked value

[Gokuba]

HI,

I found a code online to send my tables via email ( not perfect though since the table changes sizes). My problem :
I have a seperate tabs with a specific name, now I would like to use that tabs name as a lookup value in Vllokup function.
My email code:

Sub Send_Selection_Or_ActiveSheet_with_MailEnvelope()
'Working in Excel 2002-2016
    Dim Sendrng As Range


    On Error GoTo StopMacro


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    'Note: if the selection is one cell it will send the whole worksheet
    Set Sendrng = Range("A1").CurrentRegion


    'Create the mail and send it
    With Sendrng


        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope


            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = "This is a test mail."


            With .Item
                .To = "ron@debruin.nl"
                .CC = ""
                .BCC = ""
                .Subject = "My subject"
                .Send
            End With


        End With
    End With


StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False


End Sub

and now my not working vllokup code:


Sub znajdzadres()
Dim ws As Worksheet
Dim eadr As Variant
eadr = "Application.WorksheetFunction.VLookup([ws],DOSTAWCY!R2C1:R83C3,3,0)"
cell("f1") = eadr






End Sub

Please help

 

[Ray Bailey]

Try this after your Dim statements

Dim sName As String
sName = ws.Name

Then replace [ws] with sName