Posts by Logit

    You can clear the error first, then call the function. However, this is an unusual thing to do. I'm following to see how you make out.


    With Err Object there are two methods that you can also use.

    • Err.Clear: This method clears the error number and error description from VBA’s memory (It’s different from “On Error GoTo -1” as it doesn’t completely reset the error).

    You could use the GoTo command .... or .... you could simply recalll the Function.

    Something like :

    There is nothing in your existing macro to cause the issues you are experiencing.

    Two things I would try ....

    #1: Add another command at the end of your macro, telling Excel to close the first workbook. Redundant I know but ???

    #2: Don't utilize the "Workbook_BeforeClose(Cancel As Boolean)". Create a new macro called "CloseMyWB" or something and

    place all your macro commands in there ... including closing the first workbook.

    The following accomplishes the goal you seek. However, it is not presently edited to match your precise layout.

    By changing only a few specifics in the code you will have your project complete. Or ... use the attached as is.


    Here are two macros ... one to hide and one to unhide. Change the column indicated in the macro code ...

    Compare the code line by line. I commented out a few lines and corrected the TIME to "00:00:02" where noted.

    I ran the edited code without error and it consistently produce a copy of my desktop 10 times in a row ... whereas

    your original code would error out on the 3rd or 4th attempt.

    'The following seems to work consistently here :

    I suspect the primary edit involved the wait time.

    Option Explicit
    Private Sub Workbook_Open()
    Dim DateDueCol As Range
    Dim DateDue As Range
    Dim NotificationMsg As String
    Set DateDueCol = Sheet1.Range("F7:F100") 'the range of cells that contain your due dates
    For Each DateDue In DateDueCol

    I tried to highlight the change in your code but it appears that did not work as desired.

    The sixth line down (begins with Set DateDueCol) .... insert the appropriate sheet name before the work Range.

    Have you looked in the OUTLOOK / SEND folder to see if the email was actually sent ? Maybe some of the emails are still sittin in OUTLOOK ?

    If you believe the issue lies with the number of emails attempting to be sent ... you might try setting a pause between each email sent to see if that would

    make a difference (however I doubt that would change anything).

    Here is another approach :