dsvleeuw
06-16-2018, 02:19 AM
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.
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.