Solved

Outlook macro that checks all selected mails in a folder and gets the delivered and read data to an excel. need help.

Posted on 2009-06-27
60
389 Views
Last Modified: 2012-05-07
Hi,

Outlook macro that checks all selected mails in a folder and gets the delivered and read data to an excel. need help.
 I have this code from David. that gets the read and delivered data to an excel. at present the tracking is not created because the mails read and delivered confirmation mails get into a folder called "Delivered&Read"

I need help with a work around for this situation.

Regards
Sharath

Sub RecipientReport()
    Const xlWorkbookDefault = 51
    Dim olkMessage As Outlook.MailItem, _
        olkRecipient As Outlook.Recipient, _
        excApp As Object, _
        excBook As Object, _
        excSheet As Object, _
        lngRow As Long
    Set excApp = CreateObject("Excel.Application")
    Set excBook = excApp.Workbooks.Add()
    Set excSheet = excBook.Worksheets(1)
    excApp.Visible = True
    With excSheet
        .Cells(1, 1) = "Subject"
        .Cells(1, 2) = "To"
        .Cells(1, 3) = "Delivered"
        .Cells(1, 4) = "Read"
    End With
    lngRow = 2
    For Each olkMessage In Application.ActiveExplorer.Selection
        excSheet.Cells(lngRow, 1) = olkMessage.Subject
        For Each olkRecipient In olkMessage.Recipients
            With olkRecipient
                excSheet.Cells(lngRow, 2) = olkRecipient.Address
                Select Case .TrackingStatus
                    Case olTrackingDelivered
                        excSheet.Cells(lngRow, 3) = olkRecipient.TrackingStatusTime
                    Case olTrackingNotDelivered
                    Case olTrackingRead
                        excSheet.Cells(lngRow, 4) = olkRecipient.TrackingStatusTime
                    Case olTrackingNotRead
                    Case olTrackingNone
                End Select
            End With
            lngRow = lngRow + 1
        Next
    Next
    Set excSheet = Nothing
    'Change the file name and path on the next line'
    excBook.SaveAs "C:\eeTesting\Message Tracking.xlsx", xlWorkbookDefault
    excBook.Close True
    Set excBook = Nothing
    Set excApp = Nothing
    Set olkRecipient = Nothing
    Set olkMessage = Nothing
    MsgBox "Done", vbInformation + vbOKOnly, "Recipient Report"
End Sub

Open in new window

0
Comment
Question by:bsharath
  • 35
  • 25
60 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 24734572
Hi, Sharath.

Look back at this question: http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24457457.html

We can accomplish what you've asked by changing a few lines.

1.  Change line #1 to
    Dim WithEvents olkDeleted As Outlook.Items

2.  Change line #8 to
    Set olkDeleted = Application.Session.GetDefaultFolder(olFolderDeletedItems).Items

3.  Change line #11 to
    Private Sub olkDeleted_ItemAdd(ByVal Item As Object)
0
 
LVL 11

Author Comment

by:bsharath
ID: 24734617
Thanks David...
Sent you an email with my This outlooksession code... As you know there are other codes in there...
0
 
LVL 11

Author Comment

by:bsharath
ID: 24734618
Thanks David...
Sent you an email with my This outlooksession code... As you know there are other codes in there...
0
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.

 
LVL 11

Author Comment

by:bsharath
ID: 24763159
0
 
LVL 11

Author Comment

by:bsharath
ID: 24779313
Hi David...
Any views...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24779897
Open and close both receipts.  Does that update the tracking information?
0
 
LVL 11

Author Comment

by:bsharath
ID: 24780299
David i did so and waited for a long time....
Attached is a screenshot
1st is manually sent mail
2nd is without any receiptiant
3rd is via the script

Only the one i sent manually i get the tracking option
Capture.JPG
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24780478
"Only the one i sent manually i get the tracking option"
How were the other messages sent?
0
 
LVL 11

Author Comment

by:bsharath
ID: 24780515
The 2nd i sent normally like when the popup asked me yes/no i said No
The 3rd one i selected yes when the macro asked me for the receiptiant
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24780570
Sorry, but I'm lost.  I've no idea what you're talking about.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24780595
David if you remember the code in the snippet you gave me will ask me if i want a receiptiant for read and delivered.
So when i use this code in the snippet and say yes. the tracking does not get created....
The code you gave me for this post does work if i send a mail manually by selecting the read and delivery check boxes. Rather then the below code "yes or no"

Hope now i get you to the right path

The code from
http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24457457.html

Now the code below has to create a tracking first for me to use your solution for this post
Sub ReceiptSwitch()
'Read and delivery report swtch
 
    bolReadReceipt = Not bolReadReceipt
    bolDeliveryReceipt = Not bolDeliveryReceipt
    MsgBox "Read and delivery receipts are now " & IIf(bolReadReceipt, "on", "off")
End Sub
 
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    toMe Item
    If (Not bolReadReceipt) And (Not bolDeliveryReceipt) Then
        If MsgBox("Do you want receipts for this message?", vbQuestion + vbYesNo, "Request Receipts") = vbYes Then
            Item.ReadReceiptRequested = True
            Item.OriginatorDeliveryReportRequested = True
        End If
    Else
        Item.ReadReceiptRequested = bolReadReceipt
        Item.OriginatorDeliveryReportRequested = bolDeliveryReceipt
    End If
   ' CheckFollowUp Item, Cancel
    Item.Save
End Sub

Open in new window

0
 
LVL 76

Expert Comment

by:David Lee
ID: 24780622
Got it.  If you send a message where you've manually requested a receipt, then everything works correctly.  But if you send a message and request a receipt via this macro, then things aren't working.  Is that correct?
0
 
LVL 11

Author Comment

by:bsharath
ID: 24780632
yes right
When a mail sent and i select yes. the tracking does not get created ... even when the Delivered and read mails land back into the inbox...
And they are in the inbox for days... The sent item does not get the tracking...

0
 
LVL 76

Expert Comment

by:David Lee
ID: 24780816
And all the sent items are in the default sent items folder, right?
0
 
LVL 11

Author Comment

by:bsharath
ID: 24782211
Yes David all in the default Sent folderonly. In my Mailbox...
i receive mails and Store them in the mailbox folders and later move them to my Pst's
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24790504
Open one of the messages where you requested a receipt via the script.  Click the Message Options button in the Options group of the ribbon.  Are both boxes checked under Tracking Options?
0
 
LVL 11

Author Comment

by:bsharath
ID: 24791110
David when i open the sent mail Via the script. I dont get any option that shows the tracking...
0
 
LVL 11

Author Comment

by:bsharath
ID: 24791111
David when i open the sent mail Via the script. I dont get any option that shows the tracking...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24792442
I understand, but that's not what I asked you to check.  
0
 
LVL 11

Author Comment

by:bsharath
ID: 24792516
>>Open one of the messages where you requested a receipt via the script
Hope you mean send a mail and select Yes. After that open the sent item and see for the option message tracking.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24792517
>>Open one of the messages where you requested a receipt via the script
Hope you mean send a mail and select Yes. After that open the sent item and see for the option message tracking.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24792534
"Hope you mean send a mail and select Yes"
Correct.

"After that open the sent item and see for the option message tracking."

1.  Click the Message Options button in the Options group of the ribbon.  
2.  Look at the Tracking Options section.  Are both boxes checked?
0
 
LVL 11

Author Comment

by:bsharath
ID: 24792571
David...
Attached is the image
I cannot see an option as "Options" in the ribbon
Can you suggest please...
Capture.JPG
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24792852
This looks like a new message.  Open the item you sent.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24792900
No thats the item i sent. For a new message window i get 4 tabs in the ribbon .For the sent mail just 1 tab as shown in the above puicture
0
 
LVL 11

Author Comment

by:bsharath
ID: 24792901
No thats the item i sent. For a new message window i get 4 tabs in the ribbon .For the sent mail just 1 tab as shown in the above puicture
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24792919
Ok.  Seems strange that a sent message would be untitled.  That aside, do you see the Options group?  It's the group that has the icons labeled Categories, Follow Up and Mark as Unread.  There's a solid dot to the right of the word Options.  Click it.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24792958
David.. I right clicked the message and selected "Message options" and i could see the attached image
When Selected "NO" and i send the mail these are unchecked and when selected Yes via the script . The check boxes are checked...
Capture.JPG
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24798309
Let's check one other thing.

1.  Click Tools and select Options
2.  Click the E-mail Options button
3.  Click the Tracking Options button
4.  Are the first three boxes checked?
0
 
LVL 11

Author Comment

by:bsharath
ID: 24800414
David below is how the window looks...
0
 
LVL 11

Author Comment

by:bsharath
ID: 24800415
David below is how the window looks...

Capture.JPG
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24801966
Check the 3rd box, "After processing move receipts to", and leave the destination folder set to Deleted Items.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24801983
Done... I sent an email i did get the read and delivery receipts but tracking not created...
0
 
LVL 11

Author Comment

by:bsharath
ID: 24801984
Done... I sent an email i did get the read and delivery receipts but tracking not created...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24809068
Then there's something wrong with Outlook on your computer.  The scripts don't have anything to do with tracking.  That is built into Outlook.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24810932
David but when manually a mail sent after checking both check boxes. It does creating the tracking...
Read and deliver options are checked. But via script when selected yes i do receive the delivered and read mails to my inbox but tracking does not get created...
0
 
LVL 11

Author Comment

by:bsharath
ID: 24810933
David but when manually a mail sent after checking both check boxes. It does creating the tracking...
Read and deliver options are checked. But via script when selected yes i do receive the delivered and read mails to my inbox but tracking does not get created...
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 24811743
I understand and I'm at a loss to explain that.  All the script does is request a receipt.  There's no difference between requesting a receipt manually and requesting it via a script.  Both set the underlying Outlook properties OriginatorDeliveryReportRequested andReadReceiptRequested to True.  You proved that the script is doing this properly in post 24792958.  Outlook cannot distinguish between a receipt generated by a message where you manually requested a receipt and one where you used the script to request a receipt.  A receipt is a receipt.  Since Outlook is correctly processing receipts that were requested manually, but not those that were requested by scripting, then there must be something wrong with Outlook.  The only other possibility is that the sent items are not in Sent Items, in which case receipt processing would fail since it only looks for the original message in Sent Items, or if the receipt was being diverted on arrival to some folder other than the inbox.  I asked about both conditions and you have assured me that the original messages are in Sent Items and that receipts are being delivered to the Inbox.  The final check was to ensure that receipt processing is turned on.  It is.  I've eliminated all the possible causes I can think of.  The only possibility that remains is that there's something wrong with Outlook.  It doesn't make sense, but it's all that's left.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24811767
Ok David... Thanks for the explanation...

I shall reinstall Outlook but one Q...How can i check if the sent folder is the default or a wrong one...
0
 
LVL 11

Author Comment

by:bsharath
ID: 24811768
Ok David... Thanks for the explanation...

I shall reinstall Outlook but one Q...How can i check if the sent folder is the default or a wrong one...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24811885
Since you are on Exchange the Sent Items folder in your mailbox (i.e. indented under Mailbox - Sharath) is the default folder for sent items.  If it's in a PST file, then it is NOT the default sent items folder.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24811895
Don't reinstall Outlook yet.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24811902
Ok David... Then thats the deault Sent folder. As i receive all mails and the sent mails are in the mailbox only.

Any way i shall reinstall Outlook and get back...
Ok got your other comment.. I will not reinstall now...

Can you have a look at my other posts when time permits...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24831582
Receipts have stopped working altogether on my system  It doesn't matter how I request a receipt, the tracking information is no longer being updated.  I'm trying to determine why.  Discovering the answer may help me figure out why it isn't working at your end.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24831597
Oh ok David...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24835346
Do you have any rules that makes changes to the receipts?  For example a rule that assigns a receipt to a category, flags it, does anything to it at all?
0
 
LVL 11

Author Comment

by:bsharath
ID: 24836858
No David i dont think So...
The only one that you gave e yesterday to flag sent mails if needed...
No inbuilt rules
0
 
LVL 11

Author Comment

by:bsharath
ID: 24836859
No David i dont think So...
The only one that you gave e yesterday to flag sent mails if needed...
No inbuilt rules
0
 
LVL 11

Author Comment

by:bsharath
ID: 25005481
Hi David any views on this ....
0
 
LVL 76

Expert Comment

by:David Lee
ID: 25109510
Are receipts still not working on your system?
0
 
LVL 11

Author Comment

by:bsharath
ID: 25109531
Yes david still the same issue....
0
 
LVL 76

Expert Comment

by:David Lee
ID: 25111113
I can't explain why you aren't getting receipts.  
0
 
LVL 11

Author Comment

by:bsharath
ID: 25111411
David in the image the first mail view is the one i used the code to send. I selected yes when it prompts
The 2nd is when i send a mail manually and select the 2 check boxes. Delivery and read receiptian.


Capture.JPG
0
 
LVL 76

Expert Comment

by:David Lee
ID: 25121405
The only possible answer would seem to be that the receipts aren't being requested.  The problem is that you've already checked that in this post (24800415) and the receipts are being requested.  If a receipt is requested, then the next possibility is that no receipt is being returned.  The code cannott control whether a receipt is returned or not.  All it can do is request one.  Maybe some other piece of code, you have a ton of it, is deleting them.  Seems unlikely since manually requested receipts arrive ok.  In short, I can't explain why you aren't getting receipts.  The whole thing makes no sense.
0
 
LVL 11

Author Comment

by:bsharath
ID: 25121424
:-(
But when i request a delivery and read receiptian via the code. I do get reply mails for delivery and read too...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 25121474
Then we are back to waht I said in this post (24811743).  
0
 
LVL 11

Author Comment

by:bsharath
ID: 25121506
David...
I just replaced my whole machine to a new set. The Outlook is freashly installed also...
Thanks for all the help on this... I shall close this Q... If you suggest...
I shall get back on the same later after few days. May be some thing might click
0
 
LVL 76

Expert Comment

by:David Lee
ID: 25121521
Sorry, I wish I had an answer.  It's simply impossible for me to understand how the system can selectively process some receipts and not others if the conditions I've outlined above are all being met.
0
 
LVL 11

Author Comment

by:bsharath
ID: 25121586
Thank U David... :-)
0
 
LVL 76

Expert Comment

by:David Lee
ID: 25140494
You're welcome.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

803 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