How do I collect data via email for a specific record in a table, using Access 2007?

[TechyGal]

Hi there!

I am having difficulty in using the 'Collect and Update Data via
Email' function in Access 2007.

I have developed a database for logging and monitoring IT Support
Issues and would like to be able to do the following task using the
data collection via email function:

1). Send an email to a user who has logged a case asking them to
provide feedback to the ICT Team - This section is present on the Case
Details form but I predict that users may forget to fill this in once
the issue has been resolved.

I need to be able to send a data collection request for a specific
case/record in the cases table!

Does anyone know if this is possible?

The Cases table PK is a unique Identifier, ID, which is an Autonumber!

From what I can work out ( and I am just a beginner in this database

development field) is that the Data collection is sent for a table and
not a specific record.

When a request is sent from the Feedback table the user is presented
with the ability to select the case they wish to respond to by
selecting the option that relates to their case e.g. 1, using the
'Case Title' field which is provided as a list of options at the
bottom of the screen.

This is all very well when there are only a few cases/records in the
table but this system will be used for every IT issue so is likely to
get very large, very quickly!!

Is there a way around this???

Any help or advice on this would be appreciated greatly!!

From A deeply desperate sole...

TechyGal

 

[Arvin Meyer [MVP]]

The collect and update via email function is to fill a specific row of data
from the fields in an email which is formatted to present the data in a
specific manner. For instance, if you wanted to fill a table with names and
addresses collected from an email. It is not designed to respond to table
events.

There must be a method of determining which record to send a request for.
Access/Jet does not use triggers so when data hits a table there is no event
generated. If there isn't a user picking an event, you must use code to poll
the table and find any record after a certain period of time. The timestamp
field, assuming there is one, is the most effective way of determining which
records are new. You can also add a Boolean field and poll the table to see
which rows haven't been marked True as yet.

If there is multiple rows with separate records, you must send an email for
each one. To do that you need to create and send your emails in a loop. I
suggest you build a recordset, then loop through it in code, getting the
"To" address, and body from data in the recordset. The subject line can
contain either information from the recordset, or be a predetermined string.

 

[TechyGal]

Hi Arvin,

Thanks for your response! However, I must state that I really am a
beginner in this field and could do with a bit of a dummy's guide and
step-by-step instructions.

I kind of follow where your comming from with regards to the Timestamp/
Boolean fields - I think you are describing a method of automatically
checking to see if a 'Case' has had the 'Feedback' section completed
or not and if Not to automatically trigger a Collect via email
request.

This is great but I still don't understand how to send a request for
data relating to a specific 'Case' without the recipient having to
manually enter the correct case 'Title' from a potentially huge list
of possibilities (as provided within the HTML form created by Access
2007 Collect Data via email function) ??

I am happy to initially send the request manually while inside the
'Case Details' Form, by using a Button which I would like to setup to
send a Data Collection form that relates specifically to the 'Case' or
Record I was viewing when I pressed the button.

Does that make sense or am I not explaining this very well?

Thanks,

 

[Arvin Meyer [MVP]]

Automatic response implies that an action be carried out without human
intervention. In server based database systems like SQL-Server, the server
is running all the time, and when something hits a table, the server can
respond.

Access is not server based, it runs from a workstation, and all action is
instigated from that workstation. That doesn't mean that you cannot respond
automatically, but it does mean that the database must be running (a server
always is, but a workstation may not be), and since there are no triggers to
respond, we must poll the data to know if there are changes.

If what you are asking is how do we know if the case requires a response?
That's where a Boolean or a Timestamp comes in. If I poll the data once
every minute, hour, day, etc., or when I log on, I have an event that can
look at the data and say "Oh! I see you require an answer here. Let me just
send it to you" Or even, "Hey! I'm missing the phone number for this record,
let me send an email to the email address for this person and ask for it."

I don't have Access 2007 installed on my production machines, and I don't
have an Exchange server so I can't interact with the email feature in Access
2007, but you really don't need that to poll the data and send an email.
Obviously, I cannot write the entire application for you, but I can point
you to all the code you need to put together to do it yourself. In the
process, you'll become a developer. (Teach a person to fish). What I need
from you is:

1. An outline of the process, showing:
a. Flow of the work.
b. Names of the tables and fields affected.
2. Patience, if you have a deadline, extend it.

 

[TechyGal]

Hi Arvin and thank you so much for your help! I feel as though I have
been banging my head against a brick wall and just when I think I am
getting somewhere , I don't!

Okay here goes my explanantion:...


A 'Customer' opens a form to create a request for a new ICT Issue
'Case';
An ID (Auto Number) is generated for the new 'Case';
The 'Customer' fills in the 'Title', 'Description','Date Opened',
'Category (e.g. Access) and 'Opened By' fields;

These fields all exist in a table called 'Cases' where the PK is 'ID';

The ICT Team then view the 'Case' and add all 'Action History' details
to the case and once resolved they set the 'Status' to ''Resolved';

It is at this point that 'Feedback' needs to be entered into the
'Case' by the 'Customer'

The table 'Cases' conatins the following fields:

Rating - Where the user selects from a drop down list either Poor,
Average, Above Average or Excelent
Rating Date - The date the feedback was eneterd
Rating Reason - A text field allowing for further info

The 'Customer' field in the table 'Cases' relates to the 'Customer'
table where all details are held about the customer e.g. Name, email
address etc.

I have created an extra field in the 'Cases' table called
'Feedback' (set to a Boolean Yes/No field) and in the ChangeEvent
trigger for the 'Rating' field (on the Cases Form) I have entered the
following code:

Private Sub Rating_Change()
If IsNull(Me.Rating) Or Me.Rating = "" Then
Me.Feedback = False
Else
Me.Feedback = True
End If
End Sub

I have also entered this code on the 'Case Details Form:

Private Sub Form_Current()
If Not IsNull(Me.Rating) Then
Me.Feedback = True
Else
Me.Feedback = False
End If

End Sub

This allows me to detect whether a record has any rating/Feedback
information provided! (I think)


What I now need to do is utilise the Collect Data via Email feature to
allow me to trigger the email request to be sent from either a query
or the actual 'Case Details' form

Inside this data collection form it needs to display the 'Title' of
the case that it is requesting the feedback information for.

I have been able to send email data collection from a query which
displays cases where Feedback = No/False but the information on the
data collection form is blank i.e. NO 'Title' is entered and the data
I enter from the email is unable to update to the 'Rating' fields in
the query.???

I know there must be a way around this and Patience is one thing I do
have. I am a firm believer that where there is a will there is a way
it is just hard trying to explain it on paper!

The data collection pulls the email address of the 'Customer' through
the query but I think I am missing a link somewhere!??

Thanks for your help and Support!!

Really appreciated!

 

[Arvin Meyer [MVP]]

Actually, I think this will be pretty easy. You need to use an event on the
main form or subform to check and see if the email gets sent, then just send
it and mark the checkbox to show completion of that task.

I've even done it before. There is only 1 problem and that is the Outlook
security warning ("An Outside Program is trying to send mail. Do you want to
allow this?") Of course we do! Fortunately, there are good programmers who
understand that we can be click yes to that warning all day long. So you
will need to get a free bit of code named Redemption and put it on any
machine where you need to stifle that warning:

http://www.dimastr.com/redemption/

Now, with that solved, what I do is set an unbound checkbox on the form and
set its visible property to False. I will call this check box chkFlag
because it flags whether or not to email. Now you can also leave this
checkbox visible, and set it to be bound to your record. I'd prefer to leave
it unbound and reset it every time a record changes.

Certain events should trigger this email. For instance, the Case Details
form's Status control has a value of Resolved. So if that control's
(controls are what you are calling fields in a form or report ... Fields are
columns in a table or query ... Controls house fields) value is Resolved,
the Flag control is set to True in the Status control's AfterUpdate event.
Then in the form's AfterUpdate event, there might be some code like:


If Me.chkFlag = True Then
Call MailIt
End If

and the MailIt() function might look something like:

Public Function MailIt()
Dim strSubject As String
Dim strMsg As String
Dim strTo As String

strTo = Me.txtCustEmail

strSubject = "Rating for Case " & Me.txtCaseNo & " - " & Me.txtCustomer

strMsg = Me.txtCaseNo & ",," & """" & Format(Now, "mm/dd/yy h:nn AM/PM")
& """" & _
Me.cboRating

Call Email(strTo, strSubject, strMsg)
Me.chkFlag = False ' Set it to False when done

End Function

and in a standard module, the email function might look like:

Public Function Email(strTo As String, Optional strSubject _
As String, Optional strMsg As String)
'Set reference to Outlook
On Error GoTo Errhandler
Dim objOutl As Outlook.Application
Dim objEml As Outlook.mailitem

Set objOutl = CreateObject("Outlook.application")
Set objEml = objOutl.createItem(olMailitem)

With objEml
.To = strTo
.Subject = strSubject
.Body = strMsg
.send
End With

ExitHere:
Set objOutl = Nothing
Exit Function

Errhandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Function

and there you have it.

 

[TechyGal]

Hi Arvin!

Thank you so much for your help!

This works great in terms of sending the email once the case has been
set to 'Resolved'! The problem now however is that the replies are not
returned into the database, which means a lot of manual work in terms
of reading the email replies and entering the information manually
into the relevant case in the database! Any ideas of, using this
method rather than the data collection via email function available in
office 2007, how to collect the data back into the Access database??

Many Thank,

TechyGal

 

[Pieter Wijnen]

That requires automation with Outlook & is really a question for the Outlook
community.
If you were using Eudora however (& attachments), it would be a simple
matter of browsing it's attachment folder...

Pieter

 

[TechyGal]

Hi Pieter,

I wasn't really wanting to program using Outlook as this is something
I know nothing about!!

I was thinking more on the lines of changing the way the original
email is sent from Access so that when the email is returned the
option for 'Export to Access' is available. Similar to the way the
'Collect Data via Email' function in Access 2007 works.


???

TechyGal

 

[Pieter Wijnen]

I'm not touching Acc'2007 with a yardpole yet; unknowest why <g> - reading
all the *fun* everybody has with it.
I'm afraid there has to be some Outlook automation behind said function
though.
this link might be helpful thought

http://www.vb123.com/toolshed/03_docs/outlookorders.htm

googled for "Read data from outlook mail ms access"

HTH

Pieter

Hi Pieter,

I wasn't really wanting to program using Outlook as this is something
I know nothing about!!

I was thinking more on the lines of changing the way the original
email is sent from Access so that when the email is returned the
option for 'Export to Access' is available. Similar to the way the
'Collect Data via Email' function in Access 2007 works.


???

TechyGal

 

[Pieter Wijnen]

Also
http://support.microsoft.com/?scid=kb;EN-US;313795

HTH

Pieter

Hi Pieter,

I wasn't really wanting to program using Outlook as this is something
I know nothing about!!

I was thinking more on the lines of changing the way the original
email is sent from Access so that when the email is returned the
option for 'Export to Access' is available. Similar to the way the
'Collect Data via Email' function in Access 2007 works.


???

TechyGal

 

[Paul Horak]

just wondering if you ever received a response to this question. I am having a similar issue myself. Thanks.

Hi there!

I am having difficulty in using the 'Collect and Update Data via
Email' function in Access 2007.

I have developed a database for logging and monitoring IT Support
Issues and would like to be able to do the following task using the
data collection via email function:

1). Send an email to a user who has logged a case asking them to
provide feedback to the ICT Team - This section is present on the Case
Details form but I predict that users may forget to fill this in once
the issue has been resolved.

I need to be able to send a data collection request for a specific
case/record in the cases table!

Does anyone know if this is possible?

The Cases table PK is a unique Identifier, ID, which is an Autonumber!

development field) is that the Data collection is sent for a table and
not a specific record.

When a request is sent from the Feedback table the user is presented
with the ability to select the case they wish to respond to by
selecting the option that relates to their case e.g. 1, using the
'Case Title' field which is provided as a list of options at the
bottom of the screen.

This is all very well when there are only a few cases/records in the
table but this system will be used for every IT issue so is likely to
get very large, very quickly!!

Is there a way around this???

Any help or advice on this would be appreciated greatly!!



TechyGal

On Thursday, September 20, 2007 6:57 AM Arvin Meyer [MVP] wrote:
The collect and update via email function is to fill a specific row of data
from the fields in an email which is formatted to present the data in a
specific manner. For instance, if you wanted to fill a table with names and
addresses collected from an email. It is not designed to respond to table
events.

There must be a method of determining which record to send a request for.
Access/Jet does not use triggers so when data hits a table there is no event
generated. If there isn't a user picking an event, you must use code to poll
the table and find any record after a certain period of time. The timestamp
field, assuming there is one, is the most effective way of determining which
records are new. You can also add a Boolean field and poll the table to see
which rows haven't been marked True as yet.

If there is multiple rows with separate records, you must send an email for
each one. To do that you need to create and send your emails in a loop. I
suggest you build a recordset, then loop through it in code, getting the
"To" address, and body from data in the recordset. The subject line can
contain either information from the recordset, or be a predetermined string.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

news:[email protected]...