troubleshooting Question

I need form to close after an email is sent.

Avatar of Jason Steward
Jason Steward asked on
OutlookMicrosoft AccessVBA
7 Comments1 Solution88 ViewsLast Modified:
Access doesn't execute DoCmd.Close to close the form after an email is sent.

Here's my entire code:

Private Sub Command799_Click()

Me.Dirty = False
    
If IsNull(Me.txtChargeCode.Value = True) And Me.cboMancamp.Value > 0 Then

        MsgBox "You must enter an AFE, Work Order, or Cost Center", vbOKOnly, "CHARGE CODE REQUIRED"

Else

If Me.cboMancamp.Value > 0 And IsNull(Me.txtStartDate.Value = True) Then

        MsgBox "You must enter an arrivial and check-out date", vbOKOnly, "LODGING DATES REQUIRED"

Else

If Me.cboMancamp.Value > 0 And IsNull(Me.txtEndDate.Value = True) Then

        MsgBox "You must enter an arrivial and check-out date", vbOKOnly, "LODGING DATES REQUIRED"

Else

DoCmd.RunCommand acCmdSaveRecord

Dim strBody As String
Dim strEmail As String
Dim strSubject As String
Dim objOutlook As Object
Dim objMailItem As Object
Const olMailItem As Integer = 0
Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(olMailItem)
    
On Error GoTo err_Error_handler

strEmail = [TO_Group]
strCc =  [CC_GROUP]
strSubject = "Field Visit Booking (Request ID# " & Me.FieldVisitBookingID & ") For " & txtContactName & " Starting " & Me.txtStartDate & " Finishing " & Me.txtEndDate
strBody = "<IMG alt='' hspace=0 src='L:\SharedData\Harrison\Midstream\ACIST\Support Files\Graphics\OPEN_email header-02.jpg' align=baseline border=0> <br>" & _
    "<br><br><font face=Calibri style=font-size:14pt;>All,<br><br> " & "" & _
    "A representative from " & Me.txtCompanyFullName & ", " & Me.txtContactName & ", (" & Me.txtContactMobile & ") will be visiting the field between <b>" & Me.txtStartDate & "</b> and <b>" & Me.txtEndDate & "</b>.  " & _
    "We would like to book them at the " & Me.cboMancamp & " and charge to <b>" & txtChargeCode & "</b>.<br /> " 

                  

objMailItem.SentOnBehalfOfName = strFro
objMailItem.To = strEmail
objMailItem.CC = strCc
objMailItem.Subject = strSubject
objMailItem.Importance = olImportanceHigh
objMailItem.HTMLBody = strBody
objMailItem.BodyFormat = olFormatHTML
objMailItem.Attachments.Add ("L:\test.pdf")
objMailItem.Display
 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
  Case 287
   MsgBox "Canceled by user.", vbInformation
  Case Else
   MsgBox "Error " & Err.Number & " " & Err.Description
 End Select

Resume exit_Error_handler

DoCmd.Close acForm, "FieldVisitBookingEntryF"

End If
End If
End If
End Sub

The code executes perfectly to run the email, but doesn't close the form afterwards.  Thanks.
ASKER CERTIFIED SOLUTION
Shaun Kline
Lead Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros