Tracking Screen For Emailed Requests

You can try to move all functionality to Access. Users can use Access runtime, which if free, when you can develop in full version
http://www.microsoft.com/en-us/download/details.aspx?id=10910

So you have email requests for Parts, and you need to track those?

From my experience, trying to work with "data" in an email body is troublesome at best. Unless you can define the format of that body, and dictate the email clients used in both sending and receiving, then you're in for a difficult task. Email is notoriously difficult to parse into relevant data, so I would presume that you'll have to manually enter the data initially.

If you do that, you could then easily track the progress of the request. For example, when you initially enter the request, you could set the status as "Initiated" or "Started" or whatever makes sense to your business. When someone begins processing, they could set the status to "In Progress", and so on. This would require manual intervention, of course.

Multiple users could update this data as it flows through the system. The best way to manage that is to have a Backend (the data tables ONLY), which is located on a common network share, and a FrontEnd (the forms, reports, queries, etc) which you would deploy to each user. All Frontends would be connected to the same Backend, so when UserA updates the status of Part Request XYZ, all other users would see that as well.

@LSMConsultion: I'm very familiar with xfitguru's requirements so here's some more info about the emails. All of them have a simple Subject consisting of "<some variable text> Request" and a body consisting of just "Please upload the attached" which is an attachment consisting of a worksheet. So all he wants to track is to whom the email was sent to and the status of the request. He and I never discussed it but I think would be useful to also store the name of the worksheet (which contains a date) in the tracking information.

I am a bit hazy on your exact requirements here, ("Tracking"?)
...and I am also a bit confused on how Excel, Outlook and Access need to interface with each other to get you what you are looking for.

In any event, ...(to add to what LSM posted), you can certainly "Link" a users Inbox and query the incoming emails for certain keywords in the Subject, Body, To, or From Fields

For example, you can use the "External Data" utility in Access to Link to a users Inbox,...then use a query like this to query the "Subject" for a certain keyword:
SELECT Inbox.Subject, Inbox.Contents
FROM Inbox
WHERE Inbox.Subject Like "*" & [Enter Subect Keyword] & "*";

...or like this to search the body text:

SELECT Inbox.Subject, Inbox.Contents
FROM Inbox
WHERE Inbox.Contents Like "*" & [Enter Email Body Keyword] & "*";

(I am sure you can see how this could be adapted to search the "To" and "From" fields...)

Not sure if this would be useful for your "tracking" needs, but it may be another piece of the puzzle...

;-)

JeffCoachman

I'm developing a sharable Excel database that I believe you'll be able to use. Be back in a while.

I want to thank all fo you for the advice.  Unfortunately, I am limited to Excel for the time being since my company rejected the notion of changing their dependence on a totally excel related transfer of data.  Trust me, you cannot say something that I have not already said.  Needless to say, I was floored when I came on board.  It is so bad that, when they actually considered my suggestion to begin using Access,  they had to bring in an outside consultant to teach our Accounting Department how to build a simple database in Access.  I do not know how much she was paid but it was totally a rip off.  She was from the local community college and, on several occasions, had to refernence an Access user manual in the middle of her lecture/presentation.  SInce they "chosen" few from our Accounting Department did not catch on, the company felt that it would be too painful to implement Access on a company wide basis.  Thus, I lost that battle.  So, here I am, using Excel spreadsheets like its a magic show. Haha

 I am so incredibly grateful for the advice everyone!!!

Then perhaps you could post a sample Excel file, and also include a sample of the exact output you need...?

@chris: I've attached a crude first draft of the database and a version of the ADS Help Desk that interacts with it. In order to try it out you'll need to change the TRACKING_DB_PATH constant that you'll find in the help desk's Functions module. Also for now the tracking is only activated for the New Item request.
ADS-Parts-Help-Desk-V24.xlsm
Database.xlsm

Chris, attached is a newer version of the help desk (although the name is the same) and a new version of the database. Again please make sure you change TRACKING_DB_PATH to reflect the path and name of the database. Also note that the database workbook and its sheets should not be locked. Give it a whirl and let me know how it looks.
ADS-Parts-Help-Desk-V24.xlsm
Track-Requests-V2.xlsm

Sorry for the delay Marty.  I am looking at this now.  I assume the latest attachments supercede any earlier versions?  Thanks for the help.  :)

Well you could keep version 23 around in case you decide not to go with the Excel shared workbook, but it would not be hard to back out 24's changes. In any case building the shared workbook is not hard to do at all but I've seen several people suggest that shared workbooks should not be used because they're prone to crashing. When I see things like that I always wonder if they're simply repeating what someone else said with no actual experience one way or the other. Excel has been around for 16(?) years and it would be unusual to it to still have problems like that but who knows. The other problem is it seem that you can't protect the sheet but I'm still looking into that.

When I attempt to email the request or attach a file, it starts to flicker and appears to be somewhat volatile.  It hasn't closed or locked up on me yet but I am still testing.  Also, can we modify this so when an attachment is provided, it doesn't automatically send the email?  Or is that a requirement?  Thanks for all of your help my friend.  :)

When I attempt to email the request or attach a file, it starts to flicker and appears to be somewhat volatile.  It hasn't closed or locked up on me yet but I am still testing.

I can probably fix that.

Also, can we modify this so when an attachment is provided, it doesn't automatically send the email?

I'm not sure what you are asking. Currently, other than the parts that allows the user to select the attachments, the email process is the same for both emails without attachments and emails with attachments. In the code for emailing the requests there's a line that says .Display '.Send (in 3 places) which I assume you will change to just .Send when we are done you distribute the workbook. Are you asking to do something with that?

No sir, I don't want anything done with the .Send or .Display.  I am referring to simply clicking the button that allows a user to add attachments.  When I click this, it prompts for the file location as normal, but then, as soon as it's closed, it is as if I clicked the Email/Send button.  I get an email draft that is populated.  My request is to change this process so the user can attach a document and then click the Email/Send button.  Thank you for the help my friend.  God bless!  :)

I don't see what's wrong with the way it is working now. If the user clicks the plain email button the report is sent immediately. If the user clicks the email button with the paperclip he's asked for the attachments and then the email is sent immediately. So the second button does exactly this "...the user can attach a document and then click the Email/Send button" but in one step.

Another way the think of it is that the button without the paperclip is "Email Request" and the one with the paperclip is "Email Request with Attachments". (Note that it can be more than one attachment).

Oh, I see.  Okay that makes sense.  I can elaborate that when I push this out to the entire company.  I like that.  I didn't realize that was the intended functionality but I am perfectly fine with it.  I also like that it can be more than one attachment.  Thanks Marty.

Try this. For now put all three files in the same folder.
ADS-Parts-Help-Desk-V25.xlsm
Track-Requests.xlsm
TrackRequests.mdb

Greetings Marty.  I am getting errors when I attempt to open the Tracking Form.  I have placed the errors below.  Also, I opened the ADS Help Desk form and submitted a request.  It emailed but nothing showed on the tracker this way either.  








Quick question.  Can we add a field to the Tracker Form?  I did not think about this at the beginning but can we have the name of the person who submitted the request as well?  It would be "SUBMITTED BY" on the document.  It doesn't matter where it goes.  

On the ADS Help Desk, is it displaying alerts because I am the person running it?  Basically, will it do this for users other than me and you?  Also, can we have it where it returns the user to the 'HOME' screen after they have submitted their request.  Please let me know if you have any questions.  Thanks again Marty!

First let's talk about the steps that lead to the error. What kind of request did you submit from the help desk? In this statement "It emailed but nothing showed on the tracker this way either", what does the bolded part mean? Was the tracking database empty or did you use it the way I posted it where it already had some records? Can you remember the steps you took after (and perhaps before if you did something like clear the database)?

Can we add a field - sure. The easiest thing to do would be to use submitter's PC name. Is that OK?

"On the ADS Help Desk, is it displaying alerts because I am the person running it? " are you talking about alerts like "your request has been submitted"? Everyone sees those.

"Also, can we have it where it returns the user to the 'HOME' screen after they have submitted their request." - sure.

I just got the same error which is a good thing 'cause now I can find out more easily what's going on.

Can you supply the list of Statuses please?

Good morning Marty.  I just am now able to get back with you on this.  I am assuming that the list of statuses you are referring to are the dispositions that will be in the list of values such as: ('SENT','COMPLETE','AWAITING RESPONSE', etc.)  These are what I could think of off the top of my head.  

SENT
PENDING
OPEN
WORK IN PROGRESS
WAITING ON CLIENT
COMPLETE
CLOSED
CANCELLED
RESOLVED


Also,  can we have a link to the attachment or the attachment itself added to the tracker screen?  This is the last thing needed to add on this.  I'm sorry for continuing to give the "Oh, and's"  I wanted it to contain the same data as the current template being used by our IT guys.  Thank you Marty!!!

Also, I meant to ask about the Request type.  Can we have it specify the request type for 'Inventory Item Update Request'? Maybe have it state 'update Berry Status' or 'Update Expire Status'.  Or, would you rather have it state, 'Inventory Item Update Request-Berry'?  Something similar to this?  THanks

I'm sure I can show the specific IIN request but I don't think I understand what you want in reference to the attachments. What do you mean when you say "can we have... the attachment itself added to the tracker screen"? And when you say "can we have a link to the attachment...added to the tracker screen are you talking about a link to where it's from or a link to where the recipient stores it? If it's the latter I don't think that's possible since the recipient can put the file anywhere he wants and in any case that happens after it's sent, and if it's the former that's not possible unless everyone has access to everyone else's PC. Please explain more.

Let's leave the URL processing for a later time. Here are updated files.

Changes for Track Requests.xslm
Note that even though this is an updated file, that I haven't added a version number to the name. That's because the name is used in the Help desk workbook and I don't want to worry about keeping them in synch.

o Corrected the startup error
o Added 'Submitted By' column
o Updated Status values
o Protected the workbook and the hidden 'Named Ranges' sheet with the usual password.   (The 'Named Ranges' sheet is where the status values are stored.)
o Added a 'Refresh' button so that user can see new requests if he  has this workbook already open when new requests are created.

Changes for ADS Parts Help Desk V26.xlsm
o Added code to handle the new 'Submitted By' database field
o Return "Home" after successful email
o Include the specific request type instead of the generic IIN report name in the tracking for IIN requests

Changes for TrackRequests.mdb
o Added SubmittedBy field
ADS-Parts-Help-Desk-V26.xlsm
Track-Requests.xlsm
TrackRequests.mdb

Good day Marty and God bless.  In response to Thread ID: 39066004, I have attached a summary below.  I hope I addressed your concerns completely.  Please let me know if not.  

• "What do you mean when you say "can we have... the attachment itself added to the tracker screen"?"
• "can we have a link to the attachment...added to the tracker screen..."

I was only inquiring about having some way to reference the attachments that are sent with the request.  If a manager looks at a particular request, he/she could click a link/file/url or whatever in order to see the file that was submitted with the request.

The only way I can think of to do that would entail a manual process on the recipient's end.

Okay.  That's nothing major if we cannot have it.  The users can simply reference their Outlook history for the attachment.  

So far this is working out pretty well.  I have modified the forms a little just for cosmetics.  I attached just in case you didn't like the way I have it formatted.  Also, I do have a question.  If one user has the ADS Help Desk file open, how can I eliminate the ReadOnly warning message populating when another user attempts to open.  Additionally, how can I have it setup so there will be no message asking to save when the file is closed.  Basically, I want to save this file somewhere, provide my useres with a link to it, and have them use it but not be able to save over it. Thanks for your help with this one Marty!!!
ADS-Parts-Help-Desk-V26Test.xlsm
Track-Requests.xlsm

What did you change in ADS-Parts-Help-Desk-V26Test.xlsm?

I don't understand this:

If one user has the ADS Help Desk file open, how can I eliminate the ReadOnly warning message populating when another user attempts to open.  Additionally, how can I have it setup so there will be no message asking to save when the file is closed.  Basically, I want to save this file somewhere, provide my useres with a link to it, and have them use it but not be able to save over it.

The way I envision this is that each person has their own copy of the ADS Parts Help Desk workbook and their own copy of the TrackRequests workbook, and the Access database would be placed on a server that everyone has access to. So the only file that is shared is the  Access database. Do you have any concerns about that scenario? Given that scenario I don't see how your questions apply but if they do then please let me know.

You are correct, given that scenario, then my questions are not applicable.  I have no concerns whatsoever pertaining to your scenario.  We just had different things in mind but the same outcome. With your plan, I could email the files out for everyone's use.    I could simply save the Access database in a network location accessible to everyone OR I could work with our IT group to have this placed on our server.


My idea was that the Help Desk and the Tracker would be singular files saved in a network location, thus creating the error for which I was referring. The current Help Desk resides a certain drop-down link (picture below) which each user has access to.  Once they are finished submitting their request, they simply exit the program and everything is handled from the program's Tracker screen.  What I had in mind was basically an emulation of this.  


The error message I was reffering to in the previous post is below:

In the error message it refers to Sheet1.csv.  Nowhere in our workbooks is such a file used or created.

Roger that Marty.  The error message I posted was only an example.  I simply selected the first file avaiable to replicate the msg box error and copied for the thread.  Sorry for not stating that when I posted.

So far everything looks great Marty!!.  I have a few people on my team testing the crazy out of these before I push out the the entire company.  I do have a couple of thoughts and ideas.  Just wanna know what you think.  

1.) Can we have an 'EXIT' button on the Tracker Form in order to close?

2.) Can we add a page on the Tracker Form which shows Totals for Request Statuses?
      For Instance:
        - 25 Work in Process
        - 30 Complete
        - 07 Sent
        - 02 Waiting on Client

- This would be primarily for management to look at the overall disposition of requests.  I guess it would be similar to a dashboard for the Help Desk/Tracking System.  What are your thoughts?

1) Why?

2) Sure but next question, OK?

Why?
 - I was asked by my Director for a way to pull metrics for the requests.  We want to compare the traffic coming through this new process versus the historical data the older way.  

Next question?
 - Yes sir.  I was actually going to suggest that myself so that works for me.  :)

My Why question was referring to this:

1.) Can we have an 'EXIT' button on the Tracker Form in order to close?

Oops! My apologies Marty, I was under the impression that you were referring to the below:

Can we add a page on the Tracker Form which shows Totals for Request Statuses?
      For Instance:
        - 25 Work in Process
        - 30 Complete
        - 07 Sent
        - 02 Waiting on Client

As pertaining to the 'EXIT' button, I wanted to add this just for pure cosmetics.  While the user could simple close out of the Excel file, I wanted the Tracker to be as similar to the ADS Help Desk file in terms of appearance and its user "friendliness."  Again, my apologies for the confusion before.

I'm confused. There's no Exit button in the Help Desk so I don't understand the need for one in the tracker. BTW if this question is fully tested can it be closed?

When you modified TrackRequests.xslm to make formatting changes, did you do anything to the "Named ranges" sheet like protect it with a password? I need to make it visible and I can't.

Edit: Doh! - Nevermind

The Help Desk has an 'Exit Button'.  See below:

Marty, I am closing this question out.  Everything has been tested for the most part.  I am still having members of my team test as well.

Words cannot express how much Marty has helped me thus far!  I am humbled and grateful for all of the help!

Here is the link to the new question.  Thanks again for all of your help my friend!!

New Question

As always, I'm glad I was able to help.