?
Solved

Microsoft Issues database template

Posted on 2011-11-01
26
Medium Priority
?
549 Views
Last Modified: 2012-05-12
Hi all,

I am currently in the process of sort of replicating Microsoft's Issues database template2003 in A97 with additional features of my own.

http://office.microsoft.com/en-gb/templates/issue-database-TC001218693.aspx?AxInstalled=1&c=0

However I am quite keen of adopting the send email feature. Basically I am trying to send an email to either a particular contact or a group of contacts  (assigned to)regarding the issue that I am requesting that needs attention.

Grateful if anyone is aware of the template so that I can ask exactly my requirement.




Thanks
0
Comment
Question by:PipMic
  • 15
  • 6
  • 3
  • +1
25 Comments
 
LVL 40

Expert Comment

by:als315
ID: 37067838
I don't use this template, but I've downloaded it and can try to help you.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37069811
<send email feature>
...Are you referring to the "Email Data Collection" feature, or are you just wanting to send an email?

< Basically I am trying to send an email to either a particular contact or a group of contacts  (assigned to)regarding the issue that I am requesting that needs attention.>
...Are these Contacts in Outlook or just people with email addresses listed in the database?

"Sending an email" can either be incredibly simple or incredibly complex depending on your exact needs and skill level.

At the low end sending an email is as simple as this:
DoCmd.SendObject acSendNoObject, , , "Somebody@SomeEmail.com", , , "Your Subject", "Your email Body Text"

So you will have to be *very* specific on your *exact* needs here...

JeffCoachman
0
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 37073202
You also need to be specific on your version of Access.   Do I understand you are trying to code this is Access 1997?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:PipMic
ID: 37075319
Hi guys,

My apologies for not responding earlier.

1. Yes my current version is A97.

2. I have not tested the issues template (A03) thoroughly but having seen it and given that I was working on a similar database, I thought it would be a great idea if I could set out the task details in a form and then select the individual to whom I want it assigned to OR assign it to a group of individuals.

The email I am envisioning in would require a

1. To

2. BCC (no need for CC because as I mentioned previously it would be eithe to an individual or a group. The idea of the BCC, in case I have to submit the email to a Non-IT manager!

3. Subject (which could be picked up from the form, I suppose)

4. Task (which would be picked up from the form itself)


I was wondering whether task details would need to go as part of an attachment?

I would be grateful for assistance. If you require more info please let me know.

Thanks
0
 

Author Comment

by:PipMic
ID: 37075711
Hi guys,

I can upload what I have so far, if anyone is wants to have a look.

Thanks
0
 

Author Comment

by:PipMic
ID: 37075739


My copy, here goes! Please note it is unfinished.
testtasks.mdb
0
 
LVL 40

Expert Comment

by:als315
ID: 37076466
There is macros, where you can do all your tasks, filling fields. May be you can explain where you have problems?
0
 

Author Comment

by:PipMic
ID: 37076710
Hi,

 it doesnt work as I want it to:

The macro opens outlook Express (My default email application)

1. I cant get it to pick up the email of the person  or the group the task is assigned to.

2. I cant assign it to a specific template which I have.

My concern is that I have copied and amended the macro from the Issues Database (A3) and adapted it to this A97 database

0
 

Author Comment

by:PipMic
ID: 37076764
Hi,

I almost forgot. The moment I include an entry for the task description, Outlook Express opens a new email and ignores my email template.

I hope I am making some sense

Thnaks
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 37076827
Again, more info is needed.

Note that Access 97 is a 14 year old application.
With all the updates to Windows, Office,  Outlook, Security, Web browsers, ...etc.
There is no guarantee that this will work "Smoothly"

So my first question would be can this be upgraded to at least Office 2007/Win XP?

1. Are you planning on clicking a button on the form to send the email for to each recipient, individually?
...or do you want this to happen automatically... (one button click for all recipients.)

2. Please try to avoid using "Lookup" fields in your tables.
See here:
http://access.mvps.org/access/lookupfields.htm
Use the lookups in your forms only.  There is wizard there that will help you do this as well.

3. I am confused when you say:
"Subject (which could be picked up from the form, I suppose)"
"Task (which would be picked up from the form itself)"
...because each form record is for a task.
Since one person can have many tasks, I am confused here.
It would seem to make more sense to loop the people and gather all the tasks details and put that all in one email...
Please clarify...

4. <I was wondering whether task details would need to go as part of an attachment?>
This would definitely be easier, but then you need create a report that we can filter for each person.

5. What format would the attachment be in?  
Text, Excel, and HTML are all easy to do (but may not translate all of the reports formatting), but a PDF in Access 97 will require a third party utility (and a bit more coding and complexity)

6. <BCC (no need for CC because as I mentioned previously it would be either to an individual or a group. >
Again, clearly define a "Group"...?
In this a distribution list in Outlook?

7. Where are all the group member emails stored? How are we to know what people are in which groups?
...etc

8. Do you want to preview the emails before sending, or send them automatically?

9. This will all be *much* easier to do if you used vba code instead of macros.


JeffCoachman


0
 

Author Comment

by:PipMic
ID: 37077202
Gosh! boag you are thorough :)

Ok I'll start from the back.

1. yes I would have preferred to use vba code but since I saw this Microsoft database I thoght I could copy the idea of the macro to the database I was developing.

2. No I cannot upgrade as it stands. I am using A97 on a XP platform.

3. I dont know whether you've seen my uploaded database. The idea is that a form is filled in detailing the task to be carried out. There is a button which initiates the send email. You make the good point which is whether I want to preview the email first. The answer is yes.

4. The email addresses is not part of the Contact lists of Outlook Express. These addresses form part of database (These are found in the tbl_Assignee). that table also includes a names belonging to the group - Please see the table for this.

5. By using the macro method, it seems that by using  SendObject I am able to include certain things as part of the email, i.e. Subject, message, etc.
I used these "attributes to try and facilitate the preparation of the email. Somehow it is picking uo the person or group to whom to send to.
It is picking up the Subject (picked up from the Form) but thats about it. I've also included a Message text (picked up from the form) but the moment I do that it does not pick up the template file (this template file is not in my upload)

6. i am willing to accept a similar solution either using an attachment (a report) or some other method to achieve my idea.

 I think I have covered everything but if i am missing out on something please let me know!!
0
 

Author Comment

by:PipMic
ID: 37077229
Version 2 - PLEASE IGNORE PREVIOUS POST -

Gosh! boag you are thorough :)

Ok I'll start from the back.

1. yes I would have preferred to use vba code but since I saw this Microsoft database I thoght I could copy the idea of the macro to the database I was developing.

2. No I cannot upgrade as it stands. I am using A97 on a XP platform.

3. I dont know whether you've seen my uploaded database. The idea is that a form is filled in, detailing the task to be carried out. There is a button which initiates the send email.
You make the good point which is whether I want to preview the email first. The answer is yes.

4. The email addresses is not part of the Contact lists of Outlook Express. These addresses form part of database (These are found in the tbl_Assignee). That table also includes a names belonging to the group - Please see the table for this.

5. By using the macro method, it seems that by using  SendObject I am able to include certain things as part of the email, i.e. Subject, message, etc.
I used these "attributes" to try and facilitate the preparation of the email.

Somehow it is not picking the person or group to whom to send to.
It is, however,  picking up the Subject (picked up from the Form) but thats about it. I've also included a Message text (picked up from the form) but the moment I do that, it does not pick up the template file (this template file is not in my upload)

6. I am willing to accept a similar solution either using an attachment (a report) or some other method to achieve my idea.

 I think I have covered everything but if i am missing out on something please let me know!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37080468
For Number 3, my question here is *When* you want the email sent out.

Again, forcing the use to click a button is problematic because they can forget, or click it twice.

Is your goal that each time a person gets a new task, you want to send them a email?
0
 

Author Comment

by:PipMic
ID: 37081852
Hi,

Thanks boag2000,

My initial idea would be to force the user to click the button.

However you make a good point! Perhaps before closing the form it might be an idea to stop the user exiting the form until the email has been sent.

Have you seen my uploaded database? Is there any chance of carrying out what I am suggesting?

Thanks
0
 

Author Comment

by:PipMic
ID: 37082386
Hi all,

I've amended my database so that I can send it to the emails either to an individual or a group.

However, I aleardy have an email template file in html format but when I write out the path where the template is located, the email sent does not pick up the template but rathers sends a new email to the pertinent Assignees with the everything else , i.e. Subject and  Message text.
testtasks3.mdb
0
 

Author Comment

by:PipMic
ID: 37106158
Hi,

I am trying to improve and simplify my explanation and provide a sample database (done in A97) .

 I have the following so far:
* Using A97
* a template created for Outlook Express.

The database (see attached)

An input form where all the info related to a task is inputted.

As it stands: the database works "nearly" as required.

The problem I am haviing occurs when I want to send an email to a designated user/users

If I do not fill in the Task Description in the Input form, the send email works well, i.e  My own Outlook express template is picked up automatically, and so is the To: and Subject:

However when I additionally populate the Task Description field and send the email, the template is not picked up and instead a blank email comes up.
Note  -  To: and Subject: are picked up

Grateful if there is a way on ensuring that the outlook express tempalte is picked up.

Hope this is more clear.
testtasks5.mdb
0
 

Author Comment

by:PipMic
ID: 37120190
Hi all,

I am still trying to figure this one out.

I was wondering whether if I change whole thing. Rather than use a macro to send the email I was wondering whether it was possible to use some VBA code.  So far I have read up on this but I am not achieving my aim.

grateful if someone could assist me with the code.

As way of reminder, I have a form which will provide the details that I want with my email. (Please note that I do not want an attachment to my email)

Please see my uploaded database.

To = txt_sendemail
Subject = Task
Message Text = Task Description
template  = c:\email.htm
0
 

Author Comment

by:PipMic
ID: 37120537


Hi all


I am trying this code but to no avail:

DoCmd.SendObject acSendNoObject, , , "txt_sendemail", , , "Task name", Forms!["frm_ProjectIssueLog"]!["Task Description"], True, "c:\email.htm"

Grateful for assistance
0
 
LVL 40

Expert Comment

by:als315
ID: 37120734
Try
 Forms![frm_ProjectIssueLog]![Task Description]
or me.[Task Description]
0
 

Author Comment

by:PipMic
ID: 37137175
Thanks als315 but it doesnt work, i.e. the text in the Task Description does not appear in the body of the email   !!!



0
 
LVL 40

Expert Comment

by:als315
ID: 37137281
Problem is in Access 97. I have no Access 97 and this template is not working without conversion. I will ask other experts. May be anybody have Access 97.
0
 

Author Comment

by:PipMic
ID: 37137435
The database can be converted upwards , no probs.
0
 
LVL 40

Expert Comment

by:als315
ID: 37137592
Yes, but you need working DB in Access 97. Some things will work in next versions of Access, but will not in Access 97. In Access 2010 I can save only to Access 2000 format or higher.
0
 
LVL 40

Accepted Solution

by:
als315 earned 400 total points
ID: 37137711
After conversion I have no problems with your db. All fields are filled. Macro was converted to code in (Button pressed event):
With CodeContextObject
        DoCmd.SendObject , "", "", .txt_sendemail, "", "", .[Task Name], .[Task Description], True, ""
End With
0
 

Author Closing Comment

by:PipMic
ID: 37226004
I have distributed the points.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question