Lancer une macro via une formule

**Mav77** · 30/10/2018, 12h19

Bonjour à tous,

je suis nouveau et tout débutant en programmation excell.

Je me suis déjà sorti de pas mal de galères grâce à de nombreuses recherches sur ce site mais là je sèche tout près du but!!!
Je suis étudiant en 2ème mécatronique et en stage en entreprise,
celle-ci me demande de faire un programme sur excell qui fera ceci:
envoie de mail automatique (via gmail de l'atelier) quand la date de maintenance de tel machine est atteinte.

voici mon problème:

J'ai réussi à créer une macro qui envoie bien un mail automatiquement quand une cellule se modifie, mais uniquement manuellement.
Via une formule ça ne fonctionne pas.
La formule que j'utilise est la condition si (si la date d'aujourd'hui atteint date de maintenance alors affichage maintenance cellule et envoie automatique du mail)
Je sais que c'est mon subprivate worksheet_change le problème, que je dois utiliser plutôt le worksheet_calculate.
Mais je n'arrive pas à utiliser celui-ci.

Voici mon code et celui de la feuil1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Sub EnvoiMailCDO()
Dim mMessage As Object
Dim mConfig As Object
Dim mChps
 
 
    Set mConfig = CreateObject("CDO.Configuration")
 
    mConfig.Load -1
    Set mChps = mConfig.Fields
    With mChps
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        'Adapter suivant votre serveur de mail. (exemple pour Gmail.)=> Hotmail "smtp.live.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        'En principe, 25 fonctionne avec tout les serveurs.
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
 
        'Vous pouvez essayer sans ces trois lignes
        'Mais si votre serveur demande une authentification,
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "1"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "adressemail"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "motdepasse"
        'Si votre serveur demande une connexion sûre (SSL)
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = "true"
        .Update
    End With
 
    Set mMessage = CreateObject("CDO.Message")
    With mMessage
    Set .Configuration = mConfig
        .To = "adressemail"
        .From = "adressemail"
        .Subject = "test"
        .TextBody = "Ce mail vous est envoyer pour tester la macros"
        'Pour ajouter une pièce jointe, un fichier, classeur, image etc.
        '.AddAttachment 'Chemin et nom complet du fichier à joindre
        .Send
    End With
    Set mMessage = Nothing
 
    'Pour un autre message, pas besoin de tout reconfigurer, il faut toutefois recréer un nouveau
    'message à chaque fois.
    Set mMessage = CreateObject("CDO.Message")
    With mMessage
    Set .Configuration = mConfig
        .To = "adressemail"
        .From = "adressemail"
        .Subject = "C'est pour le deuxième test d'envoi mail"
        .TextBody = "Ce mail vous est envoyer pour tester la macros" & Chr(13) _
            & "et voir si le deuxième message est bien passer."
        'Pour ajouter une pièce jointe, un fichier, classeur, image etc.
        '.AddAttachment 'Chemin et nom complet du fichier à joindre
        .Send
    End With
    Set mMessage = Nothing
 
 
    'Libère les ressources
    Set mConfig = Nothing
    Set mChps = Nothing
End Sub

feuil1:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I20")) Is Nothing Then
    Select Case Target.Formula
 
        Case "Maintenance"
            Call EnvoiMailCDO
 
        End Select
End If
End Sub

Donc ici, cela fonctionne si je modifie la case I20 manuellement.

Merci d'avance pour l'aide!

**Igloobel** · 30/10/2018, 12h57

bonjour,

Pourquoi utilises-tu "Target.formula" plutôt que Target.value

même si c'est une formule tu récupères la valeur de la formule plutôt que la formule elle-même

tu dis

A+

**Mav77** · 30/10/2018, 13h16

Merci pour la réponse rapide,

j'ai donc changé Target.formula par Target.value

et worksheet_change par worksheet_calculate

mais ça ne fonctionne pas:

erreur de compilation:

la déclaration de la procédure ne correspond pas à la description de l'événement ou de la procédure de même nom.

**Pierre Fauconnier** · 30/10/2018, 14h34

Salut.

Avant même de parler de target.formula ou de target.value, il faut identifier le déclencheur du code.

Dan ton cas, le déclencheur n'est pas sur la cellule qui contient la formule, mais sur la cellule qui modifie la valeur renvoyée par la formule, autrement dit, sur un antécédent de cette cellule formulée.

Perso, je ne déclencherais pas le mail sur la modification de la cellule, mais par un bouton sur lequel on cliquerait pour lister les lignes concernées et agir en conséquence. Imagine la personne qui se trompe en saisissant une date et qui voit un mail partir... Pas certain que ce soit top au niveau ergonomie

**Mav77** · 30/10/2018, 14h53

Salut Pierre,

je pense que j'ai mal expliqué la situation.

Exemple en C1 =aujourd'hui() , en E1 une date d'échéance (date à laquelle correspond la maintenance d'une machine)

ma formule en G1 : =SI(E1=C1;"Maintenance";"en ordre")

Donc si la date d'échéance = la date d'aujourd'hui, alors la cellule marquera Maintenance et enverra un mail automatique.
Personne ne touchera à ce fichier excell. Il a justement pour but que tout se fasse automatiquement en fonction des dates et que personne n'intervienne.

L'envoie du mail automatique via un bouton ou modification manuelle d'une cellule, j'y arrive.
Le problème c'est que la macro (envoie du mail) doit se lancer à partir d'une formule. L'événement change ne convient donc pas car il ne marche qu'en cas de modification manuelle.

**Pierre Fauconnier** · 30/10/2018, 15h05

Un fichier Excel est "statique" par essence.

Si tu veux déclencher des actions de façon "automatique", tu dois déclencer une macro sur l'ouverture du fichier qui va scruter les valeurs et déclencher l'envoi du mail selon les critères fixés.

Si tu laisses le fichier Excel "tout le temps" ouvert, tu dois faire tourner une macro en boucle (une sorte de timer) qui, à espaces réguliers, va démarrer la macro qui va scruter les valeurs et déclencher l'envoi du mail. Ca bouffe des ressources et je pense que ce n'est pas pertinent. Excel n'est, au départ, pas fait pour cela. Si c'est le cas de figure, tu aurais intérêt à prévoir une tâche Windows qui ouvre le fichier Excel tous les jours, et dans l'événement d'ouverture, tu déclenches la macro qui envoie les mails, puis qui ferme le fichier.

Excel n'est pas "fait" pour cela, au départ et par nature. Peux-tu préciser le contexte?

**pijaku** · 30/10/2018, 15h11

Bonjour,

Une macro lancée par le Workbook_Open() et relancée à intervalles réguliers (toutes les heures ?) par Application.OnTime.

Après réflexion, si la formule teste une date en fonction de la formule =AUJOURDHUI() le lancement une fois par jour est suffisant.
Voir, comme le dit Pierre (que je salue), du côté du programmateur de tâches Windows.

A++

**Mav77** · 30/10/2018, 15h41

Envoyé par pijaku

Bonjour,

Une macro lancée par le Workbook_Open() et relancée à intervalles réguliers (toutes les heures ?) par Application.OnTime.

Après réflexion, si la formule teste une date en fonction de la formule =AUJOURDHUI() le lancement une fois par jour est suffisant.
Voir, comme le dit Pierre (que je salue), du côté du programmateur de tâches Windows.

A++

Salut pijaku,

tu as compris, une fois par jour est suffisant.
Par contre je ne comprend pas le lancement de la macro envoimail par workbook_Open(),
car ça doit pas envoyer un mail à l'ouverture d'excell mais uniquement quand les dates correspondent.

**Mav77** · 30/10/2018, 15h20

Le contexte:

L'entreprise me demande de créer un fichier excell de gestion des équipements,

dans celui-ci il y a aura une date d'échéance par machine pour sa maintenance,

et donc elle voudrait qu'un mail soit envoyé à la personne responsable de la machine quand sa date de maintenance est arrivé.

Donc comme tu l'as deviné, je fais démarrer l'ordinateur tout les jours à 7h automatiquement avec la tâche Windows qui démarre le bon fichier excell.
Et là si la date du jour correspond à une date d'échéance d'une telle machine, alors un mail sera envoyé automatiquement à la personne concerné.

Y aurait-il autre chose qu'excell pour régler ceci?

**Pierre Fauconnier** · 30/10/2018, 15h28

Excel n'est pas forcément fait pour cela, mais pourquoi pas?

L'idée est bonne et rejoint ce que j'ai proposé.

A l'ouverture du fichier (par scheduler ou manuellement), on lance une macro qui scanne la colonne des dates des prochaines maintenances et qui réagit en conséquence. En soi, c'est juste une boucle sur la colonne d'un tableau structuré, si la conception est bonne.

Si l'envoi du mail est maîtrisé, c'est l'affaire de 5 minutes et de quelques lignes de code.

**Mav77** · 30/10/2018, 15h37

Envoyé par Pierre Fauconnier

Excel n'est pas forcément fait pour cela, mais pourquoi pas?

L'idée est bonne et rejoint ce que j'ai proposé.

A l'ouverture du fichier (par scheduler ou manuellement), on lance une macro qui scanne la colonne des dates des prochaines maintenances et qui réagit en conséquence. En soi, c'est juste une boucle sur la colonne d'un tableau structuré, si la conception est bonne.

Si l'envoi du mail est maîtrisé, c'est l'affaire de 5 minutes et de quelques lignes de code.

L'envoi du mail est maîtrisé (c'est beaucoup dire!!!!!), je l'ai testé en appuyant sur un bouton ou en modifiant une case manuellement et je reçois bien le mail.

Ce qui l'est pas, c'est le reste!

Donc ici je dois faire une 2ème macro qui va scanner les dates, et ainsi appelé la première macro d'envoie de mail?
Je ne dois donc pas utiliser la fonction SI une date d'échéance correspond à la date d'aujourd'hui, alors lancement de la macro EnvoiMailCDO?

**Pierre Fauconnier** · 30/10/2018, 15h39

Si, tu peux utiliser une colonne formulée. Ca simplifie le code...

Avec un tableau structuré appelé t_Suivi disposant d'une colonne Echéance, tu pourrais utiliser le code suivant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub CheckDates()
  Dim Cell As Range
 
  For Each Cell In Range("t_Suivi[Echéance]")
  If Cell.Value < Date Then SendMail Cell.Row
End Sub
 
Sub SendMail(Row As Long)
...
...
End Sub

La macro CheckDates scrute la colonne du tableau structuré, et appelle la macro d'envoi du mail en fonction du test réalisé...

Au passage, c'est une très bonne idée d'avoir une macro qui envoie le mail, une macro qui teste les dates...

**Mav77** · 30/10/2018, 15h46

Envoyé par Pierre Fauconnier

Si, tu peux utiliser une colonne formulée. Ca simplifie le code...

d'où j'ai besoin de votre aide!

Je suis super débutant dans ce domaine.

quel événement je dois utiliser pour lier ma formule SI à ma macro EnvoiMailCDO ?

l'événement change ne fonctionne pas sur les formules,
l'événement calculate oui mais le problème c'est que j'arrive pas à l'utiliser, je dois faire beaucoup d'erreurs de code, et surtout il y a pas de target, donc dès qu'il va y avoir une modification sur ma feuille ça va envoyé un mail,
alors qu'ici je vais avoir un tas de date et de mails différents correspondant.

edit: je n'ai pas vu la suite de ton message, 1000 excuses, j'y regarde

**Mav77** · 31/10/2018, 10h24

Envoyé par Pierre Fauconnier

[...]
Avec un tableau structuré appelé t_Suivi disposant d'une colonne Echéance, tu pourrais utiliser le code suivant[...]

Bonjour Pierre,

j'ai renommé ma macro d'envoie mail "SendMail" pour correspondre avec ton code.
Ce code je l'ai placé avant celui d'envoie mail, j'imagine que c'est bon?

J'ai fait mon tableau structuré nommé t_suivi avec la colonne Echéance. (Au passage nickel ton tuto sur les tableaux structurés!)
Je teste la macro checkdates, et ça me marque erreur de compilation : for sans next, où dois-je placer ce next?

Ce code va donc scanner les dates de la colonne Echéance et les comparer à la date d'aujourd'hui que j'aurai mis en formule sur une cellule quelconque, c'est bien ça?

**pijaku** · 31/10/2018, 10h28

Bonjour,

Le Next vient, forcément, en "fin" de boucle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub CheckDates()
  Dim Cel As Range
 
  For Each Cel In Range("t_Suivi[Echéance]")
    If Cel.Value < Date Then SendMail Cel.Row
  Next
End Sub

**Pierre Fauconnier** · 30/10/2018, 15h48

Il n'y a pas besoin de gérer l'événement Change. Tu appelles la macro CheckDates à l'ouverture du fichier (sur l'événement Workbook_Open).

Si tu veux appeler la macro sur l'événement Change, c'est parce que quelqu'un change la date dans le fichier... Mais si personne ne change la date manuellement, il n'y a pas d'événement Change...

**Pierre Fauconnier** · 31/10/2018, 10h28

Envoyé par Mav77

[...]
Je teste la macro checkdates, et ça me marque erreur de compilation : for sans next, où dois-je placer ce next?[...]

Erreur dans mon code... (J'ai codé sans tester, honte sur moi)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub CheckDates()
  Dim Cell As Range
 
  For Each Cell In Range("t_Suivi[Echéance]")
    If Cell.Value < Date Then SendMail Cell.Row
  Next
End Sub

L'ordre des procédures/fonctions n'a pas d'importance

Envoyé par Mav77

[...]
Ce code va donc scanner les dates de la colonne Echéance et les comparer à la date d'aujourd'hui que j'aurai mis en formule sur une cellule quelconque, c'est bien ça?

La date du jour est donnée par la fonction Date utilisée dans le code (dans la ligne de comparaison).

**Mav77** · 31/10/2018, 10h34

Ok! Merci à vous deux pour la réponse rapide!

Je continue un peu à vous embêter avec mes questions de débutant,

maintenant ça me marque:

Erreur d'exécution '1004':

La méthode 'Range' de l'objet'_Global' a échoué

J'ai pourtant bien crée le tableau structuré qui a comme nom t_suivi et la colonne Echéance

**Pierre Fauconnier** · 31/10/2018, 10h36

Sur quelle ligne?

As-tu plusieurs classeurs ouverts? Si oui, note que par défaut, la plage du tableau structuré est recherchée sur le classeur actif. Si tu as plusieurs classeurs ouverts, le problème pourrait venir de là.

**pijaku** · 31/10/2018, 10h44

Je viens de tester.

C'est bête, mais c'est comme ça :

Mon Excel à moi, lorsque j'ai saisi "Echéance" comme entête de colonne, a corrigé en "Échéance"

Du coup, VBA ne connait pas la colonne t_Suivi[Echéance] mais t_Suivi[Échéance]

Lancer une macro via une formule

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager