Need an automated solution

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?
LVL 2
johnson00Asked:
Who is Participating?
 
WhisperUKConnect With a Mentor Commented:
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
 
WhisperUKCommented:
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
 
johnson00Author Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
WhisperUKCommented:
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
 
johnson00Author Commented:
cdonts
0
 
gnudiffCommented:

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
 
johnson00Author Commented:
Are collectedName and collectedPosition the form field names?  Where do I let it know what html template to put this information into?
0
 
gnudiffCommented:
>> 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
 
johnson00Author Commented:
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
 
WhisperUKCommented:
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
 
johnson00Author Commented:
How would this work for check boxes and option sections?
0
 
WhisperUKCommented:
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
 
WhisperUKCommented:
...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
 
johnson00Author Commented:
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
 
WhisperUKCommented:
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
 
johnson00Author Commented:
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
 
WhisperUKCommented:
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
 
WhisperUKCommented:
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
 
johnson00Author Commented:
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
 
WhisperUKCommented:
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
 
johnson00Author Commented:
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
 
johnson00Author Commented:
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
 
WhisperUKCommented:
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
 
WhisperUKCommented:
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
 
johnson00Author Commented:
Wow!  This is complicated!

I changed the code for the radio buttons, and still no luck.
0
 
johnson00Author Commented:
I got it working!  Typo on my part.  Sorry!!
0
 
johnson00Author Commented:
I can't thank you enough for your help!  You deserve a whole case of beer!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.