I've written a script which generates a Lotus Notes email to publish data at certain time intervals. This script is Macro2 - Macro5. Macro 2 - Macro 5 are all identical, but I've duplicated the script under different subs to try and diagnose my problem. The problem that I am experiencing is sometimes at for instance 8pm, 4 emails will be generated. One email will be triggered correctly by Macro 5, but the other emails sent at 8pm have been triggered by Macro 2. I know this because I've added an extra line to each Macro to indicate in the email which Macro generated it.
I am using the following to call these subs:
In "This Workbook" I have:
Private Sub Workbook_Open()
Call DailyAM
Call DailyPM
End Sub
In Module 1:
Sub DailyAM()
Application.OnTime TimeValue("06:00:00"), "Macro2"
Application.OnTime TimeValue("10:00:00"), "DailyAM"
End Sub
Sub DailyPM()
Application.OnTime TimeValue("12:01:00"), "Macro3"
Application.OnTime TimeValue("16:00:00"), "Macro4"
Application.OnTime TimeValue("20:00:00"), "Macro5"
Application.OnTime TimeValue("23:59:00"), "DailyPM"
End Sub
Really confused as to why this is happening. Pretty certain the issue has nothing to do with Macro 2-5, but here it is just in case:
Sub Macro5()
Windows("Silo report test v2.xlsm").Activate
Application.Calculate
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
'Email address array changed for privacy
vaRecipient = VBA.Array("[email protected]")
MailDoc.SendTo = vaRecipient
MailDoc.Subject = Range("B1").Value
Set workspace = CreateObject("Notes.NotesUIWorkspace")
Dim notesUIDoc As Object
Set notesUIDoc = workspace.EditDocument(True, MailDoc)
Call notesUIDoc.GOTOFIELD("Body")
Call notesUIDoc.FieldClear("Body")
Call notesUIDoc.FieldAppendText("Body", Range("B9").Value & vbCrLf & vbCrLf & Range("b10").Value & Range("I10").Value & Range("D10").Value & vbCrLf & Range("b11").Value & Range("I11").Value & Range("D11").Value & vbCrLf & Range("b12").Value & Range("I12").Value & Range("D12").Value & vbCrLf & vbCrLf & Range("b13").Value & Range("I13").Value & Range("D13").Value & vbCrLf & vbCrLf & Range("b14").Value & Range("C14").Value & Range("D14").Value & vbCrLf & vbCrLf & Range("b15").Value & Range("I15").Value & Range("D15").Value & vbCrLf & Range("F4").Value & vbCrLf)
notesUIDoc.Send
notesUIDoc.Close
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub
See Question&Answers more detail:os