Help Combining Multiple Scripts

[harryhoudini66]

Hello, I need help combining multiple scripts. On their own, each one works. I schedule them to proces via Windows Scheduler. I would prefer to schedule one script instead of three so I am trying to combine them. I am a total newb when it comes to scripts. In fact, some of them I created through the help in these forums or by googling. When I run the script below, I get a message indicating their is an issue with Line 11, character 1. Not sure what the problem may be.


CreateObject("WScript.Shell").Run "\\fileserver2\msc\MSC_Reports\Interval\Scripts\Intervals\Morning_Intervals.acsauto"
Delay 60

Sub Delay( seconds )
Dim wshShell
Set wshShell = CreateObject( "WScript.Shell" )
wshShell.Run "ping -n " & ( seconds + 1 ) & " 127.0.0.1", 0, True
Set wshShell = Nothing
End Sub

Option Explicit
Dim fso, f, sFile, sCSVPath, xl

Dim wb

Set xl = CreateObject("Excel.application")

sCSVPath = "\\fileserver2\MSC\MSC_Reports\Interval\Output"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(sCSVPath)
For Each sFile In f.Files
If UCase(fso.GetExtensionName(sFile.Name)) = "CSV" Then
OpenFileInExcel sFile.Path
End If
Next

OpenFileInExcel "\\fileserver2\MSC\MSC_Reports\Interval\MSCInteReportvWindows_7.xlsx"

'sorry that I missed this...
xl.DisplayAlerts = False
For Each wb In xl.Workbooks
With wb
If UCase(Right(.Name, 3)) <> "CSV" then
.Save
End if
.Close
End With
Next

Sub OpenFileInExcel(sFilename)

Set wb = xl.Application.Workbooks.Open(sFilename)

xl.Application.Visible = True

End Sub

xl.quit

Dim objOutl
Set objOutl = CreateObject("Outlook.Application")
Set objMailItem = objOutl.CreateItem(olMailItem)
objMailItem.Display
strEmailAddr = "mail@mail.com"
objMailItem.Recipients.Add strEmailAddr
objMailItem.Subject = "Interval Report "& Date &" for 7-11"
objMailItem.HTMLBody = "Text"
objMailItem.Attachments.Add "\\fileserver2\MSC\MSC_Reports\Interval\MSCInteReportvWindows_7.xlsx"
objMailItem.Display
Set objMailItem = nothing
Set objOutl = nothing

 

[PHV]

Option Explicit must be at the very top of the script ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[harryhoudini66]

Thanks. That did work. Most of it completed. I did get an error though with line 54, character 1.Any ideas?

 

[PHV]

Please, post the error MESSAGE too ..
Anyway, where is olMailItem defined ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[harryhoudini66]

Thanks. You are correct. The error actually indicates olMailItem not defined. Not sure what that piece does in particular. It works when I run the scripts by itself. It only complains when I combined it witht the other entries.

 

[PHV]

Well, seems like you don't know what you're doing ;-)
Add this before you use olMailItem:
Const olMailItem = 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[harryhoudini66]

That would be a correct assesment. I am learning. Thank you so much for your help and patience.

 

[harryhoudini66]

Okay, I see. The Option Explicit tells the script to stop processing if there is an error or something not defined. Without Option Explicit it would skip that step and would go to the next one.

What does olMailItem do by the way?

 

[PHV]

What does olMailItem do by the way
Tell Outlook that you want to create, say, a mail item ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[harryhoudini66]

Thanks again. Removing the Option Explicit made it all work. I know it is not clean code but it works for now. Much appreciated.

 

[guitarzan]

The Option Explicit tells the script to stop processing if there is an error or something not defined
Half right... it just forces variables to be defined. It does more than make code "clean"; it prevents you from making a typo in a variable name that could give you unexpected results and very hard-to-debug errors.

 

[harryhoudini66]

Thank you so much for the clarrification. Much appreciated.