Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sending Form via E-Mail (Access 97)

    Currently the below code does everyhing that is needs to:
    1) Collects data for new users
    2) Sends me an e-mail with the updated table once any user is added
    3) Send me the same table but with different e-mail name when a Exec is added, for user confirmation

    What I would like to do now is to have the form e-mail the user (all but those who register as EXEC) so that they will recieve their password quickly. (Since the EXEC password works for all forms, and functions, I would like to make sure only those who need it, will get it).

    There is the code:
    Dim DB As DAO.Database
    Dim rs As DAO.Recordset

    Set DB = CurrentDb
    Set rs = DB.OpenRecordset("tblUsers", dbOpenDynaset)
    ' TO ENSURE ALL FIELDS COMPLETED
    If IsNull(FName) Or IsNull(LName) Or (ClockNum) <= 0 Then
    MsgBox "All fields must be completed"
    LName.SetFocus

    Else

    With rs
    .AddNew
    .Fields("UserID") = Me![UserID]
    .Fields("Password") = Me![Password]
    .Fields("LastName") = Me![LastName]
    .Fields("FirstName") = Me![FirstName]
    .Fields("ClockNumber") = Me![ClockNumber]
    .Fields("adddate") = Me![adddate]
    .Fields("Plant") = Me![Plant]
    .Update


    End With


    Set rs = Nothing
    Set DB = Nothing
    'HERE IS WHERE IT NEEDS TO SEND THE USER ONLY THIER INFORMATION
    If [Plant] <> "EXEC" Then
    DoCmd.SendObject acSendTable, "tblUsers", acFormatXLS, [UserID], , , "Password", , False
    Else
    End If
    'THIS NOTIFIES ME THE DIFFERENCE BEWTEEN SIMPLY ANOTHER USER AND A EXEC USER REGISTERING
    If [Plant] = "EXEC" Then
    DoCmd.SendObject acSendTable, "tblUsers", acFormatXLS, "Paul Brown", , , "New EXEC User", , False
    Else
    DoCmd.SendObject acSendTable, "tblUsers", acFormatXLS, "Paul Brown", , , "New User", , False
    End If
    DoCmd.Close acForm, "usernewemail"
    DoCmd.OpenForm "switchboard"
    End If

    Any as the code stands right now, the [userID] is e-mailed the entire table if they are not EXEC, when they only need their information. Also, please keep in mind that there is a field on the form that has the password that is coming from DLookUp from when the user chooses their plant. However, I woul like to keep this field invisible, at least to submission, so that one user can not simply keep changing the plant and gather all the passwords. As a side note , users only see this screen until the register. Once they register, they never see this screen again.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Sending Form via E-Mail (Access 97)

    Build a query qrycurrentuser as "Select * from tblUsers where tblusers.[userid]= forms!frmNewUser![userid]"

    (change frmnewUser the the name of the form that you use)
    then change :
    DoCmd.SendObject acSendTable, "tblUsers", acFormatXLS, [UserID], , , "Password", , False

    to

    DoCmd.SendObject acSendQuery, "qrycurrentUser", acFormatXLS, [UserID], , , "Password", , False
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending Form via E-Mail (Access 97)

    Solved!

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •