Would this be easy to do??

[Rick Stevens]

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?

many thanks for an advice you can give

Rick

--
visit www.east4adventure.org.uk/snorkel

[Julio]

Re: Would this be easy to do??

Only way I can imagine doing this is with coding, VBA. I am guessing
you use Outlook for email. You would need to establish a connection
between Outlook and Access. Maybe you can make a rule in the Outlook
rules wizard that sends those emails to a folder, and have the VBA
code trigger anytime an email goes to that folder. They are probably
a couple of ways to do this, but I don't see anything that doesn't
involve coding such as VBA. Hence, not easy to do.

On Mar 14, 10:59 am, "Rick Stevens" <rick-stev...@ntlworl d.comwrote:

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?
>
many thanks for an advice you can give
>
Rick
>
--
visitwww.east4a dventure.org.uk/snorkel

[Rick Stevens]

Re: Would this be easy to do??

Thanks for that Julio , I thought it didnt sound like beginners stuff.

Anyone have any ideas how this scenario could be easily accomplished? It
doesnt have to be in access, could be a webpage or anything

cheers

Rick

--
visit www.east4adventure.org.uk/snorkel
"Julio" <sdilucca@gmail .comwrote in message
news:95c8b09e-5b96-4462-a756-ed3bd671a9c4@m4 4g2000hsc.googl egroups.com...

Only way I can imagine doing this is with coding, VBA. I am guessing
you use Outlook for email. You would need to establish a connection
between Outlook and Access. Maybe you can make a rule in the Outlook
rules wizard that sends those emails to a folder, and have the VBA
code trigger anytime an email goes to that folder. They are probably
a couple of ways to do this, but I don't see anything that doesn't
involve coding such as VBA. Hence, not easy to do.
>
On Mar 14, 10:59 am, "Rick Stevens" <rick-stev...@ntlworl d.comwrote:

>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?
>>
>many thanks for an advice you can give
>>
>Rick
>>
>--
>visitwww.east4 adventure.org.u k/snorkel

>

[Deano]

Re: Would this be easy to do??

"Rick Stevens" <rick-stevens@ntlworl d.comwrote in message
news:IpwCj.9387 $jH5.6482@newsf e3-win.ntli.net...

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?
>
many thanks for an advice you can give
>

There is plenty of info on connecting Access to email programs and
processing messages. It depends on what program you use - if it's webmail
then I wouldn't have a clue but else you can definitely parse emails with
some code. It should be fairly straightforward if the data is consistent
and the code to change the buttons is trivial.
It would be worth thinking of what fields you want to capture, then you
might expand the program with a few useful reports e.g failure over time
etc.

[DFS]

Re: Would this be easy to do??

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>:<Equipmen t 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 checkEquipStatu s()

'OUTLOOK OBJECTS AND VARS
Dim olApp As New Outlook.Applica tion
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.GetNamesp ace("MAPI").Fol ders("Mailbox - Stevens,
Rick")
Set olSourceFolder = olMailbox.Folde rs("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("UPD ATE 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.txtStatusUpd ate = Now()

'UPDATE THE SCREEN TO SHOW LATEST STATUS
Me.datasheetNam e.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.txtStatusUpd ate 15 minutes Then
call checkEquipStatu s
End If

Enjoy!