We help IT Professionals succeed at work.

Windows batch file to trigger e-mail notification.

Hi,I am a newbie to windows scripting.I haven't written a single one by myself yet.Now I want to write a script which would accept an arguement and based on that would need to read a file and send e-mails to a list of stake holders.
.
The arguemnt could be a character string of 5 to 10 size,say clientD  OR a folder path.The file to be read can be either a notepad or an excel file.The file would have records of type(client ID,E-mailID).
.
Then upon the checkin event in the version manager tool,this script should run and send a notification to the id-s.
.
The e-mail client is lotus notes,I would need to create a sample script in my windows pc (windows xp/SP3) to test the file reading and e-mail part and then should hand over that to the version manager administrators.
.
Could someone help me to figure out how to do this ?
Comment
Watch Question

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
That's really a lot of things you want to do. The text input file would be no problem, but batch files can't read XLS files. VBScript can, however.
  1. What do you mean by "The arguemnt could be a character string of 5 to 10 size,say clientD  OR a folder path"? Can it really be one of both, changing each time? And if so, how to differentiate between both?
  2. What do you mean by "either a notepad or  an excel file"? Is that dynamic again, or can you determine the format?
  3. Do you know how to send emails via Lotus Notes using a batch command? If not, does Lotus Notes accept standard SMTP mails?
  4. What is the body of the mails to send?

Author

Commented:
1.What do you mean by "The arguemnt could be a character string of 5 to 10 size,say clientD  OR a folder path"? Can it really be one of both, changing each time? And if so, how to differentiate between both?
==>I can determine what it should be.This would be an argument that would come from Serena Version manager upon a trigger of an event of checking in a file.or more precisely it won't be dynamic.either of the one.
2.What do you mean by "either a notepad or  an excel file"? Is that dynamic again, or can you determine the format?
====>Again its not dynamic.Once decided its going to be that.If notepad is the convenient one ,I can stick onto that.But if its possible I would like to stick onto excel,only if that won't cause too much of extra coding.
3.Do you know how to send emails via Lotus Notes using a batch command? If not, does Lotus Notes accept standard SMTP mails?
=====>I have no clue about those functionalities in windows scripting.
4.What is the body of the mails to send?
======>Its going to be a static text with an exception that argument should be used to understand the client id and incorporate in the body.Say for eg:if the argument is decided to be text,"001123", I would need to filter 1123 from that string and understand that is client "ABC".
So the flow would be this:
You'll need to work out how/if "Serena Version Manager" will trigger your VBS based on a check in.
I'm fairly certain Lotus accepts communications over SMTP
Can you clearly state all of the arguments that you want the script to pick up, and where the values for these arguments come from?
 
 

Author

Commented:
This is how the PriorCheckin.bat  - The scrpt that I need to write- would be called  from VM with parameters passing being workfile name,version and path.
EVENTTRIGGER = PrePut   <Full qualified path>\PriorCheckin.bat __EventWorkfile__ __EventVesrion__ __EventPath__.
I assume ;
set FileCheckedIn=%1 would be the way to read the argument for further processing.
But the important thing that I need to know is how to get the Lotus notes client  send e-mail .

Author

Commented:
Not necessarily a lotus notes client but SMTP server (built in ?) in windows xp.Is that doable via a script or do i need to write a vba script to call in the batch script ?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Pure cmd.exe needs an external tool, like BLAT (free), to send a SMTP message. IIRC sending an email via VBS is easy. So it is a matter of taste and preference.
A SMTP server is part of the IIS installation package, and can be installed on XP via the Software applet, Windows Components, IIS. That one would do the job well for sure.
Well you wouldn't need the SMTP server on IIS if you just sent the email directly to the lotus notes server.
I suggest at this point you download BLAT and try to get it working with your SMTP server (whic Lotus Notes should provide). Once you have verified that works, then you have verified that you can send emails from a batch file (via BLAT) or from VBS (via a generic SMTP object)
If you can't get SMTP communication to work then yes you'll need to install a Lotus Notes client and leverage that through VBS (assuming it exposes an interface). But you should avoid that at all costs.
I still don't quite understand the other bits you want but lets start with validating the email functionality.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I agree to that, let's check one by one.

For the other parts, you could provide one example of input and output, including the file content to use, and how to convert one to the other.

Author

Commented:
I cant request for a download of any software.Though its free , it takes a long process to get approval.I am on with writing a vba and then calling it in batch script.
[Another point , the script would be in a shared drive , yes ,must be a windows server for sure.]

.
I looked at the possibility of CDO.But I am not sure what is the server argument to be given.
Following is from EE itself.Could someone explain the server argument ?
-------------------------------------------------------------------------------------------------------------------
Sub SendByGMail()
     
    ' adapted from  http://www.mathworks.com/matlabcentral/fileexchange/22502

    Dim Msg As Object
    Dim Conf As Object
    Dim Flds As Object
     
    Const Schema As String = "http://schemas.microsoft.com/cdo/configuration/
    Const UserName As String = "your.gmail.user.name@gmail.com"
    Const Password As String = "your.gmail.password"
    Const SendTo As String = "foo@company.com,hee@company.com" ' modify as needed
    Const Sender As String = "Your Name"
     
    Set Msg = CreateObject("CDO.Message")
    Set Conf = CreateObject("CDO.Configuration")
    Set Flds = Conf.Fields
     
    Flds.Item(Schema & "sendusing") = 2
    Flds.Item(Schema & "smtpserver") = "smtp.gmail.com"
    Flds.Item(Schema & "smtpserverport") = 465
    Flds.Item(Schema & "smtpauthenticate") = 1
    Flds.Item(Schema & "sendusername") = UserName
    Flds.Item(Schema & "sendpassword") = Password
    Flds.Item(Schema & "smtpusessl") = 1
    Flds.Update
     
    With Msg
        .To = SendTo
        .From = UserName
        .Subject = "Simple plain text CDO example"
        .TextBody = "This is a test of my CDO code(sent using SMTP authentication)"
        .Sender = Sender
        .ReplyTo = UserName
        Set .Configuration = Conf
        .Send
    End With
     
    Set Msg = Nothing
    Set Conf = Nothing
    Set Flds = Nothing
     
End Sub
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
smtpserver is your SMTP server.
I don't think you will use S/SMTP (or SMTP/S), which is port 465, and need SMTP Authentication. So the part will be more like:

    Flds.Item(Schema & "sendusing") = 2
    Flds.Item(Schema & "smtpserver") = "MySmtpServer"
    Flds.Item(Schema & "smtpserverport") = 25
    Flds.Item(Schema & "smtpauthenticate") = 0
    ' Flds.Item(Schema & "sendusername") = UserName
    ' Flds.Item(Schema & "sendpassword") = Password
    Flds.Item(Schema & "smtpusessl") = 0
    Flds.Update
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
As you now need to use either VBS or VB for sending emails, I would do the remainder there, too. Should not be difficult.

Author

Commented:
My question is , where can I get the SMTPServername ? I do not know where to find that from.
Its probably the same as your Lotus Notes server name
Before you try Blat it might be worth doing a low level test on the SMTP port, along the lines of this link:
http://www.outlookpower.com/issues/issue200506/00001576001.html
This will allow to verify the state of SMTP communications, and next you can try BLAT. Note that these tests are for Outlook but it should be generally the same for Lotus Notes
 

Author

Commented:
As I said , I cant have a s/w installed for this.I would try the link that you have given.

Author

Commented:
I guess i can get the SMTP server.If I write using VBA the only way is to keep it as .xls ?Can I use the CDO object with VBScript ?
Yes you can use the CDO object with VBScript. VBA and VBScript are almost identical.
I find it is best to develop and debug in VBA then convert to VBScript (I've never come accross a VBScript development tool)
CDO needs to be installed in order for it to be used.... I think this requires the Outlook client to be installed. Here's some instructions on how to check:
http://www.blackberryforums.com.au/forums/microsoft-exchange/385-check-if-where-cdo-dll-file-registered.html
 
 

Author

Commented:
How would I conver to vbs ? It could be something more than just change in the file extension right ? Or its not ?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I do not see anything you need to change, but didn't test it. Changing the file extension to .vbs should be sufficient.
General guide to convert VBA to VBS:
1. Change all Set x = New (object) to Set x = CreateObject("object string")
2. Change all variable definitions to not have a type i.e. change 'Dim i as Integer' to 'Dim i'
3. Copy and paste the VBA code into a text file and rename the extension to VBS
 
There's more to it than this though.
Now, if all that is double dutch then you should really consider how you are going to break this up into completable chunks. Why don't you concentrate on the connectivity parts now
- Ensure that your version control system can trigger a batch file (which will trigger a VBS),
- Ensure that you can utilise your Lotus Notes server to send emails. (check SMTP connectivity)
If you can't do those two things then there is no point writing a script to do it.

Author

Commented:
- Ensure that your version control system can trigger a batch file (which will trigger a VBS),
---->I can wrap the vbs script in a windows script ,isnt it ?
- Ensure that you can utilise your Lotus Notes server to send emails. (check SMTP connectivity)
---->It does.Today I verified a sendEmail package in databse(Oracle) which uses SMTP server.
.
I would test is sometime this week or early next week.But could you now tell me how to read a file and get the content ?
.
Do you have a sample program ?
The file would look like :
201134:mike_bello@something.com:johnS@something.com
129833:Another@something.com
"I can wrap the vbs script in a windows script ,isnt it ? "
Yes you can but you also said that you wanted to trigger this from your version control program, right? so can your version control program trigger a windows script? If not then you can't call a script of any kind.
Now that you have mentioned Oracle, you could also write all of this in an Oracle package and possibly trigger that.
Attached is a sample VBS that opens a file and reads each line into a variable and shows it. It doesn't do anything else. Its pretty useless but it gives you a starting point.
Can you clairfy what is in that file? Does it consist of a piece if data followed by a colon followed by a list of emails (followed by a colon). Would you want to load the first field into one variable and the list of emails into your send field?

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\YourFile.TXT", 1)
Do Until objFile.AtEndOfStream
    Call MsgBox(objFile.ReadLine)
Loop

Open in new window

You paste that code into a text file, rename the .TXT extension to .VBS (the icon should change), then double click it.

Author

Commented:
thank you. tested that code.So I would use the vbs script to read and process file and send email , then call that in .bat script.
.bat script can be called by VM.I have confirmed it.
----------------------
But to know more about windows shell scripting : Would you be able to provide the same code for a .bat file ?
----------------------
Can you clairfy what is in that file? Does it consist of a piece if data followed by a colon followed by a list of emails (followed by a colon). Would you want to load the first field into one variable and the list of emails into your send field?
----------------------------
Yes.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Parsing your file in a batch file:

@echo off
REM expects the ID to search for as first parameter
setlocal EnableDelayedExpansion
for /F "tokens=1,2,3* delims=:" %%A in ('findstr /b /l /c:"%1" C:\YourFile.TXT') do (
   echo ID %%A goes to %%B
   set others=%%C
   if defined others for %%a in (!others::=,!) do echo     and %%a
)

It is more complex as the number of receipients is dynamic, and we have to parse them. If you provide the list of receipients in a suitable format for a mailing list, which is comma- or semicolon-separated, you do not need further processing.
OK so we have a few pieces we can tack rogether here:
1. VM calls a batch script, passing parameter(s) (not sure exactly what)
2. Batch script calls a VBS, passing parameters
3. VBS has access to passed parameters, a text file and a SMTP server. It cooks it all up and sends an email.
Correct?
Lets stick some of this stuff together.
The code below is an extension of the VBS above. However it also accepts two parameters

Dim ArgObj, var1, var2, objFSO, objFile
Set ArgObj = WScript.Arguments 

var1=ArgObj(0)
var2=ArgObj(1)

Call MsgBox("Parameter 1: " & var1 & Chr(10) & Chr(13) & "Parameter 2: " & var2)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Temp\YourFile.TXT", 1)
Do Until objFile.AtEndOfStream
    Call MsgBox(objFile.ReadLine)
Loop

Open in new window

The code below is a batch file which calls the VBS script above (assuming you have saved it into C:\temp and called it TEST.VBS)
It passes in two hard coded parameters.
So paste the code below into a text file and rename the text file extension to .CMD then run it.
This is something that you can call from your VM. However note that the parameters are hard coded.
Then next code snippet will not be hard coded.

WScript C:\Temp\test.VBS P1 P2

Open in new window

OK, here is code for a batch file which passes two parameters into your vbscript.
In your C:\temp folder (along with all of your other files), create a file called param.cmd and edit it. Paste the below code in. Now you can call it like this:
Param MyParam1 MyParam2
and it will pass MyParam1 and MyParam2 into the VBScript (test.VBS). The VBScript will pick those up and msgbox them.
 
So Param.CMD is a batch script that can be called by your VM and appended with appropriate parameters (also supplied by your VM)
You can manually test it by going to a DOS prompt and typing
C:\Temp\Param.CMD MyParam1 MyParam2
So....
1. VM calls param.cmd with appropriate parameters
2. param.cmd calls vbscript and passes those parameters
3. vbscript now has parameter values, file contents, and later on, ability to email stuff.
Does that sound like we are heading in the right direction?

WScript C:\Temp\test.VBS %1 %2

Open in new window

Taking the libery of latering your unput file as follows:
(replace email seperator with ; rather than :)
201134:mike_bello@something.com;johnS@something.com
129833:Another@something.com
The attached VBS code splits the start field and the emails apart:
 

Dim ArgObj, var1, var2
Dim objFSO, objFile
Dim sLine, sField, sEmail

Set ArgObj = WScript.Arguments 

var1=ArgObj(0)
var2=ArgObj(1)

Call MsgBox("Parameter 1: " & var1 & Chr(10) & Chr(13) & "Parameter 2: " & var2)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Temp\YourFile.TXT", 1)
Do Until objFile.AtEndOfStream
    sLine = objFile.ReadLine
    sField = Left(sLine, InStr(sLine,":")-1)
    sEmail = Right(sLine, Len(sLine) - Len(sField)-1)
    MsgBox("Field: " & sField & Chr(10) & Chr(13) & "eMail: " & sEmail)
Loop

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Don't you think any of our comments was of some help?
What you;'re saying meera78 is that none of us have been of any assistance. I don't agree but rather than object I will simply refrain from assisting with any further questions.

Author

Commented:
Served the purpose