Excel VBA - determine whether an Outlook message displayed is actually sent

Hi. I have an Outlook message declared as follows in Excel VBA. It then is displayed in the second line. How do I determine if the message is actually sent?
Dim Msg As Outlook.MailItem
Msg.Disply
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
MacroShadowConnect With a Mentor Commented:
Cool!

1. Add a reference to outlook
2. Create a class module, name it EmailWatcher
3. Copy this code to the class:
Public BoolRange As Range
Public DateRange As Range
Public WithEvents TheMail As Outlook.MailItem


Private Sub TheMail_Send(Cancel As Boolean)
    If Not BoolRange Is Nothing Then
        BoolRange.Value = True
    End If
    If Not DateRange Is Nothing Then
        DateRange.Value = Now()
    End If
End Sub

Open in new window

4. Here is a sample usage:
With oMail
    .To = addr
    .Subject = "CCAT eVSM Utilities License Code"
    .Body = "Message body"
    .Display
End With
Set CurrWatcher = New EmailWatcher
Set CurrWatcher.BoolRange = Range("G12")
Set CurrWatcher.TheMail = oMail

Open in new window

Range G12 will tell you when it was sent
0
 
MacroShadowCommented:
Look in the sent items.

If you want to send it use
Msg.Send and not Msg.Display (note you have a typo).
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. Thanks, I am actually looking to detect programmatically whether the email was sent. Sorry for not explaining that clearly
I saw this link but am not sure how to implement it
http://stackoverflow.com/questions/2533066/vba-outlook-mail-display-recording-when-if-sent-manually
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
MacroShadowCommented:
Here is a sample that will loop thru an Outlook folder:
Sub Download_Outlook_Mail_To_Excel()
    'Add Tools->References->"Microsoft Outlook nn.n Object Library"
    'nn.n varies as per our Outlook Installation
    Dim Folder As Outlook.MAPIFolder
    Dim iRow As Integer
 
    'Mailbox or PST Main Folder Name (As how it is displayed in your Outlook Session)
    MailBoxName = "MailBox Name"
 
    'Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)
    Pst_Folder_Name = "Folder Name" 'Sample "Inbox" or "Sent Items"
 
    Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Pst_Folder_Name)
    If Folder = "" Then
        MsgBox "Invalid Data in Input"
        GoTo end_lbl1:
    End If
 
    'Rad Through each Mail and export the details to Excel
    Sheets(1).Activate
    Folder.Items.Sort "Received"
    For iRow = 1 To Folder.Items.Count
        Sheets(1).Cells(iRow, 1).Select
        Sheets(1).Cells(iRow, 1) = Folder.Items.Item(iRow).SenderName
        Sheets(1).Cells(iRow, 2) = Folder.Items.Item(iRow).Subject
        Sheets(1).Cells(iRow, 3) = Folder.Items.Item(iRow).ReceivedTime
        Sheets(1).Cells(iRow, 4) = Folder.Items.Item(iRow).Size
        'Sheets(1).Cells(iRow, 5) = Folder.Items.Item(iRow).SenderEmailAddress
        'Sheets(1).Cells(iRow, 6) = Folder.Items.Item(iRow).Body
    Next iRow
    MsgBox "Outlook Mails Extracted to Excel"
 
end_lbl1:
 
End Sub

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you very much for taking all the time out to show me. Much appreciated!
0
 
MacroShadowCommented:
My pleasure!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.