Find two cells values in hidden sheet and store as string

Question

My idea is to make a (Forget Password) form that will restore the (Username) & (Password) and send it via Email Address provided by the user. In the following picture the customer will enter his Email address so the procedure code will find an exact match of the Email Address in hidden sheet table, if there is a match the next two cells will be stored as a string.

As you can see down below this is the hidden sheet with a table that contains information about registered customers, so when we get Email matching the (Username) and (Password) will be stored as string ((!!without activating or seeing this sheet!!))

This is my current code:

Public Function send_email()
Dim NUser As String
Dim NPass As String
Dim info As Variant
Set cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = "587"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "dash32762@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*******"
.Update
End With

' ========(( below is the code i want to find and store user and pass ))

Set info = Worksheets("AdminPanel2").Range("I11:I80").Find( _
What:=Me.txt_pass.Value, LookIn:=xlFormulas)

If Not info Is Nothing Then

   info.Parent.Activate
        info.Offset(0, 1).Select
        NUser = ActiveCell.Text            
 MsgBox "That data was sent"

 Else

MsgBox "That data was not found."

End If

'===========(( below code i want to recall it in body of the email ))

With cdomsg
.To = info
.From = "dash32762@gmail.com"
.Subject = "Restore information"
.TextBody = Hello youur username is NUser your password is NPass
.send
End With
Set cdomsg = Nothing
End Function

This is the code I want to modify:

 ' ========(( below is the code i want to find and store user and pass ))

Set info = Worksheets("AdminPanel2").Range("I11:I80").Find( _
What:=Me.txt_pass.Value, LookIn:=xlFormulas)

If Not info Is Nothing Then

   info.Parent.Activate
        info.Offset(0, 1).Select
        NUser = ActiveCell.Text            
 MsgBox "That data was sent"

 Else

MsgBox "That data was not found."

End If

I suggest you to hide those e-mails and passwords in your screenshot, just in case. — Foxfire And Burns And Burns, Oct 29 '18 at 11:40
@FoxfireAndBurnsAndBurns Thank you for your care it's not even real passwords just random — Fadi, Oct 29 '18 at 11:46

Vityata · Accepted Answer · 2018-10-29T13:44:16.903

2

Sending email and password in one email is very bad. Keeping email and password in a hidden Excel sheet is even worse. This is really not how it is done and if this is not some school project, you may have a lot of problems at work. The best practice is not to keep a password, but to keep its hash. And not to send the old password, but make a new one.

Having said all of the above, the .TextBody should be a string with & between the variables like this:

With cdomsg
    .To = info
    .From = "dash32762@gmail.com"
    .Subject = "Restore information"
    .TextBody = "Hello your username is" & NUser & " your password is" & NPass
    .Send
End With

And concerning the part in your question:

With Worksheets("AdminPanel2").Range("I11:I80")
    Set info = .Find(What:=Me.txt_pass.Value, LookIn:=xlValues, LookAt:=xlWhole)
End With

If Not info Is Nothing Then
    NUser = info.Offset(0, 1)
    MsgBox "That data was sent for user " & info
Else
    MsgBox "That data was not found."
End If

edited Oct 29 '18 at 13:44

answered Oct 29 '18 at 11:36

Vityata

42,633
8
55
100

Yeah i know it's not save at all, it is actually a college project i want it to make it as it is, and FYI i did couple of basic security to protect my vba codes and hide the sheet tabs completely. – Fadi Oct 29 '18 at 11:43
@Fadi - your project, your rules. – Vityata Oct 29 '18 at 11:52
The table are not represent the email passwords, it's just table for my own sign up form to use my excel project – Fadi Oct 29 '18 at 11:52
Thank you so much i knew it was a simple code but could't solve it without your help :) – Fadi Oct 29 '18 at 14:58

Find two cells values in hidden sheet and store as string

1 Answers1