Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help Combining Multiple Scripts

Status
Not open for further replies.

harryhoudini66

Technical User
Jun 20, 2006
90
US
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
 
Option Explicit must be at the very top of the script ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks. That did work. Most of it completed. I did get an error though with line 54, character 1.Any ideas?
 
Please, post the error MESSAGE too ..
Anyway, where is olMailItem defined ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
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
 
That would be a correct assesment.:) I am learning. Thank you so much for your help and patience.
 
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?
 
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
 
Thanks again. Removing the Option Explicit made it all work. I know it is not clean code but it works for now. Much appreciated.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top