469,954 Members | 1,694 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,954 developers. It's quick & easy.

Would this be easy to do??

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
Mar 14 '08 #1
4 1612
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...@ntlworld.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.east4adventure.org.uk/snorkel
Mar 14 '08 #2
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" <sd******@gmail.comwrote in message
news:95**********************************@m44g2000 hsc.googlegroups.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...@ntlworld.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.east4adventure.org.uk/snorkel

Mar 14 '08 #3
"Rick Stevens" <ri**********@ntlworld.comwrote in message
news:Ip*****************@newsfe3-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.
Mar 14 '08 #4
DFS
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!

Mar 15 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

24 posts views Thread by Charif Lakchiri | last post: by
39 posts views Thread by Antoon Pardon | last post: by
5 posts views Thread by Ronald S. Cook | last post: by
4 posts views Thread by Sanoski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.