JazzSP8
Well-known Member
- Joined
- Sep 30, 2005
- Messages
- 1,227
- Office Version
- 2013
- 2010
- Platform
- Windows
Hi Guys
I've created a spreadsheet which automatically imports files, runs a couple of tried and tested macros every hour and then Emails (via Outlook) the results out to about 30 people.
I use the Application.OnTime method;
This launches this bit of code;
I trialed this for about a day and it worked perfectly, then yesterday I had it running "live", it seemed to be working ok, but after running for 6 hours (meaning it had run 6 times) it went on a rampage and sent its 7th Email seven times causing me a flood of phone calls and a royal pain. I couldn't find the soloution, or cause it to replicate yesterday and all seems fine again this morning, the only thing different between the trial run and today is that I have the Emails on manual sending
This is the code I used to send the Email (found on this board).
Yesterday when "live" the Application.Sendkeys line wasn't commented out, but thats the only difference.
The spreadsheet is running on Windows 2000 using Office 2000 and pretty much left alone apart from a couple of scedhuled reports, none of which run on the hour.
Can anyone see why it would suddenly go hyperactive on me, or give me something else to look at or think about?
I've created a spreadsheet which automatically imports files, runs a couple of tried and tested macros every hour and then Emails (via Outlook) the results out to about 30 people.
I use the Application.OnTime method;
Code:
Sub ActivateOnTime()
Application.OnTime TimeValue(Hour(Now) + 1 & ":00:00"), "Sheet2.AutoUpdaterDay"
End Sub
This launches this bit of code;
Code:
Sub AutoUpdaterDay()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.StatusBar = "Generating Auto Update"
Module1.ImportData
Sheet4.DeleteOtherRows
Sheet4.Tidy
Sheet3.DeleteSkillsetRows
Sheet3.TidyUp
Sheet2.Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = " "
Sheet2.Email
Module1.ActivateOnTime
End Sub
I trialed this for about a day and it worked perfectly, then yesterday I had it running "live", it seemed to be working ok, but after running for 6 hours (meaning it had run 6 times) it went on a rampage and sent its 7th Email seven times causing me a flood of phone calls and a royal pain. I couldn't find the soloution, or cause it to replicate yesterday and all seems fine again this morning, the only thing different between the trial run and today is that I have the Emails on manual sending
This is the code I used to send the Email (found on this board).
Code:
Sub Email()
Dim appOutlook As Object
Dim MailItem As Object
Dim FSObject As Object
Dim myTo As String
Dim myCC As String
Dim mySub As String
Set rngeSend = Application.Range("A1:D15")
Set FSObject = CreateObject("Scripting.FilesystemObject")
Set appOutlook = CreateObject("Outlook.Application")
Set MailItem = appOutlook.CreateItem(olMailItem)
tmpFile = FSObject.GetSpecialFolder(2)
tmpFile = tmpFile & "\myRange.htm"
ActiveWorkbook.PublishObjects.Add(xlSourceRange, tmpFile, rngeSend.Parent.Name, _
rngeSend.Address, xlHtmlStatic).Publish True
Set TStream = FSObject.OpenTextFile(tmpFile, 1)
strHTMLBody = TStream.ReadAll
strHTMLBody = Replace(strHTMLBody, "align=center", "align=left", , , vbTextCompare)
TStream.Close
Kill tmpFile
With MailItem
.HTMLBody = strHTMLBody
.To = Application.Range("A30") & "; " & Application.Range("A31")
.Subject = Application.Range("B1")
.Display
'Application.SendKeys "%s", True
End With
Set MailItem = Nothing
Set appOutlook = Nothing
Set FSObject = Nothing
Set TStream = Nothing
End Sub
Yesterday when "live" the Application.Sendkeys line wasn't commented out, but thats the only difference.
The spreadsheet is running on Windows 2000 using Office 2000 and pretty much left alone apart from a couple of scedhuled reports, none of which run on the hour.
Can anyone see why it would suddenly go hyperactive on me, or give me something else to look at or think about?