locked
Email Sent Programmatically Through MS Access VBA Not Showing in Outlook Sent Items RRS feed

  • Question

  • I set up a form in Access which collects data and sends the data in a email.  Once the email is sent programmatically the Sent Items folder in Outlook is checked for that email.  The sub uses either the GetObject or CreateObject to reference Outlook.  Then Send and Receive is performed.  Then the Sent Items folder is checked for an email with the unique subject of the email sent programmatically.

    The problem I'm having is even though the email is sent and Send and Receive has been performed the email is not found in the Sent Items folder.  The program is designed to exit the function and return to the form when the email is not sent.  When the users for the second time click "Submit" on the form to send the email and the process repeats, then the email is located in the Sent Items folder.

    Why is the email not found in the Sent Items folder in Outlook the first time?

    Wednesday, November 25, 2020 12:57 PM

All replies

  • If Outlook is open, can you actually see the email being created in the Sent Items folder?

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Wednesday, November 25, 2020 4:16 PM
  • Yes, I have the newly created email displayed before it is sent.  After the email is sent and the Sent folder is checked, even if the message shows the email was not sent some of the users check their Sent folder and see that the email was sent. Since many of the users are remote and it may be a delay when performing Send and Receive should I include a pause until the Send and Receive action is complete before checking for the sent email?
    Wednesday, November 25, 2020 4:23 PM
  • Please show your code that sends the message and checks the sent message in the Sent Items folder.

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Wednesday, November 25, 2020 5:10 PM
  • Here is the code:

    With olkMsg
        .To = strRecipient
        .Save
        .Attachments.Add strAttach, olByValue
        .Subject = strSubject
        .Display
        .Application.ActiveWindow.WindowState = olMinimized
        blnResult = MsgBox("Send Request now?", vbQuestion + vbYesNoCancel, "Send Request")
        If blnResult = vbYes Then
        .Send
        olkNamespace.SendAndReceive True

        End If
    End With


    Function CheckMsgSent(strSubject As String) As Boolean
    Dim olkApp As Outlook.Application
    Dim olkMsg As Outlook.MailItem
    Dim olkContact As Outlook.ContactItem
    Dim olkInspector As Outlook.Inspector
    Dim olkNamespace As Outlook.Namespace
    Dim olkFolder As Outlook.Folder
    Dim colItems As Outlook.Items

    Set olkNamespace = olkApp.GetNamespace("MAPI")
    olkNamespace.SendAndReceive False

    'Check to see if message was sent
    Set olkFolder = olkNamespace.GetDefaultFolder(olFolderSentMail)
    Set colItems = olkFolder.Items

    Set olkMsg = colItems.Find("[Subject] = " & Chr(34) & strSubject & Chr(34))


    If Not olkMsg Is Nothing Then
        CheckMsgSent = True
    Else
        CheckMsgSent = False
    End If

    End Function

    Wednesday, November 25, 2020 6:03 PM
  • When is CheckMsgSent function called?

    Keep in mind that SendAndReceive is asynchronous, and it can be a long time (seconds) before the message is actually sent.

    It is much better to use items.ItemAdd event on the Sent Items folder - the item will be passed as a parameter to your event handler. Make sure to declare the Items variable (that raises the events) on the global level so it does no get released.


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    • Proposed as answer by KHURRAM RAHIM Friday, November 27, 2020 11:04 AM
    Thursday, November 26, 2020 11:32 PM
  • Thank you for your response. I've been trying to get this to work but I can't seem to be able to initialize the event handler.

    Initially, I used the CheckMsgSent function after the function to send the email is complete but before the code for the Submit button on the form is finished.

    Here is the code in the Class module:

    Option Compare Database
    Option Explicit
    
    Public WithEvents OlkItems As Outlook.Items
    
    Public Sub Initialize_handler()
     
     Set OlkItems = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderSentMail).Items
     
    End Sub
    
    Private Sub OlkItems_ItemAdd(ByVal Item As Object)
     
        MsgBox "The CAD Request has been sent.", vbOKOnly, "CAD Request Sent"
        
    End Sub

    I'm not sure how to make the connection to the function that sends the email.

    Monday, December 7, 2020 11:53 PM