Add email VBA to existing code

C

Chad

Hello, I am running this code in my WelcomeForm On Open event and when a
person is denyed access to the database and before OR after the database
closes I would like a outlook email window to come up so they can email me.
what and where would I add the code to the code below? Thanks!

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" & Me.Text_Network_User & "'" &
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " & Chr(13) & _
"Please contact the Database Adminstrator for assistance.",
vbOKOnly + vbCritical, "Logon Denied"
DoCmd.Quit

Else

stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End If

End Sub
 
A

Arvin Meyer [MVP]

Chad said:
Hello, I am running this code in my WelcomeForm On Open event and when a
person is denyed access to the database and before OR after the database
closes I would like a outlook email window to come up so they can email
me.
what and where would I add the code to the code below? Thanks!

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String
Click to expand...

' add this
Dim strTo As String
Dim strSubject As String
Dim strMessage As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" & Me.Text_Network_User & "'"
&
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " & Chr(13)
& _
"Please contact the Database Adminstrator for assistance.",
vbOKOnly + vbCritical, "Logon Denied"
Click to expand...

'add the code here:

strTo= "(e-mail address removed)"
strSubject = "The subject you want"
str Message = "The body of the message"

DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject, strMessage
 
C

Chad

Thanks for the reply! I get a compile error Variable not Defined and it
highlights strTo. Here is the complete code im using if that helps. Maybe I
put it in the wrong place..Thanks!

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" & Me.Text_Network_User & "'" &
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " & Chr(13) & _
"Please contact the Database Adminstrator for assistance.",
vbOKOnly + vbCritical, "Logon Denied"

strTo = "(e-mail address removed)"
strSubject = "Cant open database"
Str Message = "The body of the message"

DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject,
strMessage
DoCmd.Quit

Else

stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End If

End Sub
 
D

Douglas J. Steele

It looks as though you missed Arvin's instruction

' add this
Dim strTo As String
Dim strSubject As String
Dim strMessage As String


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
C

Chad

Oh! I didnt see that... Well it works great except for it I deside not to
send the email and close the email box I get an error and cant close the
database

error:
The SendObject action was canceled.
You used a method of the DoCmd object to carry out an action in VB, but hen
clicked cancel in a dialog box.
For example, you used the Close method to close a changed form, then clicked
Cancel in the dialog box that asks if you want to save the changes you made
to the form.
 
D

Douglas J. Steele

You'll have to determine the error number, and put in error handling that
ignores that specific error.
 
D

Douglas J. Steele

Start by adding standard error handling.

At the top of the module (right after the "Private Sub Form_Open()"), put

On Error GoTo Err_Form_Open

At the end of the module (right before the "End Sub"), put

End_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Open

Now when you run the code, you'll get a message box with a number, followed
by a colon, followed by your error message. Once you know the error number,
change the last bit of code to

End_Form_Open:
Exit Sub

Err_Form_Open:
Select Case Err.Number
Case xxx ' Whatever error number you got above
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Open
End Select
 
C

Chad

This is weird? I am now working on it at home on Vista and it wors great but
my PC at work is using XP and thats the one I get the error on. It says im
still running or have open a DAO and simething else and thats why it wouldnt
close..Any ideas?
Thanks
 
C

Chad

I will have to try tomorrow morning when im on the PC running XP... I will
get back to you in the morning. Thnaks!
 
C

Chad

Ok I used the error handler and got a number 2501 so I added it in the error
handler. I opened the database and got the message “Can’t enter blab bla blaâ€
and an email opened up “As expected†I didn’t send the email but instead I
close it and I get these errors:

SendObject action was canceled.
You used a method of the DoCmd object to carry out an action in VB, but then
clicked cancel in a dialog box.

I then click OK in the popup and another error comes up.

You cant exit “Database Name†now.
If your running a VB code module that is using OLE or DDE you may need to
interrupt the module.

I click OK then the database opens “Which its not supposed toâ€. I try to
close it and the last error message pops up. I have to end task to get out of
the database. What can I do to fix this problem? Here is the code I’m using.
Thanks!

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String
Dim strTo As String
Dim strSubject As String


Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" & Me.Text_Network_User & "'" &
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " & Chr(13) & _
"Please contact the Database Adminstrator for assistance.",
vbOKOnly + vbCritical, "Logon Denied"

strTo = "(e-mail address removed)"
strSubject = "Access Database Denied"
DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject


DoCmd.Quit

Else

stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End If

End_Form_Open:
Exit Sub

Err_Form_Open:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Open
End Select

End Sub
 
D

Douglas J. Steele

I hadn't notice the DoCmd.Quit there before. Try replacing that with

Cancel = True
 
C

Chad

It didnt work. I get the same errors but the database wont open. Thats how
its suposed to work. before I added the Cancel =True it opened the
databese... Any other sugestions?
--
Newbies need extra loven.........


Douglas J. Steele said:
I hadn't notice the DoCmd.Quit there before. Try replacing that with

Cancel = True
Click to expand...
 
D

Douglas J. Steele

What do you mean by "it opened the database"?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chad said:
It didnt work. I get the same errors but the database wont open. Thats how
its suposed to work. before I added the Cancel =True it opened the
databese... Any other sugestions?
Click to expand...
 
C

Chad

When I had the DoCmd it opened the database after the error prompts. Then it
wouldnt let me close the database it kept saying:
You cant exit “Database Name†now.
If your running a VB code module that is using OLE or DDE you may need to
interrupt the module.
Now that you had me take the DoCmd out and add Current = True I still get
the same errors and cant close the database but the database never opens my
start up "WelcomeForm" I still have to Ctl+Alt+Delete to end task to get out.
 
C

Chad

Douglas, Is there a way I could just not have a close button on the email
prompt that comes up? This way they are forced to send the email... Also is
there a way I can add who tried to open the database by getting thier Login
name from the text box Text_Network_User. Thanks for all your help on this!
 
C

Chad

How could I log who was denied access to the database? The only way it shows
who is logging in is by using the Text_Network_User text box.
 
D

Douglas J. Steele

You'll only sending the e-mail if something was put in text box and whatever
is in the box isn't in the tbl_LoginID. Isn't what's in Text_Network_User
what you'd log?

Incidentally, I don't know whether you're making them key their info into
Text_Network_User or if you're prefilling that control. If you aren't
prefilling the control for them, take a look at
http://www.mvps.org/access/api/api0008.htm at "The Access Web" for code to
retrieve the current user's Network ID.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Export to PDF 0
Working outlook via vba code with xlsx - error 0
Permission to open database 6
VBA works in orignal but fails in copy of database 1
Password required button 5
From one form, open another form, find record (no filter) 5
A2000 form ceases working under A2002 1
Adding table into existing access database via ADO 0

Top