Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I am not sure about the time slots that you are using but I guess the problem is that the delay is not good enough and hence you are getting the email twice.</p> <p>Here is a code that I tried and tested. I am using a slightly different method than yours wherein I am latebinding with Outlook and sending the excel file as an attachment. The benefit of this method is that you don't have to <strong>open the workbook</strong>.</p> <p><strong>Test Conditions</strong></p> <pre><code>'Based on your comment, Testing for 4 different workbooks 'for 4 diff time intervals 'Time interval 1 : 11:30 PM - 11:35PM C:\Temp\Book1.xlsx 'Time interval 2 : 11:35 PM - 11:40PM C:\Temp\Book2.xlsx 'Time interval 3 : 11:40 PM - 11:45PM C:\Temp\Book3.xlsx 'Time interval 4 : 11:45 PM - 11:50PM C:\Temp\Book4.xlsx </code></pre> <p><strong>Logic</strong></p> <p>The logic is to set the <code>Wait</code> value in such a way that it doesn't re-enter the same loop again. Also it helps if you specify the start time and the end time in the <code>IF</code> condition unlike in your code where you specify just the start time.</p> <p>I have commented the code so that you will not have a problem understanding the code. Still if you do, simply post back.</p> <p><strong>Code</strong></p> <pre><code>Private Sub Workbook_Open() Dim B1 As String, B2 As String, B3 As String, B4 As String Dim sEmail As String Dim SendEml As Boolean Dim OutApp As Object, OutMail As Object 'Testing for 4 different workbooks for 4 diff time intervals 'Time interval 1 : 11:30 PM - 11:35PM C:\Temp\Book1.xlsx 'Time interval 2 : 11:35 PM - 11:40PM C:\Temp\Book2.xlsx 'Time interval 3 : 11:40 PM - 11:45PM C:\Temp\Book3.xlsx 'Time interval 4 : 11:45 PM - 11:50PM C:\Temp\Book4.xlsx B1 = "C:\Temp\Book1.xlsx" B2 = "C:\Temp\Book2.xlsx" B3 = "C:\Temp\Book3.xlsx" B4 = "C:\Temp\Book4.xlsx" '~~&gt; Email Address sEmail = "someone@someone.com" Do Select Case Hour(Now()) '~~&gt; I have only one case here as I am checking for 11PM '~~&gt; If your time slots fall under differnt hours then '~~&gt; Create more cases accordingly Case 23 If Minute(Now()) &gt;= 20 And Minute(Now()) &lt; 25 Then FileToAttach = B1: SendEml = True ElseIf Minute(Now()) &gt;= 25 And Minute(Now()) &lt; 30 Then FileToAttach = B2: SendEml = True ElseIf Minute(Now()) &gt;= 30 And Minute(Now()) &lt; 35 Then FileToAttach = B3: SendEml = True ElseIf Minute(Now()) &gt;= 35 And Minute(Now()) &lt; 40 Then FileToAttach = B4: SendEml = True End If End Select '~~&gt; Latebind with Outlook to send the email If SendEml = True Then Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = sEmail .Subject = "SO Example" .Body = "Hi Scott :)" .Attachments.Add FileToAttach '&lt;~~ This is where we attach the file .Send End With SendEml = False End If '~~&gt; I have set the wait time for 200 seconds which is about 3.3 mins '~~&gt; Change as applicable. You have to ensure that you set this carefully '~~&gt; So that the Do Loop doesn't run in the same time frame else you will '~~&gt; get duplicate emails. Wait 200 Loop End Sub Private Sub Wait(ByVal nSec As Long) nSec = nSec + Timer While nSec &gt; Timer DoEvents Wend End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POExcel VBA Validate Automated Email Status
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USSiddharth Rout
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POExcel VBA Validate Automated Email Status
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload