Date disappears when pasting Outlook inbox into Excel

[Leonard1]

Hi all,

I'm trying to copy my Outlook inbox into Excel as I need to compare dates that emails were received. Exporting the Outlook inbox doesn't carry across the date field so I've discovered that simply copying and pasting the inbox works better.

The difficulty I'm having is that it copies the 'Received' field across simply as a time. For example in Outlook it shows as 'MON 06/01/2014 10:41' but only copies across to Excel as '10:41'. The overall inbox looks as below in Excel.

From</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Subject</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Received</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Size</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Jones, Jim</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	One</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	10:41</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	12 KB</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Smith, Steve</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Two</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	10:23</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	136 KB</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Ball, Eddie</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Three</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	09:59</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	9 KB</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>

<TBODY>
</TBODY>

I've tried formatting the cell to show a different number type but it juts changes it to random numbers and then reverts back to this each time I repaste.


Can anyone help me please?

 

[Norie]

Have you tried a date/time format, eg dd/mm/yy hh:mm?

 

[Leonard1]

Yes I've amended the corresponding cells to 'dd/mm/yy hh:mm' and it then displays it as '00/01/1900 10:41'. Then when repasting the data into these cells set this way it just reverts back to displaying it as time only.

 

[s.ridd]

The VBA code below will write out the information below from every item in your Inbox onto the active sheet.

Sub CopyEmailDetails()

Dim outapp As Object 'Outlook.Application
Dim outFldr As Object 'Outlook.Folder
Dim outItm As Object, outItms As Object 'Outlook.Items
Dim l As Long


Set outapp = CreateObject("Outlook.Application")
Set outFldr = outapp.Session.GetDefaultFolder(6) '6 = olFolderInbox change to the relevant fodler if required
Set outItms = outFldr.Items


    l = outItms.Count + 1
    Cells(1, 1) = "From"
    Cells(1, 2) = "Subject"
    Cells(1, 3) = "Received"
    Cells(1, 4) = "Size"


    For Each outItm In outItms
        Cells(l, 1) = outItm.SenderEmailAddress
        Cells(l, 2) = outItm.Subject
        Cells(l, 3) = outItm.ReceivedTime
        Cells(l, 4) = outItm.Size & " KB"
        l = l - 1
    Next


outapp.Quit
Set outItms = Nothing
Set outFldr = Nothing
Set outapp = Nothing


End Sub

Hope this helps

Simon