Get mail from outlook.
This script uses an ini file for configuration. It will read the inifile and logon to the mailbox defined in the inifile. It will then loop through the mailbox and find mails with the subject defined in the inifile. It will then save the first attachement to the path defined in the inifile. If the attachment is saved, the script will start a dts package on a sqlserver.
I use this script to automate the import of data received in an excel spreadsheet in a table in SQL server. Outlook has to be installed on the pc that runs this script!
On Error Resume next
'Loop through outlook, save attachment and start dts package on sql server!
Dim objSession
Dim objIs
Dim objFldSource
Dim objFldDestination
Dim objMessage
Dim objAttachment
Dim ivalue
strIniFile = "MailRead.ini"
strSection = "Config"
strMapiProfile = ReadINI(strINIFile, strSection, "MapiProfile")
strMailBox = ReadINI(strINIFile, strSection, "MailBox")
strSubject = ReadINI(strINIFile, strSection, "Subject")
strPath2Save = ReadINI(strINIFile, strSection, "Path2Save")
strDTSServer = ReadINI(strINIFile, strSection, "DTSServer")
strDTSPackage = ReadINI(strINIFile, strSection, "DTSPackage")
' Create Shellobject
Set ws = Wscript.CreateObject("WScript.Shell")
' Inloggen.
Set objSession = CreateObject("MAPI.Session")
Wscript.echo "Log on to profile : " & strMapiProfile
Call objSession.Logon(strMapiProfile)
' Opzoeken gewenste folder.
Set objIs = objSession.InfoStores(strMailBox)
Wscript.echo "Logged on to Mailbox : " & objIs.name
Set objFldSource = objIs.RootFolder
Set objFldSource = objFldSource.Folders(5)
Set objFldDestination = objFldSource.Folders(1)
'Next
' Loop through messages.
With objFldSource.Messages
For Index = .Count To 1 Step -1
With .Item(Index)
' Process the message.
If (LTrim(RTrim(.Subject)) = strSubject) Then
Set objMessage = objFldSource.Messages(Index)
Set objAttachement = objMessage.Attachments(1)
'save the attachment
objAttachement.WriteToFile (strPath2Save)
ivalue = ws.run("DTSRUN.exe /S " & strDTSServer & _
" /E /N " & strDTSPackage, 0, "True")
' Move the message.
Call .MoveTo(objFldDestination.ID)
End If
End With
Next
End With
' Uitloggen.
Call objSession.Logoff
Set objSession = Nothing
'*****************************************************************
'** Function: ReadIni
'** Version: 1.1
'** Created: 19-03-2003 11:02
'** Author: Adriaan Westra
'** E-mail:
'**
'** Purpose / Comments:
'** Reads a value from an iniFile
'**
'** Arguments :
'** strINIFile :Name of the IniFile to read
'** strSection :Section in the inifile to read from
'** strKey :Key of the value to read
'**
'** Changelog :
'** 19-03-2003 11:15 : Initial version
'** 19-03-2003 11:25 : Built in UpperCase Check
'**
'*****************************************************************
Function ReadINI(strINIFile, strSection, strKey)
Dim objFSO, objTextFile, strLine
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(strINIFile)
'loop through each line and check for key value
Do While Not objTextFile.AtEndOfStream
strLine = objTextFile.ReadLine
'wscript.echo strLine
If UCase(strLine) = UCase("[" & strSection & "]") Then
Do While Not objTextFile.AtEndOfStream
strLine = objTextFile.ReadLine
If UCase(Left(strLine, Len(strKey) + 1)) = _
UCase(strKey & "=") Then
ReadINI = Mid(strLine, InStr(strLine, "=") + 1)
Exit do
End If
Loop
Exit Do
End If
Loop
objTextFile.Close
End Function
The inifile should look like this:
[Config] ;name of outlook profile to use MapiProfile=MS Exchange Settings ;Name of mailbox as it appears in outlook MailBox=Mailbox - VBUser ;the subject to check on Subject=Import ;path and name of the file to save Path2Save=c:\temp\import.xls ;name of th sql server to run the dts package on DTSServer=MySqlServer ;name of the dts package to run DTSPackage=ExcelImport
