Check to see if email has been sent

[Bobnz]

Hi, I have an email sub procedure that sends an order as an attachment. However if the company does not exist in outlooks contacts then outlook opens and asks the user to select the recipient. I want to be able to test to see if the order was
sent or cancelled and output an appropriate message for either action.

access 2000 on win 98se

email :

Sub SendMessage(Optional AttachmentPath)
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.recipient
   Dim objOutlookAttach As Outlook.Attachment
   Dim supplier As Variant
   supplier = recipient
   
   
   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add(supplier)
      objOutlookRecip.type = olTo
     

      ' Add the CC recipient(s) to the message.
      Set objOutlookRecip = .Recipients.Add("rob.morg@ihug.co.nz")
      objOutlookRecip.type = olCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "Order"
      .Body = "Please fill the attached order: " & vbCrLf & vbCrLf
      .Importance = olImportanceHigh  'High importance

      ' Add attachments to the message.
      If Not IsMissing(AttachmentPath) Then
         Set objOutlookAttach = .Attachments.Add(AttachmentPath)
      End If

      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .Send
     
       
    MsgBox " Your Order has been sent"
   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

B

 

[TomHW]

Every Outlook.MailItem fires a Send and a Close event. Try hooking in to those events for the mailitem if you have to display it. If its Close event fires before its Send event then you know that it was cancelled.

The problem here is that the visible MailItem is now running in a separate process and could sit there indefinitely so you'll have to take that into consideration.

Tom

 

[Bobnz]

Thanks Tom, I'm not a programmer as such, how would I go about testing to see if close event fired before the send event?

if(.send=true)....?

Bob

 

[danvlas]

Well...add an error handling routine.
If the message is sent, the .Send method has succeeded. If not, that method will return an error.
Trap the error and you're home free...

A very simplistic way is:

On Error Resume Next
.Send
If Err.Number Then MsgBox "Failure"




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TomHW]

Let me beign by saying that I am very short on time at the moment so this post will be thrown together rather quickly, but should clear up a fe issues and will hopefully solve your problem.

The problem with error checking is that .Send will either produce an error for both cases (Sent or Cancelled) or for neither depending on the recipients being resolved. The reason for this is that once .Display is called when a recipient can not be resolved, responsibility for the MailItem transfers from the code to the user. The function finishes as soon as .Display is run, yet at this point the MailItem has just been displayed to the user. The MailItem remains for the user to make changes to the recipient if necessary and then send or cancel. By the time this occurs, no code is running to check which is why using WithEvents is required.

Below is a sample Class Module that gives a general idea as to how to hook into the Send and Close events of a MailItem.

Option Compare Database
Option Explicit

[green]' OutlookMail Class[/green]

Private WithEvents molMail As Outlook.MailItem
Private WithEvents molApp As Outlook.Application

Private mvarRecip As Variant
Private mvarCC As Variant
Private mstrSubject As String
Private mstrBody As String
Private mvarAttachPath As Variant
Private molImport As Outlook.OlImportance
Private mblnResolved As Boolean

Private Sub Class_Terminate()
    [green]' Clean up[/green]
    Set molMail = Nothing
    Set molApp = Nothing
End Sub

Public Sub Init(varRecip As Variant, strSubject As String, strBody As String, Optional varCC As Variant, _
                    Optional polImport As Outlook.OlImportance = olImportanceHigh, Optional varAttachPath As Variant)
    [green]' Get all of our email field values[/green]
    mvarRecip = varRecip
    If Not IsMissing(varCC) Then
        mvarCC = varCC
    End If
    mstrSubject = strSubject
    mstrBody = strBody
    molImport = polImport
    If Not IsMissing(varAttachPath) Then
        mvarAttachPath = varAttachPath
    End If
End Sub

Public Function Send() As Boolean
    Dim polRecip As Outlook.Recipient
    Dim polAttach As Outlook.Attachment
    
    [green]' Create the session[/green]
    Set molApp = CreateObject("Outlook.Application")
    
    [green]' Create the message.[/green]
    Set molMail = molApp.CreateItem(olMailItem)

    With molMail
        [green]' Add the To recipient(s) to the message.[/green]
        Set polRecip = .Recipients.Add(mvarRecip)
        polRecip.Type = olTo
     

        [green]' Add the CC recipient(s) to the message.[/green]
        Set polRecip = .Recipients.Add(mvarCC)
        polRecip.Type = olCC

        [green]' Set the Subject, Body, and Importance of the message.[/green]
        .Subject = mstrSubject
        .Body = mstrBody
        .Importance = molImport

        [green]' Add attachments to the message.
        'If Not mvarAttachPath Is Nothing Then
        '    Set polAttach = .Attachments.Add(mvarAttachPath)
        'End If[/green]
        
        [green]' Resolve each Recipient's name.[/green]
        mblnResolved = True
        For Each polRecip In .Recipients
            If Not polRecip.Resolve Then
                .Display
                mblnResolved = False
            End If
        Next
        
        [green]' Send if all recipients have been resolved, otherwise things have been handed over to the user[/green]
        If mblnResolved Then
            .Send
            MsgBox " Your Order has been sent"
        End If
    End With
    
    MsgBox "Send over"
End Function

Private Sub molMail_Send(Cancel As Boolean)
    MsgBox "Mail Sent"
End Sub

Private Sub molMail_Close(Cancel As Boolean)
    MsgBox "Mail Closed"
End Sub

This is not well tested and has some quirks because it was written very quickly. What you'll notice if you create an instance of this class and then run Init() and then Send() is that if the recipients are resolved, then the Send event occurs, followed by the mail actually being sent, followed by the Send() function completing. If a recipient is not resolved a MailItem becomes visible, followed immediately by the Send() function completing. Then when a user either sends or cancels, the Send() then Close() events run or the Close() event runs, respectively.

One thing to notice is that both events are cancellable, which means the events run immediately before the event actually occurs, which means any code in the functions will run before the event finalizes, which allows them to be cancelled.

You can easily add a variable to determine if the Close event ran without the Send event running or not.

Sorry about how quickly this was written and that I don't have much time to test this today.

Hope this helps,
Tom

 

[Bobnz]

Thanks Tom, I will spend a couple of days looking at this and see if I can work it out .

I will need to see if the close event runs without the send event running as the order table will be updated only if the send event has run.

Danvlas thanks for the input.

B

 

[Bobnz]

Tom, I have decided to put the onus on the user to have a correctly entered name in the outlook contact list. By inserting...

If Not objOutlookRecip.Resolve Then
MsgBox "The recipients name [" & recipient & "] cannot be found in the outlook contact list. Please check to see that recipients name has been entered correctly"
End
End If

...easier allround this way.. well easier for me anyway

Thanks for the help

B