Action After Mail is Sent

[Gerrit.B]

I use code below to create mail with attachment.

How can I run DoCmd.RunSQL SQL after mail is send and cancel DoCmd.RunSQL SQL if mail is NOT send?

Private Sub Mailpo2_DblClick(Cancel As Integer)

On Error GoTo ErrorHandler


Dim strMsg As String
Dim SD As String
Dim SQL As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strAttach1 As String


Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)


If Me.TransporttationID = 2 Then
SD = "Shipping Marks: " & [Shipping Marks] & Chr(13) + Chr(10) & "Vessel: " & [Vessel] & Chr(13) + Chr(10) & "ETD: " & [DepartureDate] & Chr(13) + Chr(10) & Chr(13) + Chr(10)
Else
SD = "Shipping Marks: " & [Shipping Marks] & Chr(13) + Chr(10) & "Flight: " & [Vessel] & Chr(13) + Chr(10) & "ETD: " & [DepartureDate] & Chr(13) + Chr(10) & "AWB: " & [AWB] & Chr(13) + Chr(10)
End If




strMsg = "Dear " & [FirstName] & "," & Chr(13) + Chr(10) & Chr(13) + Chr(10) & [HandlingAddress] & Chr(13) + Chr(10) & Chr(13) + Chr(10) & Nz([Clausulep1], "") & Chr(13) + Chr(10) & Chr(13) + Chr(10) & Nz([ClausuleP3], "") & Chr(13) + Chr(10) & Chr(13) + Chr(10) & Nz([ClausuleP2], "") & Chr(13) + Chr(10) & Chr(13) + Chr(10) & [DocTo] & Chr(13) + Chr(10) & Chr(13) + Chr(10) & SD & Chr(13) + Chr(10) & [TextOnBL] & Chr(13) + Chr(10) & [Customs] & Chr(13) + Chr(10) & Chr(13) + Chr(10)


strAttach1 = sDefaultPath & [Path] & "/" & [OrderNumber] & "A.pdf"


If Me.Check = -1 Then


With objEmail
.To = [email]
.CC = Nz([emailCC], "")
.Subject = "Ordernumber " & [OrderNumber]
.Body = strMsg
.Display
.Attachments.Add strAttach1
 
End With


    SQL = "UPDATE tblShipmentDetails SET tblShipmentDetails.Status = [tblShipmentDetails]![Status]+1 WHERE (((tblShipmentDetails.DetailID)=[Forms]![frmSendPO]![subMailBodyOrder].[Form]![DetailID]));"
    DoCmd.RunSQL SQL
    
Else
DoCmd.CancelEvent
MsgBox "File to Add Does Not Exist"
GoTo Repeat


End If


ErrorHandler:
   If Err.Number = 3059 Then
      MsgBox "No Records Updated!"
         GoTo Repeat


   Else
      MsgBox "An error occurred:" & vbCrLf & _
             "Error " & Err.Number & ": " & Err.Description
         GoTo Repeat


  
Repeat:
End If
End Sub

Gerrit

 

[Micron]

Re: Action Arfer Mail is Send

I presume an error is generated if not sent? Then put DoCmd.RunSql after the .send and execution will jump to ErrorHandler where you can decide what to do there.
Why would it not send?

Some other suggestions
- you don't need GoTo Repeat - the way you have written it, it is going there regardless.
- your objects are not being destroyed anywhere in code, memory is being held because of that
- you should deal with decisions before creating these objects e.g. determine if file is available, if something -1, etc. such as:
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
- I don't think you need Else If for If check = -1 if this is a user yes/no choice. If the control value is no, none of the block will get executed anyway, except for the message which I don't understand how the problem is that no file was found
- why the use of so many ASCII character conversions when you can use vbCrLf for both?

 

[ranman256]

Re: Action Arfer Mail is Send

add an error trap, if successfull, run sql...

if .Send then
  docmd.runSql  sSql
else
  msgbox "Send Failed"
end if

 

[Gerrit.B]

Re: Action Arfer Mail is Send

When running the code it will open a new mail in Outlook.
If the user decides not to send, it will not give an error.
So it will run the SQL part.

 

[Micron]

Re: Action Arfer Mail is Send

**hang on, just realized this is not quite right. Will try some more.
Dim wasSent as Boolean

After With block,
wasSent = objEmail.Sent
If wasSent = true Then do sql stuff
Else
don't do sql stuff

or maybe just
If objEmail.Sent Then do sql stuff else don't

Don't forget what I wrote about not destroying your objects in memory, and I would try adding the attachment before the message display.

 

[Micron]

Re: Action Arfer Mail is Send

It was coming back as False regardless, so I found that you need .display True to make Outlook modal as well as display the message. Also, I changed the boolean to variant for my own sanity since boolean defaults to false. The combination of name and false property (was false when sent) is OK but confusing. I figured this also provides a bit more flexibility if needed later. I also changed the logic of the approach.

Dim varSent As Variant

If Me.Check = -1 
   With objEmail
   .To = "abc@xyz.com"
   .CC = ""
   .Subject = "Order number abcd"
   .Body = strMsg
  ' .Attachments.Add strAttach1
   .Display True
End With

 'you will have to redirect the error routine temporarily
On Error Resume Next

'try to get .Sent property of the item. If sent, an error will be generated
'because the item no longer exists. If deleted or saved and closed, 
'the item exists and its '.sent property can be determined
varSent = objEmail.Sent 'will be null if sent

If Err = 0 Then 'email was NOT sent
   MsgBox "FALSE" 'replace this with your do/don't stuff
   Exit Sub
Else
   MsgBox "TRUE"  'replace this with your do/don't stuff
   Exit Sub
End If
'you may need to redirect error routine back to original, otherwise just exit sub

NOTE: I'm assuming the existence of the mail item in user's Sent folder will not cause issues. It did not for me.

 

[Gerrit.B]

Re: Action Arfer Mail is Send

Thanks
It's working Now!

 

[Micron]

Re: Action Arfer Mail is Send

You're welcome. Glad we could help.