Hello Forum!
I'm having a problem with the following macro I have written (see below). I've written it to where it runs at 7:35 AM (I should note I cannot use Windows Scheduler due to lack of administrative rights on my work PC) and was having problems with it running multiple times and sending multiple emails, so I added the Application.Wait command to ensure that it wouldn't do that. The problem is it still runs at 7:35 and now at 7:38! Any help would be appreciated as I am a basic VBA coder and have no idea how to fix this issue.
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, 35, 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 = "\\path\" & "report" & "*"
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 = "bossman"
' .CC = ""
.BCC = ""
.Subject = "Update " & Now
.attachments.Add currentWb.FullName
.send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.Wait Now + TimeValue("00:03:00")
Call ScheduleCopyPriceOver
End Sub
Sub auto_close()
On Error Resume Next
Application.OnTime TimeToRun, "ScheduleCopyPriceOver", , False
ActiveWorkbook.Close
End Sub
Thanks in advance!
Bookmarks