Autosend emails?

[FrenchCAD]

I repost my question from Engineering spreadsheets forum.

I'm working on a VB macro to send emails at opening and at closing of an Excel workbook. So far, it works fine at opening but not at closing. I have no idea what's going wrong.

Here is the code so far :


Public WithEvents App As Application


Private Sub Workbook_Open()
Dim i As Integer
Dim Alerte, Info, Erreur
Alerte = MsgBox("Veuillez ne pas supprimer ou renommer la feuille 'Liste emails'. Cette feuille doit aussi rester la dernière du classeur.", vbOKOnly, "Attention!&quot
Info = MsgBox("Ce fichier va tenter d'envoyer un e-mail pour notifier qu'il est utilisé. Votre gestionnaire d'emails doit être ouvert.", vbOKOnly, "Info&quot
For i = 1 To (ActiveWorkbook.Sheets.Count - 1)
ActiveWorkbook.Worksheets(i).Name = i
ActiveWorkbook.Worksheets(i).Cells(2, 2) = i
Next i
For i = 1 To (ActiveWorkbook.Sheets.Count - 1)
ActiveWorkbook.Worksheets(i).Name = "Page " & i
ActiveWorkbook.Worksheets(i).Cells(2, 2) = i
Next i
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Delivery = xlAllAtOnce
.Recipients = Array(Worksheets("Liste emails&quot.Range(Cells(1, 1), Cells(1, 200)))
.Subject = "Fichier en cours de modification"
.Message = "Le fichier " & ActiveWorkbook.FullName & " est en cours de modification"
End With
ActiveWorkbook.Route
If ActiveWorkbook.HasRoutingSlip And Not ActiveWorkbook.Routed Then
Erreur = MsgBox("Mail non envoyé", vbOKOnly, "Erreur&quot
End If
End Sub


Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Dim a, Info
a = MsgBox("Do you really want to close the workbook?", vbYesNo)
If a = vbNo Then Cancel = True
Info = MsgBox("Ce fichier va tenter d'envoyer un e-mail pour notifier qu'il n'est plus utilisé", vbOKOnly, "Info&quot
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Delivery = xlAllAtOnce
.Recipients = Array(Worksheets("Liste emails&quot.Range(Cells(1, 1), Cells(1, 200)))
.Subject = "Fichier modifié"
.Message = "Le fichier " & ActiveWorkbook.FullName & " a été modifié et est maintenant disponible."
End With
ActiveWorkbook.Route
If ActiveWorkbook.HasRoutingSlip And Not ActiveWorkbook.Routed Then
Message = MsgBox("Mail non envoyé", vbOKOnly, "Erreur&quot
End If
ActiveWorkbook.Save
End Sub


Thanks for help

Cyril Guichard
Mechanical Engineer

 

[FrenchCAD]

Ok, I found what was wrong when closing :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim a, Info
a = MsgBox("Do you really want to close the workbook?", vbYesNo)
If a = vbNo Then Cancel = True
Info = MsgBox("Ce fichier va tenter d'envoyer un e-mail pour notifier qu'il n'est plus utilisé", vbOKOnly, "Info&quot
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Delivery = xlAllAtOnce
.Recipients = Array(Worksheets("Liste emails&quot.Range(Cells(1, 1), Cells(1, 200)))
.Subject = "Fichier modifié"
.Message = "Le fichier " & ActiveWorkbook.FullName & " a été modifié et est maintenant disponible."
End With
ActiveWorkbook.Route
If ActiveWorkbook.HasRoutingSlip And Not ActiveWorkbook.Routed Then
Message = MsgBox("Mail non envoyé", vbOKOnly, "Erreur&quot
End If
ActiveWorkbook.Save
End Sub

Now, I still have a weird problem when I add or copy a sheet in my workbook. The macros to send mail don't seem to find the "Liste emails" sheet anymore...

Cyril Guichard
Mechanical Engineer