0

I want an excel file to save (stamp time) every thirty minutes and email itself to me. I have gotten the file to save and send it to me. However, when I try to do this continuously for 30 min, I run into issues. below is my code, anyone have any ideas? I tried doing a loop function, so while A1 = 0 it will keep running, but when I click stop button, it will change A1 = 1, and basically stop. I also want to be able to run this program and still be able to use excel and not be disturbed, any ideas?

Option Explicit

Private Sub CommandButton1_Click()
Range("A1").Value = 0
Do While Range("A1").Value = 0
Loop

ActiveWorkbook.SaveAs "VBA Email " & Format(Date, "mm-dd-yyyy") & " " & Format(Time, "h.mm AM/PM") & ".xlsm"
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = "xxxx@gmail.com"
.Subject = "VBA Email"
.Attachments.Add (ActiveWorkbook.FullName)
.Display
Application.SendKeys "%s"
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Application.Wait (Now + TimeValue("0:00:02"))


End Sub


Private Sub CommandButton2_Click()
Range("A1").Value = 1
End Sub
Dan Is Fiddling By Firelight
  • 5,981
  • 17
  • 77
  • 128

2 Answers2

0

To use the loop, enter your statements between the Do While line and the Loop line:

Do While... 
  ' statements here to be executed while
  ' the loop is running
Loop
Reg Edit
  • 6,719
  • 1
  • 35
  • 46
0

Instead of the Application.Wait function use Application.OnTime e.g.

Public Sub EventMacro()    
    alertTime = Now + TimeValue("00:30:00")
    Application.OnTime alertTime, "EventMacro"
    'Save your file and email it
End Sub
AnalystCave.com
  • 4,884
  • 2
  • 22
  • 30