Solved

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

Posted on 2014-04-07
6
1,951 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 26

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 26

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

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 26

Expert Comment

by:MacroShadow
ID: 39984205
My pleasure!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

My experience with Windows 10 over a one year period and suggestions for smooth operation
Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
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…

706 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

19 Experts available now in Live!

Get 1:1 Help Now