asked on

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

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dplowman

ASKER

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?

dplowman

ASKER

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

dplowman

ASKER

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!

mbizup

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.