Calculate ageing of outlook mail items

Post here if you need help with creating a Graphical User Interface in Python.

Calculate ageing of outlook mail items

Postby Premanshu999 » Sun Aug 28, 2016 3:52 pm

Hi Experts,

I am trying to develop an application in wxPython in which I will fetch all e-mails into a listctrl and sort them based on their remaining SLA (3 days) for further action. For this I will calculate the ageing of the items in the mailbox by deducting the receivedTime from current time. Below is my complete code:-

Code: Select all
from datetime import datetime
import time
import win32com.client
import wx

class MainFrame(wx.Frame):
    def __init__(self, parent, myTitle):
        super(MainFrame, self).__init__(parent, title = myTitle, size = (1300,600))
    #set the background color
    self.index = 0
    self.SetBackgroundColour((230, 230, 250))

    #create fonts
    FrameLabels_Font = wx.Font(10, wx.DECORATIVE, wx.NORMAL, wx.BOLD, True, u'Bookman Old Style')
    listItems_Font = wx.Font(8, wx.DECORATIVE, wx.NORMAL, wx.NORMAL, True, u'Bookman Old Style')

    #set the frame
    self.frame_Controls = wx.StaticBox(self, label="Controls:-", pos=(10, 275), size=(300, 250), style=wx.RAISED_BORDER)
    self.frame_Controls.SetBackgroundColour((230, 230, 250))
    self.frame_Summary = wx.StaticBox(self, label="Stats:-", pos=(10, 10), size=(300, 250), style=wx.RAISED_BORDER)
    self.frame_Summary.SetBackgroundColour((230, 230, 250))
    self.frame_Queue = wx.StaticBox(self, label="Work Items:-", pos=(320, 10), size=(950, 515), style=wx.RAISED_BORDER)
    self.frame_Queue.SetBackgroundColour((230, 230, 250))

#controls for queue frame
    LblRegion = wx.StaticText(self.frame_Queue, -1, 'Region:-', (20, 25), (150, 25))
    LblMailbox = wx.StaticText(self.frame_Queue, -1, 'Mailbox:-', (190, 25), (150, 25))
    LblSortBy = wx.StaticText(self.frame_Queue, -1, 'Sort By:-', (360, 25), (150, 25))
    LblSortOrder = wx.StaticText(self.frame_Queue, -1, 'Sort Order:-', (530, 25), (150, 25))

    rdoUnallocated = myRadioButton(self.frame_Queue, 'UnAllocated', (700,25), (110,25))
    rdoAllocated = myRadioButton(self.frame_Queue, 'Allocated', (820, 25), (110, 25))

    rgnCombo = myComboBox(self.frame_Queue,(20,60),(150,25))
    rgnCombo = myComboBox(self.frame_Queue, (190, 60), (150, 25))
    rgnCombo = myComboBox(self.frame_Queue, (360, 60), (150, 25))
    rgnCombo = myComboBox(self.frame_Queue, (530, 60), (150, 25))

    self.subList = myListCtrl(self.frame_Queue,(20,95),(910,390))
    self.subList.InsertColumn(0, 'Rush')
    self.subList.InsertColumn(1, 'Subject')
    self.subList.InsertColumn(2, 'Recevd DtTm')
    self.subList.InsertColumn(3, 'Allocated To')
    self.subList.InsertColumn(4, 'Allo. ID')
    self.subList.InsertColumn(5, 'Unique Key')
    self.subList.InsertColumn(6, 'Rem. SLA')
    self.subList.InsertColumn(7, 'Ageing')
    self.subList.InsertColumn(8, 'Duplicate')
    self.subList.InsertColumn(9, 'Actionable')
    self.subList.InsertColumn(10, 'Status')
    self.subList.InsertColumn(11, 'Start DtTm')
    self.subList.InsertColumn(12, 'Query DtTm')
    self.subList.InsertColumn(13, 'Hold DtTm')
    self.subList.InsertColumn(14, 'Continue DtTm')
    self.subList.InsertColumn(15, 'Final Status')
    self.subList.InsertColumn(16, 'Final Status DtTm')
    self.subList.InsertColumn(17, 'Final Status Date')

    #update the listctrl
    getConn = OutlookConnection()
    messages = getConn.fetchUnallocated()
    for msg in messages:
        self.subList.InsertStringItem(self.index, '')
        self.subList.SetItemFont(self.index, listItems_Font)
        self.subList.SetStringItem(self.index, 1, msg.subject)
        self.subList.SetStringItem(self.index, 2, str(msg.receivedtime))
        if msg.Importance == 2:
            self.subList.SetStringItem(self.index, 0, 'Y')
            self.subList.SetItemBackgroundColour(self.index, (255,0,0))
        #tm ="%m-%d-%Y %H:%M:%S") - msg.receivedtime
        #tm = - msg.receivedtime
        tm = time.mktime( - msg.receivedtime
        #tm = - datetime.fromtimestamp(time.mktime(msg.receivedtime))
        #self.subList.SetStringItem(self.index, 7, str("%m-%d-%Y %H:%M:%S")))
        self.subList.SetStringItem(self.index, 7, str(tm))

    #add the menu here

    #display the frame

#create the AddMenu def
def AddMenu(self):
    menuBar = wx.MenuBar()
    #file menu
    File_btn = wx.Menu()
    #sub menu items of file menu
    Logout_btn = File_btn.Append(wx.ID_EXIT,'&Logout', 'Close the application')
    #now put the File_btn to the menuBar
    menuBar.Append(File_btn, '&File')
    #set the menu bar in the application main frame
    #now bind the code which will run upon clicking the Logout button
    self.Bind(wx.EVT_MENU, self.Quit, Logout_btn)

#def the self.Quit process
def Quit(self,x):

#class for querying database
class dbQuery():
#method for getting the list of regions
    def RegionsList(self):
        myDb = 'H:\\Python\\wxPython\\Programs\\References.accdb'
        DRV = '{Microsoft Access Driver (*.mdb)}'
        PWD = 'pw'
    # connect to db
        conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s' % (myDb))
        cur = conn.cursor()
    # run a query and get the results
        SQL = 'SELECT * FROM Regions'
        return cur.execute(SQL, self.Tname).fetchall()

class myRadioButton(wx.RadioButton):
    def __init__(self, parent, mylabel, vPosition, vSize):
        super(myRadioButton, self).__init__(parent, -1, label = mylabel, pos = vPosition, size = vSize)

class myComboBox(wx.ComboBox):
    def __init__(self, parent, lstposition, lstsize):
        super(myComboBox, self).__init__(parent, -1, value="", pos=lstposition, size=lstsize)
    #this method will be used to add items from a list to the instance of the mycombobox
    def addItem(self, Lst=[]):
        for itm in Lst:

class myListCtrl(wx.ListCtrl):
    def __init__(self,parent, vPosition, vSize):
        super(myListCtrl, self).__init__(parent, -1, pos = vPosition, size = vSize, style=wx.LC_REPORT

class OutlookConnection():
    def fetchUnallocated(self):
        outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")
        inbox = outlook.GetDefaultFolder(6)  # "6" refers to the index of a folder - in this case inbox,
        return inbox.Items

app = wx.App()
FIR_Frame = MainFrame(None, 'F.I.R - TL Interface')

The challenge I am facing is in the line where I am trying to calculate the ageing of the mail item:-
Code: Select all
tm = - msg.receivedtime
self.subList.SetStringItem(self.index, 7, str(tm))

I am getting error :-
Code: Select all
tm = - msg.receivedtime TypeError: unsupported operand type(s) for -: 'datetime.datetime' and 'time'

I have tried more formats/ways... you can see them in the above code resulting in similar errors

Can someone please help me with calculating the ageing in 'HH:MM:SS' format. Also the hours should populate in greater than 24 wherever required (i.e if ageing is more than 24 hours).

Thank you so much in advance.

Regards, Premanshu
Last edited by Yoriz on Sun Aug 28, 2016 5:07 pm, edited 1 time in total.
Reason: First post lock.
Posts: 2
Joined: Sun Aug 28, 2016 3:43 pm

Re: Calculate ageing of outlook mail items

Postby Ofnuts » Sun Aug 28, 2016 7:59 pm

You can only subtract two datetime objects. Your msg.receivedtime is only a time (likely because it has no date).
This forum has been moved to See you there.
User avatar
Posts: 2659
Joined: Thu May 14, 2015 9:46 am
Location: Paris, France, EU, Earth, Solar system, Milky Way, Local Cluster, Universe #32987440940987

Re: Calculate ageing of outlook mail items

Postby Premanshu999 » Wed Aug 31, 2016 7:09 am

no it actually has date as well into it...
so basically I got it from reading the received date time of the e-mail items from outlook application.
however you are still right that in Python it is still considering it to be a time only... (even though it contains a date along with time)
Posts: 2
Joined: Sun Aug 28, 2016 3:43 pm

Return to GUI

Who is online

Users browsing this forum: No registered users and 3 guests