Rick Stevens wrote:
I am not an access expert, could anyone tell me if the following
would be easy to do??
I receive emails from a specific email address, that advise me if a
specific piece of equipment my company maintains has failed. I also
then get emails telling me when this equipment is back working again.
All of this information is held within the body of the email.
I would like to make this more graphical instead of filtering through
emails. Since it is the same piece of equipment but at numerous
locations, I would like to create an access form with buttons for all
of these locations on, but for the colour of these buttons to change
dependent upon its status. e.g an email arrives saying equipment at
location A has failed - the button goes red - email arrives saing
equipment at
location A is now working again, button goes green.
Is there an easy wav to do this?
Won't be easy if you're inexperienced with Access/Outlook/VBA. But
straightforward enough.
First, it would be nice if the email had a standardized subject line, such
as "Equipment Status: <Location# or Location Name>:<Equipment Status>". If
not you'll have a much harder time parsing the data.
Depending on how many locations you have, a button for each location on a
form may be a lot of trouble to write and maintain. So you might just use a
database table with location name, equipment status, status change date,
comments, etc. Then display this data in a datasheet view.
Loc1 Operational 3/12/08 12:23:35pm
Loc2 Operational 3/13/08 1:21:22pm
Loc3 Failed 3/14/08 19:03:03pm
Loc4 Operational 3/10/08 01:01:03am
Now you just need to write some code to read from an Outlook inbox, look for
items with "Equipment Status" as the first 16 characters of the Subject
line, get the location and status right from the subject, and update your
table and refresh the screen.
In your code module, set a Reference to the Microsoft Outlook 11 Object
Library (that's for Outlook 2003).
=================================================
Attach code like this to a form
Public Sub checkEquipStatus()
'OUTLOOK OBJECTS AND VARS
Dim olApp As New Outlook.Application
Dim olMailbox As Object, olSourceFolder As Object
Dim olItems As Outlook.Items
Dim subjectStr As String, subjectLen As Byte
Dim i As Integer, j As Integer, k As Integer
Dim location As String, equipStatus As String
'REFERENCES TO OUTLOOK FOLDERS
Set olMailbox = olApp.GetNamespace("MAPI").Folders("Mailbox - Stevens,
Rick")
Set olSourceFolder = olMailbox.Folders("Inbox")
'ITERATE FOLDER CONTENTS
Set olItems = olSourceFolder.Items
k = olItems.Count
For j = 1 To k
'ENSURE OUTLOOK EMAIL SUBJECT MATCHES ENTRY FROM TABLE
If Left(olItems(j).Subject, 16) <"Equipment Status" Then GoTo
nextEmail
'PARSE LOCATION AND STATUS FROM EMAIL SUBJECT LINE
'example: Equipment Status: Atlanta - Failed
'NOTE: you'll have to figure this out how to use Mid and InStr
location = Mid(olItems(j).Subject,19,7)
equipStatus = Mid(olItems(j).Subject,29,6)
'UPDATE YOUR TABLE
db.Execute("UPDATE Table SET Status = '" & equipStatus & "' WHERE
Location = '" & location & "';")
'NEXT EMAIL IN INBOX
nextEmail:
Next j
'CLOSE OUTLOOK OBJECTS
Set olItems = Nothing
Set olMailbox = Nothing
Set olSourceFolder = Nothing
'IN A Text FIELD ON THE FORM YOU CAN RECORD THE LAST TIME YOU CHECKED EMAILS
Me.txtStatusUpdate = Now()
'UPDATE THE SCREEN TO SHOW LATEST STATUS
Me.datasheetName.Requery
End Sub
=================================================
You could leave the Access system running, with your status form that has
settings in the TimerInterval and OnTimer event, and have it run that code
to check for emails every 5, 10, 20, 30 minutes, etc. Whatever time period
you need.
Set the TimerInterval = 1000x the number of seconds, so a 5 minute interval
= 300000
The pseudocode for the OnTimer event might look like this:
If Now - Me.txtStatusUpdate 15 minutes Then
call checkEquipStatus
End If
Enjoy!
