?
Solved

Outlook VBA Code to find sender's time zone

Posted on 2009-06-29
14
Medium Priority
?
1,808 Views
Last Modified: 2012-05-07
Is there a way to find the sender of an email's time zone? I have been able to get the sender's email address, the subject line, the time sent and time received but now need to know the time zone of the sender because we have people across the country sending us emails. Any help would be appreciated. Some of the code I have now is below.

Thanks
Lisa

EMSentDateTime = Item.SentOn
        EMSentDate = Format(Item.SentOn, "short Date")
        sentdte = EMSentDate
        EmSentTime = Format(Item.SentOn, "short time")
        senttime = EmSentTime
        EMReceivedDateTime = Item.ReceivedTime
        EMReceivedDate = Format(Item.ReceivedTime, "short date")
        recdte = EMReceivedDate
        EMReceivedTime = Format(Item.ReceivedTime, "short time")
        rectime = EMReceivedTime

Open in new window

0
Comment
Question by:LeLeBrown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 15

Expert Comment

by:badgermike
ID: 24741664
I believe office has a plug in for that:

check this link: http://www.technospot.net/blogs/outlook-plugin-to-find-senders-time-zone/

0
 
LVL 15

Expert Comment

by:badgermike
ID: 24741686
well it is not an ms plug in but a freeware - I just tested it, it works well. Not sure if it is what you want though.

0
 

Author Comment

by:LeLeBrown
ID: 24741871
Thanks badgermike. I saw the plug in but not sure I can use it since it is a work PC and I do not have rights to install software. I will give it a try. Also... there is no way - except this plug in I assume - to automatically convert the sent date and time when to the time zone you want when it comes in? Also, I was thinking, I could compare the received time to the sent time - since I pull them both - and do some sort of conversion that way. Not as accurate probably.

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 76

Expert Comment

by:David Lee
ID: 24744488
I'm leaning toward the opinion that this isn't possible.  Outlook's object model does not contain a property that stores the sender's timezone.  All message times are stored in UTC with the actual time being calculated by adding the timezone offset of the computer the message is viewed on.  I believe that plugin works by reading the internet message header which does record the timezone offset of the mail server.  That's fine if the sender is in the same timezone as the mail server.  If they aren't, then the calculation would be wrong.  That's the problem being reported by various folks at LifeHacker (http://lifehacker.com/5248961/senders-time-zone-makes-outlook-better).  Using the server time would also be a problem for anyone who is traveling or using an internet mail server.  

0
 

Expert Comment

by:Amreska
ID: 24745901
HI Blue Devil Fan,

I would like your assistance on the following question:

http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24530513.html

Thank You Very Much,

Amreska
0
 

Author Comment

by:LeLeBrown
ID: 24746068
Say you have something like below in the header. I want the time Mon, 22 Jun 2009 00:01:02 -0500 (marked with * below) as senton date and time. I do not get that date and time however, I get Mon, 22 Jun 2009 01:01:00 when I use the VBA function to get sent time. Does anyone know why I would be getting the wrong date and time?

Received: from notesgate01.agency.gov (170.97.200.48) by
 ELAN.exh.prod.agency.gov (10.2.2.31) with Microsoft SMTP Server id
 8.1.340.0; Mon, 22 Jun 2009 01:04:01 -0400
Received: from HORL.agency.gov (horl.agency.gov [170.97.202.58])      by
 notesgate01.agency.gov (8.13.8+Sun/Switch-3.2.0) with ESMTP id nxxxjuiijjjjj
      for <SpecificEmail@agency.gov>; Mon, 22 Jun 2009 01:03:52 -0400 (EDT)
Received: from mail.sender.org (HELO mail.sender.net) ([66.99.87.35])  by
 HOR.agency.gov with SMTP; 22 Jun 2009 01:02:47 -0400
Received: from sender1.sender.net ([10.1.200.48]) by mail.sender.net
 (SAVSMTP 3.1.0.29) with SMTP id Mxxx2009622 for
 <SpecificEmail@agency.gov>; Mon, 22 Jun 2009 00:02:03 -0500
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-Class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/mixed;
      boundary="----_=_NextPart_001_01C9F2F6.70C2D8D8"
Subject: Application
Date: Mon, 22 Jun 2009 00:01:02 -0500   *************
Message-ID: <7869BED3399067678338908675523409kihy78oipuyy@sender1.sender.net>
X-MS-Has-Attach: yes
X-MS-TNEF-Correlator:
Thread-Topic: Application
Thread-Index: A78uoiyT90872IOerio0234589oipuiytp098
From: "Sender1" <Sender1@sender.org>
To: <SpecificEmail@agency.gov>
Return-Path: sender1@sender.org
X-MS-Exchange-Organization-SCL: 0
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24748310
The time is adjusted based on the timezone the computer viewing the message is in.  My guess is that you must be one timezone away from the sender.  That would explain the one hour time difference you see when you look at the message.  It was Mon, 22 Jun 2009 00:01:02 in the sender's timezone which would be 01:01:00 if I'm right about you being one timezone away.
0
 

Author Comment

by:LeLeBrown
ID: 24749530
BlueDevilFan,

Yes. I am one time zone away from this sender. There are mulltiple senders however, so my program has to be able to read in each one without knowing ahead of time what time zone they are in. The problem is, when I do SentOn, I do not get the actual time sent, I seem to be getting the time the exchange server transferred it or something. The header (which is the internet email header) shows the correct time and the one I want but I can't seem to get it with the SentOn function. I have read you can use CDO to get the internet header and there is also a plug in. Both of which I can't do because I do not have rights to the exchange server or even my PC here at work to load software. Are those the only ways to read in (using VBA) the email internet header?

Thanks
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24750262
"I do not get the actual time sent"
That's incorrect.  You do get the actual time the message was sent, Outlook is simply adjusting it for your timezone.  All the times you see in an Outlook message are UTC times that have been adjusted to the timezone your computer is in.  

"I have read you can use CDO to get the internet header"
Yes, you can use CDO if you have Outlook 2003 or earlier.  Outlook 2007 can read the raw header natively.  Keep in mind that the time stamp in the header is from the server not the client.  If the client and server are not in the same timezone, then the time will be wrong.  For example, the company I work for runs its email on the US east coast.  Clients are spread all across the US.  If you looked at a message header sent by one of our employee in San Francisco it would show the Eastern time zone even though the employee, the sending Outlook client, is in the Pacific time zone.  Outlook does not appear to keep track of the sender's time zone.  

With this in mind, if you want to read the time information from the internet header, then I can help with that.  I'll need to know what version of Outlook you're using.
0
 

Author Comment

by:LeLeBrown
ID: 24750572
Hi BlueDevilFan,

I am on Outlook 2007 and I would like to read the time information from the internet header.

Thanks for your help.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 2000 total points
ID: 24750868
The code in the snippet will return the date line from the internet header.  It will only work for Outlook 2007.  Call the function like this

    strTime = GetTimeFromHeader(MailItem)

    Where: MailItem is an Outlook message
Function GetTimeFromHeader(olkMsg As Outlook.MailItem) As String
    Dim olkProp As Outlook.PropertyAccessor, strHeader As String, arrItems As Variant, varLine As Variant
    Set olkProp = olkMsg.PropertyAccessor
    strHeader = olkProp.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x007D001E")
    arrItems = Split(strHeader, vbCrLf)
    For Each varLine In arrItems
        If Left(varLine, 5) = "Date:" Then
            GetTimeFromHeader = Mid(varLine, 7)
        End If
    Next
    Set olkProp = Nothing
End Function

Open in new window

0
 

Author Comment

by:LeLeBrown
ID: 24755035
Thanks BlueDevilFan. I tried this and it works!!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24755067
You're welcome.
0
 

Author Comment

by:LeLeBrown
ID: 24765043
I hope I can tag this on to this thread... it is for the same code.
I had a For Each Item in Subfolder.items  ( subfolder is a subfolder to main inbox) and it worked ok but didn't process all of the items. It didn't error but it just stopped and my message box saying processing complete came up. I saw some code to change it to For i = icount To 1 Step -1 but how to I reference the subfolder that I want it to go through? The For each I was using told it what folder to look in but this new For i does not.

Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Changing a few Outlook Options can help keep you organized!
This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

762 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