Highlight Aged Emails

[jonmon]

Hi All

I have a requirement that when an email hits an inbox it is timed and after that time(1 hour) if it has not been marked as completed it either turns red in font or is flagged in some way. i would prefer if the colour of the text went red if i am picky :)


i only want this on one seperate inbox on the exchange server.


all your knowledge and advise appreciated, and just so you know i am a novice at code so please keep as simple as possible for my simple head to understand.

many thanks

[jonmon]

anyone got any thoughts on this? this will be added to only one inbox using outlook 2003 on the exchange server.

[SamT]

Jonmon,

Welcome to VBA Express.

I have changed the thread title to try and attract more responses to your query.

[skatonni]

Three methods for processing incoming mail are described here.

http://www.outlookcode.com/article.aspx?id=62


Rule to run a script
ItemAdd
NewmailEx


Something like this for the run a script option.


Sub Set_FollowUp_Auto(myMail As mailItem)
With myMail
.FlagStatus = olFlagMarked ' <- deprecated
.FlagDueBy = Now + 0.04 ' 1/24 = one hour ' <- deprecated
.FlagRequest = "Must action"
.Save
End With
End Sub

It may not work in the future. For reference see here ' http://msdn.microsoft.com/en-us/library/bb610089%28v=office.12%29.aspx

[jonmon]

Something like this for the run a script option.


Sub Set_FollowUp_Auto(myMail As mailItem)
With myMail
.FlagStatus = olFlagMarked ' <- deprecated
.FlagDueBy = Now + 0.04 ' 1/24 = one hour ' <- deprecated
.FlagRequest = "Must action"
.Save
End With
End Sub

Hi Skatonni

many thanks for your reply.
the code works that you provided me but i was hoping it would not pop up a box to remind you that it has been there for an hour. We will be recieving about 1500 mails to this inbox each day so not really workable to have a reminder pop up each time.
is there a way to make the text go red when it passes the set time without being marked as completed or flagged for follow up?

many thanks

[skatonni]

See whether setting an expiry works better for you.

First manually set up conditional formatting on Expired Mail so it goes red.


Sub expire_onehour(newMsg As mailItem)

Debug.Print newMsg.ExpiryTime
Debug.Print newMsg.ReceivedTime
If newMsg.ExpiryTime = "4501-01-01 " Then
newMsg.ExpiryTime = DateAdd("h", 1, newMsg.ReceivedTime)
newMsg.Save

Else

' Expiry already set by sender. You may wish to handle this mail some other way.
' newMsg.Display

End If
End Sub


When completed, mark it and remove the expiry date so it no longer displays red


Private Sub Exp_Delete()

Dim myMailitem As Outlook.mailItem
Dim newExptime As Date
Dim addDays As Long
If TypeOf Application.ActiveWindow Is Outlook.Explorer Then

For Each myMailitem In ActiveExplorer.Selection
myMailitem.ExpiryTime = "4501-01-01"
myMailitem.Save
Next myMailitem

Else
Set myMailitem = Application.ActiveInspector.currentItem
myMailitem.ExpiryTime = "4501-01-01"
myMailitem.Save

End If
End Sub


Test with this.

Private Sub expire_onehour_test()

Dim newMsg As mailItem

Select Case TypeName(Application.ActiveWindow)
Case "Inspector"
Set newMsg = Application.ActiveInspector.currentItem
expire_onehour newMsg

Case "Explorer"
Set newMsg = Application.ActiveExplorer.Selection.Item(1)
expire_onehour newMsg

Case Else
' anything else will result in an error
MsgBox "Invalid item."
End Select

End Sub