• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

Access Save, Close, and Automated Email all in one button

I currently have an event procedure on the close of the form:

I would like to create one button that would save the form, close the form, and automatically send the email like I have done in the attached code. Any ideas?
Private Sub Form_Close()

Dim objOutlook As Object
Dim objMailItem As Object
Const olMailItem As Integer = 0
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(olMailItem)

Dim strPathAttach As String

On Error GoTo err_Error_handler

'set receipient, you can use a DLookup() to retrieve your associate Email address
objMailItem.To = DLookup("Email_ID", "dbo_Associates$", "[Fullname]='" & Me.cboAssociate & "'")


'set subject with text and Form values
objMailItem.Subject = "Quality Violation " & Me.LOAN_CODE

'set body content with text and Form values etc.
objMailItem.Body = Me.qv_date & vbCrLf & "some text here:" & vbCrLf & Me.Entered_Date

' display email
' objMailItem.Display

' sending mail automaticly
objMailItem.Send

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
exit_Error_handler:
 On Error Resume Next

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
 Exit Sub
 
err_Error_handler:
 Select Case Err.Number
  'trap error 287
  Case 287
   MsgBox "Canceled by user.", vbInformation
  Case Else
   MsgBox "Error " & Err.Number & " " & Err.Description
 End Select

Resume exit_Error_handler
End Sub

Open in new window

0
dplowman
Asked:
dplowman
  • 3
  • 2
1 Solution
 
mbizupCommented:
If the close event code is currently working, you could use the following command button click event code:

Private Sub MyButton_Click()

DoCmd.RunCommand accmdSaveRecord
SendEmails
Docmd.close acform, me.name

End Sub

Open in new window



And move your email code to a sub in the same form's code module:


Private Sub SendEmails()

Dim objOutlook As Object
Dim objMailItem As Object
Const olMailItem As Integer = 0
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(olMailItem)

Dim strPathAttach As String

On Error GoTo err_Error_handler

'set receipient, you can use a DLookup() to retrieve your associate Email address
objMailItem.To = DLookup("Email_ID", "dbo_Associates$", "[Fullname]='" & Me.cboAssociate & "'")


'set subject with text and Form values
objMailItem.Subject = "Quality Violation " & Me.LOAN_CODE

'set body content with text and Form values etc.
objMailItem.Body = Me.qv_date & vbCrLf & "some text here:" & vbCrLf & Me.Entered_Date

' display email
' objMailItem.Display

' sending mail automaticly
objMailItem.Send

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
exit_Error_handler:
 On Error Resume Next

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
 Exit Sub
 
err_Error_handler:
 Select Case Err.Number
  'trap error 287
  Case 287
   MsgBox "Canceled by user.", vbInformation
  Case Else
   MsgBox "Error " & Err.Number & " " & Err.Description
 End Select

Resume exit_Error_handler
End Sub 

Open in new window

0
 
dplowmanAuthor Commented:
Thanks, If I complete the form and click on the command button, the form closes and sends the appropriate email. However, if I open the form and fill it out, click the command button, the same record from the previous form is recorded in the table, along with the prior details sent in the email. Any ideas as to why?
0
 
dplowmanAuthor Commented:
Here is what I am working with. Whenever I choose the same associate from the form that already has a record in the quality violations table, the record just duplicates the original entry.


Private Sub cboAssociate_AfterUpdate()

Me.txtTeamLead.Value = Me.cboAssociate.Column(1)

End Sub
---------------------
Private Sub cboCategory_AfterUpdate()

Me.cboSubCategory.RowSource = "SELECT distinct([SUBCATEGORY]) FROM [dbo_QV_Categories$] WHERE [CATEGORY]='" & Me.cboCategory.Value & "';"

End Sub
----------------------------
Private Sub cboSection_AfterUpdate()

 Me.cboCategory.RowSource = "SELECT distinct([CATEGORY]) FROM [dbo_QV_Categories$] WHERE [SECTION]='" & Me.cboSection.Value & "';"

End Sub
---------------------------------------------
Private Sub cmdSaveandNew_Click()

DoCmd.RunCommand acCmdSaveRecord
cmdEmail_Click
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "QV"

End Sub
---------------------------------------
Private Sub cmdEmail_Click()

Dim objOutlook As Object
Dim objMailItem As Object
Const olMailItem As Integer = 0
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(olMailItem)

Dim strPathAttach As String

On Error GoTo err_Error_handler

'set receipient, you can use a DLookup() to retrieve your associate Email address
objMailItem.To = DLookup("Email_ID", "dbo_Associates$", "[Fullname]='" & Me.cboAssociate & "'")


'set subject with text and Form values
objMailItem.Subject = "Quality Violation " & Me.LOAN_CODE

'set body content with text and Form values etc.
objMailItem.Body = Me.qv_date & vbCrLf & "some text here:" & vbCrLf & Me.Entered_Date

' display email
' objMailItem.Display

' sending mail automaticly
objMailItem.Send

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
exit_Error_handler:
 On Error Resume Next

 Set objOutlook = Nothing
 Set objMailItem = Nothing
 
 Exit Sub
 
err_Error_handler:
 Select Case Err.Number
  'trap error 287
  Case 287
   MsgBox "Canceled by user.", vbInformation
  Case Else
   MsgBox "Error " & Err.Number & " " & Err.Description
 End Select

Resume exit_Error_handler
End Sub

Open in new window

0
 
dplowmanAuthor Commented:
I think I may have figured it out on my own. I needed to create an auto number in the quality violations table so the form would now that it was a unique record!
0
 
mbizupCommented:
Ok -

I hit the sack shortly after posting my comment here last night.

It sounds like you've got this sorted out, but post back if you still need help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now