email notification of new file arrival

S

SQLcat

I currently have a job that populates an Oracle db and once finished, it lays
a 1kb text file with its results in a directory. I'd like to have this file
emailed to me once the job completes. I see there is a product
http://www.youvegotfiles.com/ that does the trick but I want to accomplish
this without paying a price for someone elses product.

Any thoughts/suggestions on how to programmatically automate this task?
 
H

Helmut Obertanner

Hello, if you have a SMTP Service on your Windows-machine running, you could
save emailfile to pickupfolder, or you can use a script that sends the
message with cdo.

can you trigger a vbs-script after running your import ?
--
Freundliche Grüße / with regards

Helmut Obertanner
Technical Consultant

Softwaredevelopment
DATALOG Software AG | Zschokkestr. 36 | D-80687 Munich
web: www.datalog.de


.... and IT works!
 
S

SQLcat

The emailfile is laid out in its own directory around 10:30am each day. I
don't know how to effectively code a VBS that will send me the notification.
Honestly, I just want to do what "youve got files" already does as outlined
via the link provided.

I'm sure this can be done, but don't know how to on my own.
 
H

Helmut Obertanner

Hello SQLcat,

here is an article how to send email from a script.
you can run this script every day at 10.45 with task scheduler

http://support.microsoft.com/default.aspx?scid=kb;en-us;286431

and here are some examples to send emails with attachments from script.
the CDO2000 is installed on every Win2K, XP and Win2k3 OS.

http://support.microsoft.com/?scid=kb;en-us;810702&spid=6384&sid=198#6

--
Freundliche Grüße / with regards

Helmut Obertanner
Technical Consultant

Softwaredevelopment
DATALOG Software AG | Zschokkestr. 36 | D-80687 Munich
web: www.datalog.de


.... and IT works!
 
H

Helmut Obertanner

Hell SQLcat.

here is a sample script, that sends a file "test.doc" from a directory
"dir1"
Why not try it - it's free and does the job.

you must change the
smarthost (should be your internal emailserver)
emailadresses (from, to)
directoryname
filename

You can't write scripts / no problem - many people can, i too :)

------------ save this as email.vbs ----------------------------

Dim iMsg
Dim iConf
Dim Flds
Dim strHTML
Dim strSmartHost

Const cdoSendUsingPort = 2
StrSmartHost = "10.11.0.18"

set iMsg = CreateObject("CDO.Message")
set iConf = CreateObject("CDO.Configuration")

Set Flds = iConf.Fields

' set the CDOSYS configuration fields to use port 25 on the SMTP server

With Flds
..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
cdoSendUsingPort
..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
strSmartHost
..Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 10
..Update
End With

' build HTML for message body
strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b> This is the test HTML message body</b></br>"
strHTML = strHTML & "</BODY>"
strHTML = strHTML & "</HTML>"

' apply the settings to the message
With iMsg
Set .Configuration = iConf
..To = "(e-mail address removed)"
..From = "(e-mail address removed)"
..Subject = "Here is your File test.doc from directory c:\dir1 (Sent via Port
25 on Server 10.11.0.18)"
..HTMLBody = strHTML

..AddAttachment "file://c:\dir1\test.doc"

..Send
End With

' cleanup of variables
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing

-------------------------------------------------------------------------



--
Freundliche Grüße / with regards

Helmut Obertanner
Technical Consultant

Softwaredevelopment
DATALOG Software AG | Zschokkestr. 36 | D-80687 Munich
web: www.datalog.de


.... and IT works!
 
S

SQLcat

I will give this a try. Thank you.

Helmut Obertanner said:
Hello SQLcat,

here is an article how to send email from a script.
you can run this script every day at 10.45 with task scheduler

http://support.microsoft.com/default.aspx?scid=kb;en-us;286431

and here are some examples to send emails with attachments from script.
the CDO2000 is installed on every Win2K, XP and Win2k3 OS.

http://support.microsoft.com/?scid=kb;en-us;810702&spid=6384&sid=198#6

--
Freundliche Grüße / with regards

Helmut Obertanner
Technical Consultant

Softwaredevelopment
DATALOG Software AG | Zschokkestr. 36 | D-80687 Munich
web: www.datalog.de


.... and IT works!
 
H

Helmut Obertanner

No, if you use a smarthost like in the script i posted.
The SMTP Server should be a server in your company, ask your Administrator
wich server you can use.
Normally it's your Exchangeserver.

--
Freundliche Grüße / with regards

Helmut Obertanner
Technical Consultant

Softwaredevelopment
DATALOG Software AG | Zschokkestr. 36 | D-80687 Munich
web: www.datalog.de


.... and IT works!
 
H

Helmut Obertanner

Hello SQLcat,

can you try if your smtp host is reachable from the computer where you run
the script ?
open a command promt
type:
telnet ip.of.smtp.server 25

did you get an answer saying helo .....

is the sender (from) address is correct ?
some mailservers block not valid email addresses

--
Freundliche Grüße / with regards

Helmut Obertanner
Technical Consultant

Softwaredevelopment
DATALOG Software AG | Zschokkestr. 36 | D-80687 Munich
web: www.datalog.de


.... and IT works!
 
S

SQLcat

turns out, with some tweaking to the VB script, I'm able to get this working.
We're almost there though....the file name changes each day after the job
completes before 11am. How do I get this to pick off only that file? By
modified date? I'm sure theres something that can additionally be tweaked in
the VBS.....

Thoughts?
 
H

Helmut Obertanner

Hello SQLCat,

this gets the file wich was created today:
replace with your AddAttachement function, and replace the directoryname.

Dim objFSO
Dim objDir

Dim objFile
Dim strFileName

'Create a FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Get the Folder
Set objDir = objFSO.GetFolder("c:\dir1")

'Loop over the Files and get the File from today
For Each objFile In objDir.Files
'Check the createddate
If FormatDateTime(objFile.DateCreated, 2) = FormatDateTime(Now, 2) Then
strFileName = objFile.Path
End If
Next

' cleanup of variables
Set objFile = Nothing
Set objDir = Nothing
Set objFSO = Nothing

..AddAttachment strFileName




also here is a reference to VBScripting...
<http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28001169>

--
Freundliche Grüße / with regards

Helmut Obertanner
Technical Consultant

Softwaredevelopment
DATALOG Software AG | Zschokkestr. 36 | D-80687 Munich
web: www.datalog.de


.... and IT works!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top