In a program for my company, I have a built-in function that will send an email to the administrator if a user cannot log in. This is fine except I would like to have a form where the administrator can change the To: address.

The problems come in when I have one form to change the address, but on the log-in form, i try to call that variable. My code looks like this...

Private Sub MailAdmin_Click()
MsgBox "An email has been sent to the administrator. You will be contacted via e-mail when you have been granted access. Please do not click on this button again."

On Error GoTo HandleErrors

Dim objOL As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim objMI As Outlook.MailItem
Dim eml1 as String

eml1 = [tblEmails]![AdministratorEmail1]
Set objOL = New Outlook.Application
Set objNS = objOL.GetNamespace("MAPI")

' If using email server, allow user to logon
' (Ignored if already logged on or if not
' using server):
objNS.Logon , , True, False

Set objMI = objOL.CreateItem(olMailItem)
With objMI
.To = eml1
.subject = "Needs Access"
.Body = "Please grant access to the Project Inventory Database to User " & fOSUserName & Date
.Send
End With

Bye:
Set objMI = Nothing
Set objNS = Nothing
Set objOL = Nothing
Exit Sub

HandleErrors:
MsgBox Err.Description, vbOKOnly, "Error No: " & CStr(Err.Number)
Resume Bye

End Sub



Can someone tell me what is wrong with my code?

You will have to make your variable global if you want to use it through out the database.

In a new module place this:

Global eml1 As String

and remove this line from your current code:

Dim eml1 as String

I think your problem is this:
eml1 = [tblEmails]![AdministratorEmail1]

You may want to use something like:

eml1 = DLookup("[AdministratorEmail1]","tblEmails")

if the table has only one record.

Thank you that worked like a charm!