Option Compare Text
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()
    Dim olApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Set olApp = Outlook.Application
    Set objNS = olApp.GetNamespace("MAPI")
    Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub Items_ItemAdd(ByVal Item As Object)
On Error GoTo ErrorHandler

    'Only act if it's a MailItem
    Dim Msg As Outlook.MailItem
    Dim i As Integer
    Dim ExecuteBatchScript As Boolean
    
    If TypeName(Item) = "MailItem" Then
        Set Msg = Item


    'Change variables to match need. Comment or delete any part unnecessary.
    'If (InStr(1, Msg.Subject, "", vbTextCompare) <> 0) And _
                '(Msg.Attachments.Count >= 1) Then
    If (InStr(1, Msg.Subject, "Round", vbTextCompare) <> 0) And _
        (Msg.Attachments.Count >= 1) Then
       
    'Set folder to save in.
    Dim olDestFldr As Outlook.MAPIFolder
    Dim myAttachments As Outlook.Attachments
    Dim FileName As String
    Dim FileNameNoExtension As String
    Dim RndID As String
        
    'location to save in.  Can be root drive or mapped network drive.
    'Const attPath As String = "C:\Downloads\Outlook_Round_Modules\"
    Const attPath As String = "J:\Grower\Round_Modules\Crop2011\Processed\"
       
       
    ' save attachment in format Batch literal + Datetime stamp + 6 digit random number
    '                            + '.' + file extension, which is either "csv" or "ERR" for unknown error file type
   Set myAttachments = Item.Attachments
   Randomize
    
    'Att = FindGrowerID(Msg.Subject) & "_" & _

    For i = 1 To myAttachments.Count
        RndID = CStr(Int((999999 + 1) * Rnd))
        
        FileNameNoExtension = "Batch_" & _
        Format(Now(), "YYYYMMMDD_hhmmss") & "_" & _
        RndID

        FileName = FileNameNoExtension & GetFileExtension(myAttachments.Item(i).FileName, ExecuteBatchScript)
        
        myAttachments.Item(i).SaveAsFile attPath & FileName
        
        If ExecuteBatchScript = True Then
            Shell "J:\Grower\Round_Modules\Crop2011\Master_scripts\RoundModule_Generate_Scripts_Master.bat "
 & FileNameNoExtension & " " & RndID, vbMaximizedFocus
        End If
       
       ' The sleep command pauses 5 server seconds in order to give the sql 
step 2 time to update the batch file name
       ' with the csv file name for the csv file processed, before the next 
csv is processed and appends more records to the submodules table
       ' Sleep 1000
    Next i
        
    ' mark as read
   Msg.UnRead = False
End If
End If
    

ProgramExit:
  Exit Sub
  
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

'Private Function FindGrowerID(Subject As String) As String
'    Dim StartIndex As String
'    Dim i As Integer
'
'    Dim GrowerID As String
'
'    StartIndex = InStr(1, Subject, "grower", vbTextCompare)
'    For i = (StartIndex + 6) To Len(Subject)
'        If i = Len(Subject) Or (IsNumeric(Mid(Subject, i + 1, 1)) = False) Then
'            FindGrowerID = Mid(Subject, StartIndex, ((i + 1) - StartIndex))
'            Exit For
'        End If
'    Next i
'End Function

Private Function GetFileExtension(FileName As String, ByRef ExecuteBatchScript As Boolean) As String
    Dim i As Integer
    Dim extension As String

    For i = 1 To Len(FileName)
        If Mid(FileName, i, 1) = "." Then
            extension = Mid(FileName, i)
        End If
    Next i
    
    ExecuteBatchScript = True
    If Not extension = ".csv" Then
        ExecuteBatchScript = False
        extension = extension & ".ERR"
    End If
    
    GetFileExtension = extension
End Function