+ Reply to Thread
Results 1 to 34 of 34

Capture in time and out time of email sent via outlook

  1. #1
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Unhappy Capture in time and out time of email sent via outlook

    Hi All,

    I work for a organisation where we have multiple mailboxes mapped to microsoft outlook.

    I would like to know whether it is possible to create a macro which should capture the time of receipt of the email, subject line and the senders information in Excel (email received in secondary mailbox not the primary one)

    Vice Versa Macro should capture the time of email sent, senders name, receipent name and subject (which we sent from Secondary Mailbox to outside world)

    Any help in this regard will appreciated

    Thanks-Kamal

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    The following macro will create a list in the active worksheet of emails received from the specified subfolder...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Hello Forum Guru,

    Thank you for providing the code however i have few questions in this regard

    First question: As per my earlier post I said i use multiple Mailboxes so how will this Marco would would recognize which Mailbox it has to select
    Second question: As mentioned in the above code you have requested to change the subfolder, since poor in coding can you elborate on this comment

    Thank you for all your help in this regard

  4. #4
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    First question: As per my earlier post I said i use multiple Mailboxes so how will this Marco would would recognize which Mailbox it has to select
    Second question: As mentioned in the above code you have requested to change the subfolder, since poor in coding can you elborate on this comment
    GetDefaultFolder(olFolderInbox) refers to your Inbox.

    Folders("MySubFolder") refers to a subfolder within your Inbox.

    So, for example, if you receive your emails of interest in a subfolder within your Inbox and it's called "Project A", replace "MySubFolder" with "Project A".
    Last edited by Domenic; 01-27-2012 at 08:19 AM.

  5. #5
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Hello Domenic,

    Thank you very much for the explanation and providing the macro.

    As mentioned in my first post there are multiple mailboxes mapped to my email.Is there a possiblity that I can use this for this type of mailboxes and also is there a possibility that I can retrive the same information from a sub folder in PST

    Thanks for all your support

  6. #6
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    Let's say that you created a personal folder and named it "2012 Emails", and that you also created a subfolder and called it "Project X". Simply replace...

    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Hello Domenic,

    Sorry I am getting back to you again on this

    In my inbox I have many subfolders, is there a possibility that I can get information for all these sub folders using this macro

    Please advise

  8. #8
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    The following macro will list in the active worksheet the name of each folder within your InBox, along with number of items for each one...

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Hello Domenic,

    I think my question in earlier post might be little confusing because based on the code you provided now it gives the below out

    Folder name and no of emails in it.

    Actually if you can look into the first macro you provided it give information like from,Subject and Received date and time however it was capturing data from one subfolder of Inbox, my query is, is it possible to capture from,Subject and Received date infromation from all the subfolders, not a single folder.

    I am really sorry that I have confused you

    Thanks

  10. #10
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    Do the subfolders in your Inbox also contain subfolders? And, if so, do you want the details for those subfolders as well? Or are you only interested in the folders within your Inbox?
    Last edited by Domenic; 02-17-2012 at 12:03 PM.

  11. #11
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Yes Domenic few sub folders have sub folders in them, yes I would need the complete data.

  12. #12
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    Place the following code in a regular module, and run the macro called 'test'...

    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    A small modification in case no mail exists in the specified folder and/or its subfolders. Replace...

    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Thank you for providing the code however there is some an error message pops up when I execute the test macro
    Run-Time Error:438 "Object Doesn't support this Prpoerty or Method"

    Please advise

  15. #15
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    Sorry, the .Sender property is only available on Excel 2010. Therefore, we'll need to use the .SenderName property instead. Replace...

    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Hello Domenic,

    I have replaced the code with the new code however still the same error is popping up.

    As instructed by you I opened a new Module copy pasted the macro however I am not able to execute.I am attaching the spreadsheet for reference

    I have both 2007 and 2010 installed on my PC
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    I've looked at your sample file, and ran the code. It seemed to work fine. I didn't get an error. Which line of code gets highlighted when you get the error?

  18. #18
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    You asked me to change the sender as SenderName , I am getting the error on that line.

  19. #19
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    Try replacing...

    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Hello Domenic,

    Thank you very much, it's works excellent.

    One last question.Let's say I am running the macro for the first time at 16:00 IST and data is exported to excel and after that at reqular intervals (could be half an hour each Interval) I will execute this macro. Just wanted to check is there a possibility that instead of capturing the whole data is there a possibility that macro should capture only new items not the old one's.What I am trying to say is I get emails at regular intervals and when i first execute the macro lets say there are 100 items macro will pull all these items to excel and again after half an hour If I re run the macro it should capture the new items only and paste them below to the existing once.

    once again thanks for all your help

  21. #21
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    As you know, every time the macro is run, it clears the worksheet and lists all emails. So, in effect, the new emails get listed. I guess an alternative would be to only loop through unread emails and list them. But, in this case, if the macro is run again before the unread emails are read, they will be listed again. Or, if the unread emails are read before running the macro, they won't get listed at all. So this seems somewhat tricky. I guess yet another alternative would be to have the macro move emails from the InBox to a subfolder once the emails have been listed on the worksheet. But this doesn't seem to appealing, at least as far as I'm concerned. I'd suggest using the current macro as is.

  22. #22
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Ok Domenic I got your point but still wanted to check is there a possibility of getting this done bcoz I want to have a track of emails

  23. #23
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    In this case, since you're only interested in new/unread emails, there's no need to search through the subfolders within the Inbox, correct? So, we only need to search through unread emails in the Inbox, correct?

  24. #24
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Thank you for coming back on this.

    My whole intention is Let's say when i first execute it, it will export data from all subfolders to Excel say time as X and when I execute the marco at Y time then it should capture the new emails information only and paste them below to existing data.there are chance of emails getting moved to subfolders also it should search all the folders again.

    Please let me know if you need more information in this regard

  25. #25
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    The following code assumes that you're starting with a blank worksheet. The first time that it is run, it inserts column headers, and lists all emails, whether read or unread. For subsequent times, it only adds unread emails to the existing list.

    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Thank you Domenic for this but the condition you have mentioned above that It will capture on unread email, that is again a challenge for me.

    Is there a possibility that both unread and read email information is captured instead of only unread emails getting captured.

  27. #27
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    Each time that the macro runs, it first determines the last used row. If the last used row is Row 1, then this means that there's no data, so it inserts the columns headers, formats Column D (received time), and then it lists all emails (read and unread emails) from the Inbox and its subfolders. If the last used row is greater than Row 1, then data already exists, so then it simply lists all new emails (read and unread emails) from the Inbox and its subfolders.

    Please Login or Register  to view this content.

  28. #28
    Forum Contributor
    Join Date
    04-03-2011
    Location
    India
    MS-Off Ver
    Excel 2015
    Posts
    122

    Re: Capture in time and out time of email sent via outlook

    Can i try this for " inbox " and subfolder "Sent item ". pls comment

  29. #29
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    For the InBox, replace...

    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.
    For the Sent Mail, try...

    Please Login or Register  to view this content.
    Or, did you want a list of items from both the InBox and Sent Mail altogether? If so, try...

    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    06-03-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Capture in time and out time of email sent via outlook

    Hello Domenic,

    Firstly I would like to say sorry for responding very late as I was not well so I I did not had a chance to look into this.

    Thank you very much for all your help however Still I can see that there is one problem in this.It captures the information perfectly however if the subject is missing from the subject line then it copies the email infromation twice in the excel spreadsheet.

    Can you have look in this

  31. #31
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Capture in time and out time of email sent via outlook

    It shouldn't matter whether an email has no subject. I've tested the code with emails that have no subjects and it seems to work fine. There's no duplication. What are the dates and times (including seconds) for both of the duplicate emails?

  32. #32
    Registered User
    Join Date
    06-15-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Capture in time and out time of email sent via outlook

    Hi Domenic, i think there's a time difference in excel and outlook? because when i used the code that you posted that captures new mails in inbox and sent folders, new mails with the same minutes still gets captured hence the duplicate. and i would like to ask also if there's a way to capture other folders also - in our office mails, we import Business Addresses in our outlook so mails there could be captured also. the code that you provided only captures my inbox but not the inbox of the Business Address

  33. #33
    Registered User
    Join Date
    04-24-2016
    Location
    Mumbai,India
    MS-Off Ver
    Version 10
    Posts
    1

    Re: Capture in time and out time of email sent via outlook

    olFolderInbox variant not defined.

  34. #34
    Registered User
    Join Date
    05-05-2016
    Location
    India
    MS-Off Ver
    2010
    Posts
    1

    Re: Capture in time and out time of email sent via outlook

    Hi Guys,

    I have worked on a similar macro which captures an email from the inbox and tracks the responses like Reply, reply to all and forward. While this works perfectly well for personal inbox, shared emailbox would not pick any of the emails sent from it.

    I have tried with many forums and experts but none are able to crack it. Is there someone who can help in this regard.

    ThanksKKarthik

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1