Faire tourner une macro en automatique sans ouvrir le fichier excel

Winniechips · 24 Février 2015

Bonjour,

J'ai créé un fichier de suivi des dates d'habilitation avec une mise en forme conditionnelle suivant le délai restant avant fin de validité. Mes cellules passent du vert, au jaune, au orange puis au rouge.
Cette mise en forme fonctionne.

Ce que je souhaitais arriver à faire, c'est que le fichier excel s'ouvre en off lors du démarrage de ma session (sans ouverture du fichier de ma part) et qu'une macro tourne et m'envoie un mail pour me prévenir qu'une échéance arrive.

J'ai donc créé un fichier en VBS que j'ai mis dans le dossier démarrage de ma session. Je pense que le fichier vbs fonctionne car je vois le raccourci temporaire de mon fichier excel sur le bureau :

Fichier VBS :

Dim xlapp, classeur, feuille
Set xlapp = CreateObject("Excel.Application")
Set classeur = xlapp.Workbooks.Open("C:\Documents and Settings\Bureau\Test Tableau de Suivi des Habilitations avec macro.xlsm")
Set feuille = xlapp.ActiveSheet

WScript.Sleep(180*1000)
classeur.close
set feuille = nothing
set classeur = nothing

J'ai créé une macro avec la méthode Auto Open que j'ai placé dans le Workbook :

Sub Aut

pen()
Envoi_Mail_Alerte_Habilitation
End Sub

Sub Envoi_Mail_Alerte_Habilitation()

Dim i As Integer
Dim j As Integer

Dim l_bool_condition1 As Boolean
Dim l_bool_condition2 As Boolean
Dim l_bool_condition3 As Boolean

l_bool_condition1 = False
l_bool_condition2 = False
l_bool_condition3 = False

For i = 3 To 12
For j = 5 To 35

If Worksheets("Suivi").Cells(i, j).Interior.Color = RGB(255, 255, 0) And l_bool_condition1 = False Then
Envoi_Mail_entre_3_et_6_Mois
l_bool_condition1 = True

ElseIf Worksheets("Suivi").Cells(i, j).Interior.Color = RGB(255, 192, 0) And l_bool_condition2 = False Then
Envoi_Mail_inf_3_Mois
l_bool_condition2 = True

ElseIf Worksheets("Suivi").Cells(i, j).Interior.Color = RGB(255, 0, 0) And l_bool_condition3 = False Then
Envoi_Mail_Dépassement
l_bool_condition3 = True

End If

l_bool_condition1 = False
l_bool_condition2 = False
l_bool_condition3 = False

Next j
Next i

End Sub

Ensuite, j'ai créé 3 modules :
Le 1er Module :

Sub Envoi_Mail_inf_3_Mois()

Dim ObjOutlook As New Outlook.Application
Dim oBjMail

Set ObjOutlook = New Outlook.Application
Set oBjMail = ObjOutlook.CreateItem(olMailItem)

With oBjMail
.To = "mon adresse email"
.Subject = "Echéance Habilitations du Personnel"
.Body = "Attention, la date de validité d'une formation ou habilitation d'un salarié est inférieure à 3 Mois."
.Send
End With
ObjOutlook.Quit
Set oBjMail = Nothing
Set ObjOutlook = Nothing

End Sub

Le 2nd Module :

Sub Envoi_Mail_entre_3_et_6_Mois()

Dim ObjOutlook As New Outlook.Application
Dim oBjMail

Set ObjOutlook = New Outlook.Application
Set oBjMail = ObjOutlook.CreateItem(olMailItem)

With oBjMail
.To = "mon adresse email"
.Subject = "Echéance Habilitations du Personnel"
.Body = "Attention, la date de validité d'une formation ou habilitation d'un salarié est comprise entre 3 et 6 Mois."
.Send
End With
ObjOutlook.Quit
Set oBjMail = Nothing
Set ObjOutlook = Nothing

End Sub

Le 3ème Module :

Sub Envoi_Mail_Dépassement()

Dim ObjOutlook As New Outlook.Application
Dim oBjMail

Set ObjOutlook = New Outlook.Application
Set oBjMail = ObjOutlook.CreateItem(olMailItem)

With oBjMail
.To = "mon adresse email"
.Subject = "Echéance Habilitations du Personnel"
.Body = "Attention, la date de validité d'une formation ou habilitation d'un salarié est dépassée."
.Send
End With
ObjOutlook.Quit
Set oBjMail = Nothing
Set ObjOutlook = Nothing

End Sub

Voilà la totalité de mon code sur lequel je travaille depuis plusieurs mois (j'ai des connaissances limitées en programmation mais je me suis débrouillée avec les tutos). Si quelqu'un pourrait m'expliquer pourquoi je ne reçois aucun mail...

Merci du temps que vous prendrez à étudier mon problème.

mromain · 24 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Bonjour Winniechips et bienvenue sur le forum.
Bonjour le forum

A la lecture de ton (long) post, plusieurs remarques viennent :

Déjà, tu devrais découper ton problème : tu postes tout un tas de code sans exemple et ...

Si quelqu'un pourrait m'expliquer pourquoi je ne reçois aucun mail...

Pas évident

Sinon, il y aurait plusieurs pistes à creuser.
Par exemple, contrôler avec des MsgBox ou des Debug.Print comment s'exécute le code (à la macro Autpen, Envoi_Mail_Alerte_Habilitation, lorsque le code envoie un mail, ...).

D'une manière plus générale, tu peux exécuter directement une macro de ton classeur depuis le script VBS (et te passer de Autpen + WScript.Sleep(180*1000)) en :

Déclarant une procédure publique dans le module ThisWorkbook du classeur :
VB:
```
Public Sub MaProcedure()
    MsgBox "MaProcedure()"
    
    '...
End Sub
```

Appelant cette procédure depuis ton fichier vbs :

VB:

Dim xlApp, xlWbk
    Set xlApp = CreateObject("Excel.Application")
    xlApp.EnableEvents = False     'désactiver les évènements (type Auto_Open)
     Set xlWbk = xlApp.Workbooks.Open("D:\Tmp\Classeur1.xlsm")
    xlWbk.MaProcedure
    xlWbk.Close
    Set xlWbk = Nothing
    xlApp.EnableEvents = True

Ensuite, dans ta macro Envoi_Mail_Alerte_Habilitation, tu utilises trois l_bool_conditionX qui semblent en l'état être inutiles.
En effet, le fait de les réinitialiser à False au sein de ta boucle fait qu'ils vaudront toujours False dans tes tests If.

Et finalement, le code serait optimisable en évitant d'ouvrir et fermer Outlook à chaque fois que tu envoies un mail. Par exemple, ouvrir Outlook au début de la macro Envoi_Mail_Alerte_Habilitation et le refermer à la fin.
Tu pourrais aussi te passer des 3 macros Sub Envoi_Mail_xxx() en passant le corps du message en paramètre à une seule fonction Function Envoi_Mail(texteMail As String).

J'espère ne pas te décourager, d'autant plus que pour t'être uniquement débrouillée avec les tutos, le code que t'as fait est vraiment pas mal.

A+

PS: Lorsque tu postes du code, essaye d'utiliser la balise highlight pour plus de lisibilité (ex : [noparse]

VB:

ton code

[/noparse]).

Winniechips · 24 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Merci pour votre réponse.

Je vais retravailler dessus, prendre en compte vos remarques et voir grâce aux Msgbox d'où vient le problème.

A bientôt,
Winniechips

Winniechips · 24 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

J'ai réalisé quelques modifications ; j'ai un message "Erreur de compilation Next sans For" et Next j s'affiche en surbrillance, sur le code suivant que j'ai modifié.
Sauriez-vous pourquoi VBA ne reconnait pas ma boucle For ?

VB:

Public Sub Envoi_Mail_Alerte_Habilitation()
Dim i As Integer
Dim j As Integer
Dim ObjOutlook As New Outlook.Application
Set ObjOutlook = New Outlook.Application

For i = 3 To 12
        For j = 4 To 35
            Select Case Worksheets("Suivi").Cells(i, j).Interior.Color
            Case Is = RGB(255, 255, 0)
                Envoi_Mail_entre_3_et_6_Mois (TexteMail)
                
            Case Is = RGB(255, 192, 0)
                Envoi_Mail_inf_3_Mois (TexteMail)
           
            Case Is = RGB(255, 0, 0)
                Envoi_Mail_Dépassement (TexteMail)
        Next j
Next i

ObjOutlook.Quit
Set ObjOutlook = Nothing
            
End Sub

Merci par avance

camarchepas · 24 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Bonjour à tous , MRomain , Winniechips,

Il faudrait fermer ton select case par un

end select juste au dessus du next J

Winniechips · 24 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Merci pour votre réponse Camarchepas.
L'erreur a disparu.

Winniechips · 24 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

J'ai fais les modifs comme MRomain me l'a suggéré au sujet des modules. Je les ai supprimé et j'ai placé 3 fonctions dans le corps du Workbook à la suite du "Public Sub Envoi_Mail_Alerte_Habilitation()" selon le modèle suivant :

VB:

Function Envoi_Mail_entre_3_et_6_Mois(TexteMail As String)
                        Dim ObjMail
                        Set ObjMail = ObjOutlook.CreateItem(olMailItem)
                        
                            With ObjMail
                                .To = "mon adresse email"
                               .Subject = "Echéance Habilitations du Personnel"
                               .Body = "Attention, la date de validité d'une formation ou habilitation d'un salarié est comprise entre 3 et 6 Mois."
                               .Send
                            End With
                            
                            Set ObjMail = Nothing
                            
End Function

Je pense que les fonctions ne fonctionnent pas car je ne reçois aucun mail en testant la macro et en ayant des cellules colorées.
Peut être un problème d'argument des fonctions... Qu'en pensez-vous ?

Merci par avance.

mromain · 24 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Bonsoir Winniechips, Nono

, le forum

Voici ce dont je te parlais :

VB:

Public Sub Envoi_Mail_Alerte_Habilitation()
Dim i As Integer, j As Integer, ObjOutlook As Outlook.Application
    
    'créer une seule fois l'application Outlook
     Set ObjOutlook = CreateObject("Outlook.Application")
    
    For i = 3 To 12
        For j = 5 To 35
            Select Case Worksheets("Suivi").Cells(i, j).Interior.Color
                Case RGB(255, 255, 0)
                    EnvoiMail ObjOutlook, "Attention, la date de validité d'une formation ou habilitation d'un salarié est comprise entre 3 et 6 Mois."
                Case RGB(255, 192, 0)
                    EnvoiMail ObjOutlook, "Attention, la date de validité d'une formation ou habilitation d'un salarié est inférieure à 3 Mois."
                Case RGB(255, 0, 0)
                    EnvoiMail ObjOutlook, "Attention, la date de validité d'une formation ou habilitation d'un salarié est dépassée."
            End Select
        Next j
    Next i
    
    ObjOutlook.Quit
    Set ObjOutlook = Nothing
    
End Sub


Private Function EnvoiMail(objOl As Outlook.Application, contenu As String)
Dim oBjMail As Outlook.MailItem
    
    'si on n'a pas passé l'application Outlook à la fonction, quitter la fonction
     If objOl Is Nothing Then Exit Function
    
    'créer le mail
     Set oBjMail = objOl.CreateItem(olMailItem)
    With oBjMail
        .To = "mon adresse email"
        .Subject = "Echéance Habilitations du Personnel"
        .Body = contenu
        .Send
        
        'à supprimer : pour contrôler si on rentre bien dans la fonction
         MsgBox "Mail envoyé"
        
    End With
    Set oBjMail = Nothing
    
End Function

J'ai rajouté une MsgBox afin de contrôler que le code envoie bien un mail.
Peut-être que ça te fera avancer sur ton souci.

A+

Winniechips · 25 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Bonjour MRomain,

Merci de ton aide.

La boite de dialogue Msgbox ne s'affiche pas si elle est dans la fonction.
Par contre, Msgbox s'affiche si elle se trouve dans "Public Sub Envoi_Mail_Alerte_Habilitation()"

Du coup, si je comprends bien, c'est la fonction qui pose problème.

mromain · 25 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Bonjour Winniechips,

A priori, ça veut dire qu'on ne rentre pas dans la fonction. Du moins, que le code ne s'exécute pas jusqu'à la MsgBox. Tu peux essayer de la mettre au tout début de la fonction pour contrôler, mais à mon avis le problème vient de tes couleurs.

Es-tu certaine d'avoir les bonnes couleurs dans tes cellules ? (255, 255, 0), (255, 192, 0) et (255, 0, 0) ?

Sans fichier exemple, ça va devenir compliqué de t'aider...

A+

camarchepas · 25 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Bonjour MRomain, Winnie ,

Je te propose d'essayer via ce petit fichier la solution qui fonctionne trés bien de M Romain .

Winniechips · 25 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Bonjour MRomain,

Au début de la fonction, la boîte de dialogue Msgbox ne s'affiche pas.
Par contre, je viens de me rendre compte que lorsque je vais sur les cellules colorées (là où la macro devrait fonctionner), et que je vais dans "Format de cellule/remplissage", il n'y a aucune couleur, alors que ma cellule est colorée.

Je viens de faire un test, je sais d'où vient le problème.
Il s'agit de la mise en forme conditionnelle. A mano, lorsque je sélectionne une cellule que je la mets manuellement en orange (par exemple), je reçois le mail.

Auriez-vous une idée sur le fait de garder la mise en forme conditionnelle et que cela fonctionne? Peut être une option cachée dans excel ?

Winniechips · 25 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

J'ai peur de devoir faire une macro supplémentaire pour remplacer la mise en forme conditionnelle traditionnelle fournie par Excel.

mromain · 25 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Bonjour Nono,

Accéder au formatage dû aux mises en forme conditionnelles est très dur (impossible ?) en VBA...

J'ai peur de devoir faire une macro supplémentaire pour remplacer la mise en forme conditionnelle traditionnelle fournie par Excel.

Ou alors, refaire le test réalisé par tes MFC dans le code, à la place du contrôle de la couleur de remplissage de la cellule.

A+

Winniechips · 25 Février 2015

Re : Faire tourner une macro en automatique sans ouvrir le fichier excel

Re-Bonjour MRomain,

Oui, c'est effectivement ce que je pensais ; à savoir faire les tests dans le code.

Pour résumé :

si 90<= (date de validité - date d'aujourd'hui) <181 alors EnvoiMail ObjOutlook, "Attention, la ...validité ...comprise entre 3 et 6 Mois."
Si 0<(date de validité - date d'aujourd'hui)<91 alors EnvoiMail ObjOutlook, "Attention, la ...validité ...inférieure à 3 Mois."
Si (date de validité - date d'aujourd'hui) <= 0 alors EnvoiMail ObjOutlook, "Attention, la ...validité ...dépassée."

Effectuez une recherche sur Excel Downloads...

Faire tourner une macro en automatique sans ouvrir le fichier excel

Winniechips

XLDnaute Nouveau

mromain

XLDnaute Barbatruc

Winniechips

XLDnaute Nouveau

Winniechips

XLDnaute Nouveau

camarchepas

XLDnaute Barbatruc

Winniechips

XLDnaute Nouveau

Winniechips

XLDnaute Nouveau

mromain

XLDnaute Barbatruc

Winniechips

XLDnaute Nouveau

mromain

XLDnaute Barbatruc

camarchepas

XLDnaute Barbatruc

Pièces jointes

Winniechips

XLDnaute Nouveau

Winniechips

XLDnaute Nouveau

mromain

XLDnaute Barbatruc

Winniechips

XLDnaute Nouveau

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums