Excel 2007 ActiveWorkbook.EnvelopeVisible Macro Sending Duplication Emails

[alexn1973]

Hello All,

First posting here, so apologies if I have missed something.

I have an issue with using Excel 2007 to send a defined range of a spreadsheet out as an email via a macro. I use Excel 2007 and Outlook 2007. I can send an email via a macro, but sometimes I get the mail multiple times

In the spreadsheet in question in the VBA editor "This workbook" I have the following code.....

Private Sub Workbook_Open()


Application.OnTime TimeValue("08:07:00"), "Send_Range"
Application.OnTime TimeValue("09:27:00"), "Send_Range"
Application.OnTime TimeValue("10:27:00"), "Send_Range"
Application.OnTime TimeValue("11:27:00"), "Send_Range"



End Sub

In the Module I have the below code

Sub Send_Range()

' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:A7").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope

' Set the optional introduction field that adds some header text to the email body.
.Introduction = ""

'Sets the To Field
.Item.To = "email.address@etc.com"

'Sets the Subject Field
.Item.Subject = ""

'Sends it
.Item.Send

End With

End Sub

This works, when the worksheet is open, I get an email sent to me at the times desired for the data in the cells selected, which is the good news, the bad news is that sometimes I can get up to 5 emails at each time slot, and other times just the one.

Would anyone be able to diagnose why I may occasionally get multiple duplicate emails?

I thought perhaps it may running the macro so fast that between 08:07:00 and 08:07:00:99 for example it has time to run the macro 3,4 or five times in under a second, sometimes?

Is it possible to do the code above but specify you only want the one mail at 08:07, and just one at 09:27 etc. I have tried to delay the macro by entering a line of "Sleep 2000" code in the module by including a line above the the start of the send range macro "Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)" but despite slowing the macro down it doesnt resolve it.

I would like to use the macro to send out a management intra-day update, but I don't want
everyone to get multiple duplication's of some of the mails, and I cannot figure out why it occurs
occasionally, if anyone could assist it would be really fantastic!

regards

 

[BJungheim]

Do you open this file multiple times between the times in your workbook_open event? Each time you open the file it sets the timer to run. In order to avoid this add a cancellation to your before_close event to cancel the schedules:

Private Sub Workbook_Open()

    Application.OnTime TimeValue("08:07:00"), "Send_Range"
    Application.OnTime TimeValue("09:27:00"), "Send_Range"
    Application.OnTime TimeValue("10:27:00"), "Send_Range"
    Application.OnTime TimeValue("11:27:00"), "Send_Range"

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.OnTime TimeValue("08:07:00"), "Send_Range", , False
    Application.OnTime TimeValue("09:27:00"), "Send_Range", , False
    Application.OnTime TimeValue("10:27:00"), "Send_Range", , False
    Application.OnTime TimeValue("11:27:00"), "Send_Range", , False
    
End Sub

 

[alexn1973]

Do you open this file multiple times between the times in your workbook_open event? Each time you open the file it sets the timer to run. In order to avoid this add a cancellation to your before_close event to cancel the schedules:

Private Sub Workbook_Open()

    Application.OnTime TimeValue("08:07:00"), "Send_Range"
    Application.OnTime TimeValue("09:27:00"), "Send_Range"
    Application.OnTime TimeValue("10:27:00"), "Send_Range"
    Application.OnTime TimeValue("11:27:00"), "Send_Range"

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.OnTime TimeValue("08:07:00"), "Send_Range", , False
    Application.OnTime TimeValue("09:27:00"), "Send_Range", , False
    Application.OnTime TimeValue("10:27:00"), "Send_Range", , False
    Application.OnTime TimeValue("11:27:00"), "Send_Range", , False
    
End Sub

Many thanks for that answer, and yes that is probably it, the duplication emails did seem rather random, but thats because the closing and re-opening of the sheet was equally random! As I was closing and opening it quite a bit as I was testing it all. If i leave the sheet open over send times it works fine, and does not duplicate.

And I will test with your new code if it keeps the count OK with intraday sheet closings.

thanks again for your reply, it is really appreciated!

 

[alexn1973]

Many thanks for that answer, and yes that is probably it, the duplication emails did seem rather random, but thats because the closing and re-opening of the sheet was equally random! As I was closing and opening it quite a bit as I was testing it all. If i leave the sheet open over send times it works fine, and does not duplicate emails.

And I will test with your new code if it keeps the count OK with intraday sheet closings.

thanks again for your reply, it is really appreciated!