scheduled macros execute a few times VBA?

[dsvleeuw]

I have been working on scheduled 4 times a day macro to automatic refresh the date, automatic refresh the pivots, automatic autofit the columns and then sends a mail. Only problem is that the mail sends itself 3 or 4 times when executing Jaanee()


Option Explicit


' this is used to run all_macro & All_macro1 at a specific time

Sub jaanee()
Application.OnTime TimeValue("07:00:00"), "all_macro"
Application.OnTime TimeValue("12:00:00"), "all_macro1"
Application.OnTime TimeValue("17:00:00"), "all_macro1"
Application.OnTime TimeValue("22:00:00"), "all_macro"
End Sub


'these are the combination of macros I want to execute one after another

Sub all_macro()
Call datum
Call refresh
Call refresh
Call refresh
Call juiste_layout
Call SendTeun
End Sub

'these are the combination of macros I want to execute one after another

Sub all_macro1()
Call datum
Call refresh
Call refresh
Call refresh
Call juiste_layout
Call Send
End Sub
This is what I used to execute the macros at a specific time:

' refresh all the pivot table

Sub refresh()
Dim pt As PivotTable
For Each pt In Workbooks(_
"shipments 5.1.xlsm").Sheets(_
"rapportages").PivotTable
pt.PivotCache.refresh
Next
End Sub

'autofit all the columns

Sub juiste_layout()
Workbooks("shipments 5.1.xlsm").Sheets(_
"rapportages").Cells.EntireColumn.AutoFit
End Sub

'refresh the date so my data input is changed

Sub datum()
Workbooks("shipments 5.1.xlsm")_
.Sheets("tijden").Range("a1").Value = Date
Workbooks("shipments 5.1.xlsm")_
.Sheets("tijden").Range("b1").Value = Time
End Sub

'sends a mail to my manager/Team at 7:00 & 22:00

Sub SendTeun()
Dim r As Range
Workbooks("shipments 5.1.xlsm")_
.Sheets("rapportages").Activate
Set r = Workbooks("shipments 5.1.xlsm"). _
Sheets("rapportages").Range("rm")
With r
Workbooks("shipments 5.1.xlsm").EnvelopeVisible = True
With .Parent.MailEnvelope.Item
.To = ""
.CC = ""
.BCC = ""
.Subject = "Automatic Message: E-comm numbers Today"
.Send
End With
End With
End Sub

'sends a mail to my Team at 7:00 & 12:00 & 17:00 & 22:00

Sub Send()
Dim r As Range
Workbooks("shipments 5.1.xlsm").Activate
Set r = Workbooks("shipments 5.1.xlsm").
Sheets("rapportages").Range("rm")
With r
Workbooks("shipments 5.1.xlsm").EnvelopeVisible = True
With .Parent.MailEnvelope.Item
.To = ""
.CC = ""
.BCC = ""
.Subject = "Automatic Message: E-comm numbers Today"
.Send
End With
End With
End Sub


These are the macros I combined with All_macro() & All_macro2. everything works fine, only when executing the Send() & SendTeun() macro sometimes it send 3/4 mails.

[Bob Phillips]

Do you mean it sends itself 4 times for each of the four runs, or once each for the four?

[dsvleeuw]

Do you mean it sends itself 4 times for each of the four runs, or once each for the four?

Hi, no every run it sends itself 4 times..