Hello Forum,
I'm having the following problem with a macro I've written to run at a certain time of day (Note: Due to work restrictions I cannot use windows scheduler to run the macro at the time required), the code runs several times and thus generates multiple emails. I'm wondering if there is a simple way to solve this so that it only runs once. Code is below:
Dim TimeToRun
Sub auto_open()
Call ScheduleCopyPriceOver
End Sub
Sub ScheduleCopyPriceOver()
TimeToRun = Now + TimeValue("00:00:01")
Application.OnTime TimeToRun, "ScheduleCopyPriceOver"
If Time = TimeSerial(7, 30, 0) Then
Application.OnTime TimeToRun, "CopyPriceOver"
End If
End Sub
Sub CopyPriceOver()
Dim txtfilename As String
Dim currentWb As Workbook
Dim openWb As Workbook
Dim newWb As Workbook
Dim openWs As Worksheet
Dim currentWs As Worksheet
Dim OutApp As Object
Dim OutMail As Object
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
txtfilename = "file location"
Set currentWb = ThisWorkbook
Set openWb = Workbooks.Open(txtfilename)
openWb.Sheets("Tabelle1").Activate
ActiveSheet.Columns("A:F").Select
Selection.Copy
currentWb.Sheets("Tabelle1").Activate
ActiveSheet.Cells(1, 1).Activate
ActiveSheet.Paste
Calculate
currentWb.Save
openWb.Close
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "boss"
.CC = "me"
.BCC = ""
.Subject = "Update"
.attachments.Add currentWb.FullName
.send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Call ScheduleCopyPriceOver
End Sub
Sub auto_close()
On Error Resume Next
Application.OnTime TimeToRun, "ScheduleCopyPriceOver", , False
ActiveWorkbook.Close
End Sub
Thanks in advance!
Bookmarks