DLookup confusion 1

[netrusher]

Below is the code I have that is working well. I just need
to add something to it. As you can see below the
toEmail = Me.RequestedBy.Value & "@nmhg.com" is
taken from the Me.RequestedBy Field. There is a name
like Allen Jackson in that field. I need to somehow
incorporate a Dlookup to come up with the appropriate Login
that goes with that name. In this case it would be abajacks

So I really want the Value of the Me.RequestedBy Field
then lookup the Login that goes with the name in this field
and for the Login to be part of toEmail code.

Below is Table and Fields the Dlookup would use but I do
not know how to make it part of the toEmail part of
the Code. Please help!

DLookup("Login", "ChangeFormUserNameTbl", "Name='" & GetCurrentUserName() & "'")

       DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

       Dim clsSendObject As accSendObject
       Dim strMsg As String
       Dim emailsubject As String
       Dim emailtext As String
       Dim SL As String, DL As String
       Dim toEmail As String
      
       SL = vbNewLine
       DL = SL & SL

       emailsubject = "Form #" & Me.FormId.Value & "; Scheduling, Please complete Checklist and Forward to Engineering"

       emailtext = "Form #" & Me.FormId.Value & DL & _
       "Comments: " & Me.BomCheckList2Comments.Value & DL & _
       "1. Please go to appropriate Form (FormPurchasedToMakeFrm) and then to the Form Number Listed above" & SL & _
       "2. Complete your Checklist" & SL & _
       "3. Next, Click Scheduling CheckBox in Routing above to Forward Form to Engineering" & DL & _
       "Thank you for your help" & DL & _
       DLookup("Name", "ChangeFormUserNameTbl", "Login='" & GetCurrentUserName() & "'") & SL & _
       "ECN Analyst"
 
       toEmail = Me.RequestedBy.Value & "@nmhg.com"

       Set clsSendObject = New accSendObject
       strMsg = String(3000, "a")
       clsSendObject.SendObject , , accOutputrtf, _
       toEmail, , "abajacks@nmhg.com", emailsubject, emailtext, True
       Set clsSendObject = Nothing
 
 End Sub

 

[Remou]

Do you mean that you want:

[tt]toEmail=DLookup("Login", "ChangeFormUserNameTbl", "Name='" & GetCurrentUserName() & "'") & "@nmhg.com"[/tt]

As an aside, it is never a good idea to call a field 'Name'.

 

[netrusher]

Thanks I will change the field name. Also, how does
the code you sent go to the Me.RequestedBy Field
to get the name to look up?

 

[Remou]

It doesn't. I am not sure what you want.

Is it:

[tt]toEmail=DLookup("Login", "ChangeFormUserNameTbl", "Name='" & Me.RequestedBy & "'") & "@nmhg.com"[/tt]

 

[netrusher]

Remou,

Once again! Thanks for the help! That is exactly what I
want.