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

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

LVL 11
bsharathAsked:
Who is Participating?
 
David LeeConnect With a Mentor Commented:
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
 
David LeeCommented:
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
 
bsharathAuthor Commented:
Thanks David...
Sent you an email with my This outlooksession code... As you know there are other codes in there...
0
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

 
bsharathAuthor Commented:
Thanks David...
Sent you an email with my This outlooksession code... As you know there are other codes in there...
0
 
bsharathAuthor Commented:
0
 
bsharathAuthor Commented:
Hi David...
Any views...
0
 
David LeeCommented:
Open and close both receipts.  Does that update the tracking information?
0
 
bsharathAuthor Commented:
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
 
David LeeCommented:
"Only the one i sent manually i get the tracking option"
How were the other messages sent?
0
 
bsharathAuthor Commented:
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
 
David LeeCommented:
Sorry, but I'm lost.  I've no idea what you're talking about.
0
 
bsharathAuthor Commented:
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
 
David LeeCommented:
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
 
bsharathAuthor Commented:
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
 
David LeeCommented:
And all the sent items are in the default sent items folder, right?
0
 
bsharathAuthor Commented:
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
 
David LeeCommented:
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
 
bsharathAuthor Commented:
David when i open the sent mail Via the script. I dont get any option that shows the tracking...
0
 
bsharathAuthor Commented:
David when i open the sent mail Via the script. I dont get any option that shows the tracking...
0
 
David LeeCommented:
I understand, but that's not what I asked you to check.  
0
 
bsharathAuthor Commented:
>>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
 
bsharathAuthor Commented:
>>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
 
David LeeCommented:
"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
 
bsharathAuthor Commented:
David...
Attached is the image
I cannot see an option as "Options" in the ribbon
Can you suggest please...
Capture.JPG
0
 
David LeeCommented:
This looks like a new message.  Open the item you sent.
0
 
bsharathAuthor Commented:
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
 
bsharathAuthor Commented:
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
 
David LeeCommented:
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
 
bsharathAuthor Commented:
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
 
David LeeCommented:
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
 
bsharathAuthor Commented:
David below is how the window looks...
0
 
bsharathAuthor Commented:
David below is how the window looks...

Capture.JPG
0
 
David LeeCommented:
Check the 3rd box, "After processing move receipts to", and leave the destination folder set to Deleted Items.
0
 
bsharathAuthor Commented:
Done... I sent an email i did get the read and delivery receipts but tracking not created...
0
 
bsharathAuthor Commented:
Done... I sent an email i did get the read and delivery receipts but tracking not created...
0
 
David LeeCommented:
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
 
bsharathAuthor Commented:
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
 
bsharathAuthor Commented:
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
 
bsharathAuthor Commented:
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
 
bsharathAuthor Commented:
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
 
David LeeCommented:
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
 
David LeeCommented:
Don't reinstall Outlook yet.
0
 
bsharathAuthor Commented:
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
 
David LeeCommented:
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
 
bsharathAuthor Commented:
Oh ok David...
0
 
David LeeCommented:
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
 
bsharathAuthor Commented:
No David i dont think So...
The only one that you gave e yesterday to flag sent mails if needed...
No inbuilt rules
0
 
bsharathAuthor Commented:
No David i dont think So...
The only one that you gave e yesterday to flag sent mails if needed...
No inbuilt rules
0
 
bsharathAuthor Commented:
Hi David any views on this ....
0
 
David LeeCommented:
Are receipts still not working on your system?
0
 
bsharathAuthor Commented:
Yes david still the same issue....
0
 
David LeeCommented:
I can't explain why you aren't getting receipts.  
0
 
bsharathAuthor Commented:
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
 
David LeeCommented:
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
 
bsharathAuthor Commented:
:-(
But when i request a delivery and read receiptian via the code. I do get reply mails for delivery and read too...
0
 
David LeeCommented:
Then we are back to waht I said in this post (24811743).  
0
 
bsharathAuthor Commented:
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
 
David LeeCommented:
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
 
bsharathAuthor Commented:
Thank U David... :-)
0
 
David LeeCommented:
You're welcome.
0
All Courses

From novice to tech pro — start learning today.