Solved

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

Posted on 2014-04-07
6
1,998 Views
Last Modified: 2014-04-07
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
0
Comment
Question by:murbro
  • 4
  • 2
6 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39984122
Look in the sent items.

If you want to send it use
Msg.Send and not Msg.Display (note you have a typo).
0
 

Author Comment

by:murbro
ID: 39984136
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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39984144
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39984159
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
 

Author Closing Comment

by:murbro
ID: 39984198
Thank you very much for taking all the time out to show me. Much appreciated!
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39984205
My pleasure!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now