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