Solved

Need an automated solution

Posted on 2004-10-12
27
207 Views
Last Modified: 2010-04-06
I have a process set up (ASP form) to collect data from an applicant, sent it to an access database, and email it in snapshot format at a certain time every day.  This is currently set up with Windows Task Scheduler, which opens FTP to transfer the file from the server at a specific time of day, following which the scheduler runs Access to generate the report and email it in snapshot format, and then scheduler runs FTP again to transfer the updated database back to the server.  The issue I'm having with using this method is that all applications are in one report.  Ideally, I would like this process to occur each time a new application is submitted.  This will allow HR to simply forward the application to the hiring manager.  With the current set up, the file has to be printed, scanned, and then forwarded via email in order to only send the appropriate applications to the appropriate managers.  Does anyone know of a more efficient way of getting separate applications emailed to HR in a format that resembles the paper application currently in use?
0
Comment
Question by:johnson00
  • 13
  • 12
  • 2
27 Comments
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12286585
You could have the "application process" generate an HTML formatted email, which exactly replicates your standard application form and sends itself to both HR and the hiring manager (or whichever recipients you'd prefer).
Alternatively HR will be wanting to control where the applications go to so instead of emailing all you could email a basic alert to the HR dept who can in turn log on to an administration section of your website where they can define where each application goes - From this point the HTML formatted email would be generated and sent to the correct hiring manager.

Lots of different solutions are available to you .... just a case of thinking what would work best for your setup - even if you think your ideal solution is impossible it can probably be done relatively easily.
 
As for the snapshot you're currently doing -  seems a bit of overkill taking a snapshot of the database every time someone submits an application but you may have other reasons for this - although I'm can't think of any.


0
 
LVL 2

Author Comment

by:johnson00
ID: 12286869
You could have the "application process" generate an HTML formatted email, which exactly replicates your standard application

How would I do this?  We had the results being emailed back to HR as a straight text file, which wasn't acceptable, and were using ASP to accomplish this.  I'm still new to ASP, so if that's what you're proposing, please be very detailed and provide an example.  The unclear part, to me, in returning an HTML formatted email is how you would get the applicant's response to populate the HTML fields in the email.
0
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12287124
Yep no problem... I'll provide an example if you let me know what email component on the webserver you would prefer to use. Hint.... I know the syntax for talking to the following components pretty well:

1 .CDOSYS
2. CDONTS
3. Jmail
4. Persits AspEmail email
5. Server Objects AspMail
0
 
LVL 2

Author Comment

by:johnson00
ID: 12287188
cdonts
0
 
LVL 3

Expert Comment

by:gnudiff
ID: 12287189

Something like that:

-1 ASP gets the data from the current applicant;
-2 ASP populates a preset HTML template with the data
-3 ASP sends the email to the HR.

step 1 is already in place,

as for step 2, there are options. One of the simplest I can imagine is like this...

you make a dummy HTML file which is as close to the printed report as needed - if you send it by email, the HR is happy.

In place of where you would like to insert the fields from user's application, you just leave a string, such as one that wouldn't be repeated elsewhere in the document. normally:
:
"
Dear HR Manager,

This is an application from ####NAME####, who is looking to fulfill the position of ####POSITION####.
etc.
"

Then, in ASP you just read the HTML file into a string.
Replace all the #### fields with the collected input from the form, like:

TemplateString = Replace(TemplateString, '####NAME####', collectedName);
TemplateString = Replace(TemplateString, '####POSITION####', collectedPosition);
etc..

and then send the resulting TemplateString via email using ASPs email client feature, if there is any.


0
 
LVL 2

Author Comment

by:johnson00
ID: 12287314
Are collectedName and collectedPosition the form field names?  Where do I let it know what html template to put this information into?
0
 
LVL 3

Expert Comment

by:gnudiff
ID: 12287676
>> Are collectedName and collectedPosition the form field names?  
Yes, basically, example names.

>> Where do I let it know what html template to put this information into?

From your description, it should be within the ASP file, when it has collected the info from the webpage, and is currently about to "send it to an access database".
0
 
LVL 2

Author Comment

by:johnson00
ID: 12287780
The following:

rs.Fields("OTHER") = Request.Form("OtherInformation")
rs.Fields("INIT") = Request.Form("INIT")

'Write the updated recordset to the database
rs.Update

is how I send the information to the database.  This actually uses two asp pages.  One asp page collects information from the applicant, and when the user clicks submit, it accesses the ASP page that actually collects the data from the form and sends it to the database.  Will the collection and sending now be combined in one document?  

Sorry for the confusion.  As I said, I'm new to all of this.
0
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12288173
OK this is simplified and hopefully easy to follow so here we go.

We'll assume we've got 3 files:
1. index.html        -  (the form, which the application fills in)
2. apply.asp         -  (Index.html's form action is set to post to this ASP-VBscript file)
3. template.html   - (the HTML template which we'll use to format the email).


INDEX.HTML
========
<!--  form is set out like so -->

<form method="POST" action=apply.asp>
     <input type="text" id="name" name="name" />
     <input type="text" id="email" name="email" />
     <input type="text" id="phone" name="phone" />
     <input type="submit" value="Apply" />
</form>


APPLY.ASP
=======

<%
Dim strName
Dim strEmail
Dim strPhone

strName   = request.form("name")
strEmail  = request.form("email")
strPhone  = request.form("phone")



' This function will take your html template and apply the form input
Function generateHTML(filename)
  Dim objFSO
  Dim objTStream
  Dim strText
  Const ForReading = 1

  Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
    Set objTStream = objFSO.OpenTextFile(Server.MapPath(filename), ForReading)
      strText = objTStream.ReadAll
    Set objTStream = Nothing
  Set objFSO = Nothing

  strText = Replace(strText, "#### Name ####", strName)
  strText = Replace(strText, "#### Email ####", strEmail)
  strText = Replace(strText, "#### Phone ####", strPhone)
  generateHTML = strText
End Function



' Insert code here to save application to database
' blah blah blah creating a new record on the database
' blah blah blah - dunnit


' Now email HR with the app using the template you set up.

Set objEmail = Server.CreateObject("CDONTS.NewMail")
  objEmail.BodyFormat = 0 'CdoBodyFormatHTML  
  objEmail.MailFormat = 0 'CdoMailFormatMime
  objEmail.From = strEmail
  objEmail.To = "hr@johnson00work.com"
  objEmail.Subject = "New application"
  objEmail.Body = generateHTML("template.html")
  objEmail.Send
Set objEmail = Nothing

%>


TEMPLATE.HTML - dont write the HTML, HEAD, BODY tags (The email will insert them for you)

<table>
  <tr>
     <th>Name</th>
     <th>Email</th>
     <th>Phone</th>
  </tr>
  <tr>
     <td>#### Name ####</td>
     <td>#### Email ####</td>
     <td>#### Phone ####</td>
  </tr>
</table>


=================================
Obviously that HTML isn't exactly what your looking but the above code should work as an example (haven't tested it though). Just generate the html as you see fit.


Hope that helps,

Whisp




0
 
LVL 2

Author Comment

by:johnson00
ID: 12288405
How would this work for check boxes and option sections?
0
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12288583
You must be saving those values to the database so exactly the same way.
Hopefully you're not intending to format the HTML email with option selects and check boxes? That would be pointless seeing as the recipient doesn't need to interact with the email
0
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12288608
...bit more helpfull...
If you're intending to mimick the input form in the email then perhaps replace checkboxes with images of ticks. Option selections could just be a textbox
0
 
LVL 2

Author Comment

by:johnson00
ID: 12288702
I agree it's pointless, but HR wants the email they receive to look exactly like the paper application, which has checkboxes and option selects.  If I can pull off an email that looks like the paper form, then there's really no point in putting any of the information into a database.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 3

Expert Comment

by:WhisperUK
ID: 12288810
option selects in a paper application?  is it a pop-up book :oD   or did you mean radio buttons?

Tell ya what... you got a URL to the application form and I'll have a look so we're singing off the same songsheet  ;)



0
 
LVL 2

Author Comment

by:johnson00
ID: 12288840
Yes, radio buttons!  Sorry!  The form that collects the data can be accessed at the following:

http://www.springfieldclinic.com/spfldclinicscripts/employment1.asp

Pop-up book, that's good!!  :)
0
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12288844
ps..... keeping a record in the database is a good idea as the email may fail due to email server troubles from time to time but if you've got the application saved to a DB then you can always store a flag for each application to determine whether the email was successful.  keeps the client/applicant sweet and HR can keep their scary smiles going for a while longer ;)
0
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12288930
ok had a look at your page and can see some more potential problems for you so gimme a couple of hours and I'll have a solution for you. it's the end of the working day for us Brits and I'm off home to beat up the wife and kids (joke) so I'll respond when I get there and have had some food.


cya soon  :)
0
 
LVL 2

Author Comment

by:johnson00
ID: 12289016
Good point on the database.  I'm not sure how to flag it to let it know if the email was successful or not, but there's always google to help me figure it out.  I'm anxious to see your solution, as I've been researching how to accomplish this for some time, and have had other users here on EE tell me that what I wanted just wasn't possible, so I'm glad you have some ideas to send my way!  You're also good at explaining, which is what I need!
0
 
LVL 3

Accepted Solution

by:
WhisperUK earned 500 total points
ID: 12292282
ok sorry about the wait - sometimes have to do life stuff ;)

I've set up a "proof of concept script" for you to use. Just go to http://www.kirmik.co.uk/dev/test/ and fill in the stuff. It's a part replica of what your page is (kind of). Fill in your own email address as the applicant and the result will be emailed to you as picked up from the template. You'll obviously want to change that to the HR dept email address but I've commented the code well enough for you to work that out ;)

points to note:
1. If you're going to have radio buttons in a group you need to give them all the same "name=" attribute so that the browser knows only one can be selected. Also give each one a different "value=" attribute so that we determine which one has been "checked" when the form is posted. In your online form you can check both radio buttons - try it and you'll see. To remedy your page you would want to put something like:
<input type="radio" name="SCEmployed" value="TRUE"> Yes
<input type="radio" name="SCEmployed" value="FALSE"> No
(notice that both are named "SCEmployed" and are value TRUE or FALSE)

2. If you're going to have images in your HTML email then you'll need to have complete URLs as they dont get sent with the email

===========================================
Ok I've done some of the work for you, which covers the input fields, combo boxes, radio buttons, check boxes & TEXAREAs..... the rest should keep you busy for a few hours  :o)

Here's the source:  http://www.kirmik.co.uk/dev/test/emailForm.zip
0
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12295227
forgot to mention:
When I said "dont write the HTML, HEAD, BODY tags (The email will insert them for you)" it doesn't mean you CAN'T put the full html in there - In my working example  I kept them in there to keep your stylesheet defenitions.

0
 
LVL 2

Author Comment

by:johnson00
ID: 12297774
Wow!  I haven't had a chance to look it all over yet, but it certainly looks like you've been busy!  I'm in training all day today and won't have a chance to take a look at all you've done until tomorrow sometime.  I REALLY appreciate it though.  From your descriptions, it sounds like you've provided me with exactly the type of help I'm looking for.  

Stay tuned, and enjoy your "life stuff"!  This stuff does seem like its own universe at times!
0
 
LVL 2

Author Comment

by:johnson00
ID: 12312935
THANK YOU THANK YOU THANK YOU!!

It works beautifully except for the radio buttons.  The checkboxes worked great!  I chose No for previously employed, and didn't receive a marked radio button in the email I received.  Any other suggestions for that area?
0
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12313312
Yep - purely a typo on my part  - sorry

To fix it Find:

  if strEmployed = "TRUE" then
      strText = Replace(strText, "#### SCEMPLOYED_TRUE ####", "checked")
        strText = Replace(strText, "#### SCEMPLOYED_TRUE ####", "")
  else
      strText = Replace(strText, "#### SCEMPLOYED_TRUE ####", "")
        strText = Replace(strText, "#### SCEMPLOYED_TRUE ####", "checked")  
  end if

And change it to:

  if strEmployed = "TRUE" then
      strText = Replace(strText, "#### SCEMPLOYED_TRUE ####", "checked")
        strText = Replace(strText, "#### SCEMPLOYED_FALSE ####", "")
  else
      strText = Replace(strText, "#### SCEMPLOYED_TRUE ####", "")
        strText = Replace(strText, "#### SCEMPLOYED_FALSE ####", "checked")  
  end if


That should work  :o)
Now you can blow a big raspberry to those who thought it wasn't possible   :-D
0
 
LVL 3

Expert Comment

by:WhisperUK
ID: 12313392
and also you asked how to trap the email send error........ here you go:

Set objEmail = Server.CreateObject("CDONTS.NewMail")
  objEmail.BodyFormat = 0 'CdoBodyFormatHTML  
  objEmail.MailFormat = 0 'CdoMailFormatMime
  objEmail.From = strEmail
  objEmail.To = "hr@johnson00work.com"
  objEmail.Subject = "New application"
  objEmail.Body = generateHTML("template.html")
  objEmail.Send

  ' check for an error
  On Error Resume Next
  If Err <> 0 Then
     ' Flag the database record to show that the email failed
  End If

Set objEmail = Nothing

It may be advisable to open the connection to the database, create a new record, save form values, send email, check for error and log in database then save and close the recordset. That way you do everything in one hit and dont have to go searching for the record twice.

Job done & I need a beer  :oD
0
 
LVL 2

Author Comment

by:johnson00
ID: 12321156
Wow!  This is complicated!

I changed the code for the radio buttons, and still no luck.
0
 
LVL 2

Author Comment

by:johnson00
ID: 12322218
I got it working!  Typo on my part.  Sorry!!
0
 
LVL 2

Author Comment

by:johnson00
ID: 12322228
I can't thank you enough for your help!  You deserve a whole case of beer!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
It's sometimes a bit tricky to use date functions in Oracle BPEL. I'll explain quickly how you can add N days to the current date. In a BPEL process this can be useful, and you can adapt it to fit your needs. First of all, let's see how to add 1 …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to count occurrences of each item in an array.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now