Databases
StackOverflow2013
Postsdbo
POExcel VBA Validate Automated Email Status
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA Validate Automated Email Status
    primarykey
    data
    text
    <p>I have been looking around the net to no avail thus far. I have an Excel spreadsheet with associated VBA code which, at a certain time everyday, e-mails the contents of the workbook to other people in the company I work for. </p> <p>This code is in an infinite loop, and is rarely closed. We have it setup on one computer to do this all day to calculate and update various things on our intranet. There are a few workbooks the code accesses at different times everyday using a time delay function.</p> <p>The problem is that sometimes the code runs a little fast and ends up sending two emails of the same workbook instead of one. Refer to the code below:</p> <pre><code>Private Declare Sub Sleep Lib "kernal32" (ByVal dwMilliseconds As Long) Private Sub Workbook_Open() Do While 1 Start = Timer If Hour(Now())=13 &amp; Minute(Now())&gt;=45 Then s = "path to file" Application.DisplayAlerts = False Workbooks.Open Filename:=s ActiveWorkbook.SendMail Recipients:="someone@someone.com" ActiveWindow.Close End If delay = Int(600 - (Timer - Start)) If delay&gt;0 Then delay = delay * 1000 Sleep delay End If Loop End Sub </code></pre> <p>As I said, there are more workbooks that are activated and calculations are done on them but there's no need to put that code up as well. It all follows pretty much the same format as above.</p> <p>I think if I played around with the sleep settings in the loop I could get it to only email once but that would take too much time.</p> <p>I was considering using a simple if statement to check to see if an email was sent today (using Date?) and if so, simply close the active window else send an email. This would ensure that only one email would be sent per day per workbook. The only trouble I am having is how to code that exactly...</p> <p>There's not much online that I could find in regards to documentation about the SendMail addin that we are using. Everything I try comes up with an error and I have no idea how to work around this. An example of what I have tried was:</p> <pre><code>If SendMail = False Then "send the email" Else "close" </code></pre> <p>Obviously that wouldn't work but it was worth the try.</p> <p>So if anyone can give me a hand with this, it would be greatly appreciated!!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USScott Fiander
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel VBA Validate Automated Email Status
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhich email client do u use? Outlook?
      singulars
      1. POExcel VBA Validate Automated Email Status
      1. USSiddharth Rout
    2. COYes, using Outlook
      singulars
      1. POExcel VBA Validate Automated Email Status
      1. USScott Fiander
    3. COOne last question... You need to send the email at 1:45PM or is there some other criteria?
      singulars
      1. POExcel VBA Validate Automated Email Status
      1. USSiddharth Rout
 

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