Reference another field using VBA

[surkdidat]

I am trying to develop something in Access when you press a button, it brings up a msgbox (fine) and you enter a reference number.
This then launches Outlook, and in the subject field enters the number you just entered into the msgbox (again fine)

What I then need is to reference a field in that sheet to be displayed alongside the reference number.

So, for example I enter reference "1432". The Field I want is called Description (in a Table/Form called "Sheet5". It is the 3rd field in the design aspect (data imported from Excel) - how do I reference this in the VBA Code please?

Private Sub Command332_Click()
Dim oLook As Object
Dim oMail As Object
 
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
 pwd1 = InputBox("Please Enter the Reference Number")
With oMail
        
        .To = ""
        .CC = "aaa@aaa.com"
        .BCC = ""
        .Subject = pwd1
        .VotingOptions = "Yes,;No"
   .Display
End With
 
Set oMail = Nothing
Set oLook = Nothing
End Sub

 

[stumac]

If it is in the form you are clicking the button on then:

.Subject = pwd1 & " " & me.description

Should return the description value of the current record being displayed on the form.

If it is in a table then you need a way to specify which record you want to return that value for.