Solved

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

Posted on 2014-04-07
6
2,032 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Find out what you should include to make the best professional email signature for your organization.
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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