CODEWITHSUNDEEP
excelvbaoutlookdate-format
Jordan Wall
Jordan Wall

Reputation: 95

Date format changing on VBA export from Outlook to Excel

This is a bit of strange one that's been doing my head in for the last few days.

I've been updating a macro in Outlook which exports details over to Excel.

So far the macro has been working fine, happily exporting the sender, subject and dates sent and received without a problem.

I've added in a bit so that I can capture the time and date if the email has been replied to/forwarded, but that's where things are going wrong.

When running the code, if I put a Debug.Print on the variable holding the reply/forwarded date it prints out in the correct format (dd/mm/yyyy hh:mm:ss), but when it pops over to excel for some reason it's being entered as mm/dd/yyyy hh:mm:ss (but only for dates where the month is <= 12).

I've checked the computer's regional settings (in fact I've tried it on 2 different machines) and can't see anything that would cause the change.

The code I'm using is below, does anyone have any ideas?

'this is the part that exports to Excel
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = GetLastVerb(msg)
Debug.Print GetLastVerb(msg)

Public Function GetLastVerb(olkMsg As Outlook.MailItem) As String
Dim intVerb As Integer
intVerb = GetProperty(olkMsg, "http://schemas.microsoft.com/mapi/proptag/0x10810003")
Select Case intVerb
    Case 102
        Debug.Print ("Reply to Sender")
        GetLastVerb = GetLastVerbTime(olkMsg)
    Case 103
        Debug.Print ("Reply to All")
        GetLastVerb = GetLastVerbTime(olkMsg)
    Case 104
     Debug.Print ("Forward")
        GetLastVerb = olkMsg.ReceivedTime
    Case 108
     Debug.Print ("Reply to Forward")
        GetLastVerb = GetLastVerbTime(olkMsg)
    Case Else
     Debug.Print ("Unknown")
        GetLastVerb = "Not replied to"
End Select
End Function

Public Function GetProperty(olkItm As Object, strPropName As String) As Date
Dim olkPA As Outlook.PropertyAccessor
Set olkPA = olkItm.PropertyAccessor
GetProperty = olkPA.UTCToLocalTime(olkPA.GetProperty(strPropName))
Set olkPA = Nothing
End Function

Public Function GetLastVerbTime(olkItm As Object) As Variant
GetLastVerbTime = GetDateProperty(olkItm, "http://schemas.microsoft.com/mapi/proptag/0x10820040")
End Function

Public Function GetDateProperty(olkItm As Object, strPropName As String) As Date
Dim olkPA As Outlook.PropertyAccessor
Set olkPA = olkItm.PropertyAccessor
GetDateProperty = olkPA.UTCToLocalTime(olkPA.GetProperty(strPropName))
Set olkPA = Nothing
End Function

Upvotes: 2

Views: 1892

Answers (1)

JosieP
JosieP

Reputation: 3410

it's because you are returning a string and vba will assume US format if possible-perhaps use

Dim sTemp as string
sTemp = GetLastVerb(msg)
if isdate(stemp) then 
   rng.Value = cdate(sTemp) 
else
   rng.value = sTemp
end if

Upvotes: 1

Related Questions