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
378 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
Hi David...
Any views...
0
 
LVL 76

Expert Comment

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

Author Comment

by:bsharath
Comment Utility
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
Comment Utility
"Only the one i sent manually i get the tracking option"
How were the other messages sent?
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
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
Comment Utility
Sorry, but I'm lost.  I've no idea what you're talking about.
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
And all the sent items are in the default sent items folder, right?
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I understand, but that's not what I asked you to check.  
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
>>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
Comment Utility
>>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
Comment Utility
"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
Comment Utility
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
Comment Utility
This looks like a new message.  Open the item you sent.
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
David below is how the window looks...
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 11

Author Comment

by:bsharath
Comment Utility
David below is how the window looks...

Capture.JPG
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
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
Comment Utility
Done... I sent an email i did get the read and delivery receipts but tracking not created...
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Don't reinstall Outlook yet.
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
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
Comment Utility
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
Comment Utility
Oh ok David...
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi David any views on this ....
0
 
LVL 76

Expert Comment

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

Author Comment

by:bsharath
Comment Utility
Yes david still the same issue....
0
 
LVL 76

Expert Comment

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

Author Comment

by:bsharath
Comment Utility
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
Comment Utility
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
Comment Utility
:-(
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
Comment Utility
Then we are back to waht I said in this post (24811743).  
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
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
Comment Utility
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
Comment Utility
Thank U David... :-)
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You're welcome.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

10 Experts available now in Live!

Get 1:1 Help Now