none
Outlook 2010 - 'Flag Completed Date' format RRS feed

  • Question

  • Hi,

    I am trying to export outlook emails to excel using excel VBA. The format that I have choosen for field 'Flag Completed Date' in Outlook is 'dd-mm-yyyy hh:mm'. This format is showing correctly in outlook but when exported to excel, the format is changing to 'dd-mm-yyyy' with no time. When I tried to change the date format to 'dd-mm-yyyy hh:mm' manually in excel, the date shows 21-01-2015 00:00. I have debug the code, the variable is picking only date and ignoring time. I have included 'SentOn' and UDF date fields in the code, those date fields are exported to excel in the correct format.

    I am not sure if this is outlook issue or excel. Please advise.

    Regards,

    KPS

    Thursday, January 22, 2015 6:38 PM

All replies

  • Hi,

    According to the description, this questions is more related to Office Development, I've moved this thread to General Office Development forum, you can get more dedicated support from the experts here.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Melon Chen
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Friday, January 23, 2015 7:56 AM
  • Hello KPS,

    What code do you use? Could you please be more specific?

    Did you try to use any low-level property viewer tool such as MFCMAPI or Outlook Spy for exploring property values? Does the Outlook property contains the required information?

    Friday, January 23, 2015 9:57 AM
  • Hi Eugene,

    Below is the code that I have used.

    Sub InboxToExcel()
    
        Dim objOL As Outlook.Application
        Dim objNS As Outlook.Namespace
        Dim objInbox As Outlook.Folder
        Dim objTable As Outlook.Table
        Dim objRow As Outlook.Row
        Dim objMsg As Outlook.MailItem
        Dim objWB As Excel.Workbook
        Dim objWS As Excel.Worksheet
        Dim objRange As Excel.Range
        Dim strFind As String
        Dim strProps As String
        Dim arr() As String
        Dim val As Variant
        Dim i As Integer
        Dim intRow As Integer
    
        strProps = _
          "SenderName,To,Subject,SentOn,ReadReceiptRequested"
        Set objOL = Application
        Set objNS = objOL.Session
        Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
        Set objTable = objInbox.GetTable
    
        Set objWB = GetExcelWB()
        Set objWS = objWB.Sheets(1)
        objWS.Name = "Inbox"
        arr = Split(strProps, ",")
        intRow = 1
        For i = 0 To UBound(arr)
            objWS.Cells(intRow, i + 1) = arr(i)
            objTable.Columns.Add arr(i)
        Next
        Set objRange = objWS.Range _
                       (objWS.Cells(1, 1), objWS.Cells(1, i + 1))
        objRange.Font.Bold = True
        Do Until objTable.EndOfTable
            intRow = intRow + 1
            Set objRow = objTable.GetNextRow
            For i = 0 To UBound(arr)
                val = objRow(arr(i))
            Select Case VarType(val)
               Case vbDate
                    val = DateToExcel(val)
               Case vbBoolean
                    val = YesNoToString(val)
            End Select
                objWS.Cells(intRow, i + 1) = val
            Next
        Loop
        For i = 1 To (UBound(arr) + 1)
            objWS.Columns(i).EntireColumn.AutoFit
        Next
        objWS.Application.Visible = True
        objWS.Activate
        Set objOL = Nothing
        Set objNS = Nothing
        Set objRow = Nothing
        Set objWB = Nothing
        Set objWS = Nothing
        Set objRange = Nothing
    End Sub
    
    Function DateToExcel(propVal)
        Dim dteDate 'As Date
        If IsDate(propVal) Then
            dteDate = CDate(propVal)
            If dteDate = #1/1/4501# Then
                DateToExcel = Null
            Else
                DateToExcel = dteDate
            End If
        End If
    End Function
    Function YesNoToString(propVal)
        If propVal = "True" Then
            YesNoToString = "Yes"
        Else
            YesNoToString = "No"
        End If
    End Function

    Wednesday, January 28, 2015 6:30 AM
  • Hi kps995,

    Base on my test in excel 2013 with outlook 2013, it works fine. I will test it in outlook 2010.

    Is the value of propVal parameter correct when debug the code?

    When/where does it change the date? (ignore time)

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 30, 2015 9:53 AM
    Moderator