Is there an Outlook Add-In that can tell you the time or time zone for a contact based on their area code?
I'm not aware of any addins, but it's a great idea. Until then, adding a field to contacts to record the time zone is one option, although you'd need to add the time zone yourself.
You can use a macro to grab the postal code and look up the time zone. While it's not perfect, it is useful for anyone who wants to identify the time zone a person lives in.
I think using the postal code would be better than the phone number's area code, at least here in the US. Thanks to number portability, the person may not be living in the same region the area code is assigned to.
The following two URLs work for US addresses. Any site that includes the zip code in the URL can be used. To use, replace the number with the zip code string. Melissadata's URL accepts the full telephone number or just the area code, if you prefer to use it.
https://www.melissadata.com/lookups/ZipCityPhone.asp?InData=12345&submit=Search
//www.zip-info.com/cgi-local/zipsrch.exe?tz=tz&zip=12345&Go=Go
At this time, the code opens a web page containing the time zone and other information.
I'm working on scraping the page and grabbing just the time zone from the page, then displaying it in a message box, writing it to the notes field, or to a custom field.
To use, select a contact and run the macro.
Get the time zone macro
Sub GetContactTimeZone()
Dim strURL As String
Dim oApp As Object
Dim strAddress As String
Set oApp = CreateObject("InternetExplorer.Application")
If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
Set oContact = ActiveExplorer.Selection.Item(1)
strAddress = oContact.MailingAddressPostalCode
ReplaceSpaces strAddress
strURL = "//www.melissadata.com/lookups/ZipCityPhone.asp?InData=" & strAddress & "=Search"
oApp.Navigate (strURL)
oApp.Visible = True
'wait for page to load before passing the web URL
Do While oApp.Busy
DoEvents
Loop
End If
Set oApp = Nothing
End Sub
Private Sub ReplaceSpaces(strAddress As String)
strAddress = Replace(strAddress, " ", "-")
End Sub
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More Information
More information as well as screenshots are at How to use the VBA Editor
Unable to see my query here. Did you receive my query? If yes, can I have update?
comments stay in moderation until i have time to answer the questions, otherwise i tend to lose them.
Hi Diana,
I have couple of queries:
1. I need to retrieve the exact time zone of each recipients (not a lookup in a web page) to do some calculations based on Time Zone in VBA. Is there a way to get this?
2. I want to fire ItemSend event only for meeting request related items like Skype Meeting, Appointment, Meeting. Currently I placed this event in ThisOutlookSession. How can we do this?
3. Once we successfully code for a desired outlook requirement, and if we want to host it in many systems, what is the best way of doing it? Macro code has to place in each and every system? If yes, can we do it with a one click executable like add-in feature?
Can you please suggest?
Appreciate your quick help on this.
1. where are you going to find the TZ? If you have the time zone, you can work with it, but outlook doesn't store the time zone anywhere - you would need to either look it up somewhere or create a custom time zone field to store it.
2. You'll use if statements if you want to limit when it runs. If you only want it to run for meetings, use something like if typeof(item) = meetingitem then...
3. compiling it into an addin is the best way to install it on multiple systems.
Thanks for the quick response. Here is further queries on above 3: #1: My requirement is to show a message with color bar, when we are trying to send a meeting invite to recipients who are out of business hours. I was able to handle color bar with categories + added confirmation box on send click via Outlook Itemsend event for a meeting item. But recipients could be in different time zone (to know their business hours), I am looking for ways to resolve this. In Outlook 2016 options, we have an option to select time zones (under calendar tab), when we change this tz, then, its reflecting in the "Open Contact Card". I am not sure whether we can get the details of this? If this is not possible, then, we need to lookup as shown in this article, but how to get only tz value (instead of showing the entire web page, which is not needed in my case) back to the outlook VBA code (for later calculations)? Sample code will help me. #2: This is resolved by adding If condition Item.Class = olMeetingRequest on ItemSend. Thanks. #3: Can you please help me on steps to create "ThisOutlookSession"… Read more »
#1 i don't have any code samples - you'll probably need to use a different site and will need to use aspx or a web service to grab the time zone.
#3 if you are just passing out the macros, you don't need to do anything fancy - but everyone will need to install the macros. if you turn it into an addin, then it can be installed using logon scripts or with a button click. This requires visual studio.
#4 you get the start time from the start field but i'm not sure why you need it when you send messages - the start time would be set before its sent.
#5 excel vba code samples can usually be hijacked to work in outlook. depending on what you need it for - datediff might work - DateDiff("n", "6:45", "7:30") / 60 - this would get you the minutes (use variables for the time).
Thanks for the response Diane.
I am able to get city of a recipient (as below) but not the country/region. How to get country details via VBA.
recipient.AddressEntry.GetExchangeUser.City
It doesn't look like country is exposed -
https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.outlook.exchangeuser_members.aspx
you can get officelocation (assuming its filled in) or state.
One more:
6: In this article you are retrieving the address based on contactitem (as shown below), but I should retrieve the address of the recipients in the meeting request (on meeting request send event). How can we get this details? I checked the item properties (in debug mode of ItemSend event) and I didn't find any address for the recipients. Can you please suggest.
If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
Set oContact = ActiveExplorer.Selection.Item(1)
strAddress = oContact.MailingAddressPostalCode
Sample code for all my queries will really help.
This macro shows how to look up a contact using the email address - https://www.slipstick.com/developer/categorize-messages-using-contact-category/
This is exactly what I was looking for, well, almost exactly. I can't wait to for the update that gets the value instead of getting the web page. Thanks again!!!