Macro envoimail associer à fonction SI

sylvain974 · 9 Mars 2015

Bonjour,

Suite à plusieurs recherches infructueuses je me permet de vous demandez comment associer ma macro d'envoi de mail à ma fonction Si.
le contexte est le suivant: Lorsque une date arrive à échéance un envoi de mail est envoyé par ma fonction SI.

Function Mail_Workbook_1()
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail
.To = "<mon adresse"
.CC = ""
.BCC = ""
.Subject = "Fin de validité"
.Body = "Bonjour La date de validité du controle technique arrive à terme, n'oubliez pas de l'actualiser"
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Function

Je voudrais l'associer à :

=SI(G4>=AUJOURDHUI()-7;"attention:arrive à échéance";"ok") et donc remplacer attention:arrive à échéance à un envoi de mail

Merci pour vos futurs réponses

Staple1600 · 9 Mars 2015

Re : Macro envoimail associer à fonction SI

Bonjour à tous

Sylvain974 [highlight][Bienvenue sur le forum][/code]
Essaie déjà ce petit bout de code en lançantg la macro nommée : AlerteECHEANCE
NB: Je me suis permis d'alléger un chouia ta macro d'envoi de mail 😉

Code:

Sub AlerteECHEANCE()
If Range("G4") > Date - 7 Then
MsgBox "attention:arrive à échéance", vbCritical
Send_Mail
Else
MsgBox "ok"
End If
End Sub

Code:

Private Function Send_Mail()
On Error Resume Next
With CreateObject("Outlook.Application").CreateItem(0)
    .To = "mon adresse"
    .Subject = "Fin de validité"
    .Body = "Bonjour La date de validité du contrôle technique arrive à terme, n'oubliez pas de l'actualiser"
    .Send
End With
On Error GoTo 0
End Function

EDITION: On peut aussi passer par une procédure événementielle (voir exemple basique ci-dessous)
Le code ci-dessous dans le code de la feuille concernée

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G4") > Date - 7 Then
MsgBox "attention:arrive à échéance", vbCritical
Function Send_Mail
Else
MsgBox "ok"
End If
End Sub

Et dans le module standard on modifie Private Function Send_Mail() en Public Function Send_Mail()

Je te laisse faire les différents tests. 😉

sylvain974 · 9 Mars 2015

Re : Macro envoimail associer à fonction SI

Tout d'abord merci Staple1600 pour l’accueil,

j'ai tester Sub AlerteECHEANCE()(erreur de compilation: sub ou function non définie) et Private Sub Worksheet_Change (qui ouvre l'exe des macros) donc aucun résultat
Quel est la différence entre private et public pour VBA ?
Pour ce qui est d'allégée le code sendmail il n'y a aucun soucis temps que cela fonctionne 🙂
Cependant je n'est tjrs pas de solution pour que cela fonctionne entre ma formule et mon macro...

PS: Désolé si mes questions ne sont pas très pertinente je suis un amateur de VBA.

Staple1600 · 9 Mars 2015

Re : Macro envoimail associer à fonction SI

Bonsoir à tous

sylvain974
Configuration1
Les macros suivantes Sub AlerteECHEANCE() et Private Function Send_Mail() sont stockés dans module standard (ALT+F11 -> Menu Insertion/Module)
Pour tester j'ai ajouté cette petite macro de test: TestduIf
(Copies ces macros dans le module d'un classeur vierge et lance la macro TestduIf
[TABLE="width: 765, align: center"]
[TR]
[TD]
Sub TestduIF()
Dim datesTest, i As Byte
datesTest = Array(1, 3, 7, 9, 11)
For i = LBound(datesTest) To UBound(datesTest)
Range("G4") = _
DateAdd("d", CDbl(datesTest(i)), Date - 15)
Call AlerteECHEANCE
Next
End Sub
[/TD]
[TD]Sub AlerteECHEANCE()
If Range("G4") > Date - 7 Then
MsgBox "Arrive à échéance.", 16, "Attention!"
Send_Mail
Else
MsgBox "ok"
End If
End Sub
[/TD]
[/TR]
[TR]
[TD]
Private Function Send_Mail()
On Error Resume Next
'juste pour tester si le "SI" fonctionne
MsgBox "Test ->" _
& vbCrLf & "Simule l'envoie de mail", vbExclamation, "Test"
'With CreateObject("Outlook.Application").CreateItem(0)
' .To = "mon adresse"
' .Subject = "Fin de validité"
' .Body = "Bonjour La date de validité du" _
& "contrôle technique arrive à terme," _
& " n'oubliez pas de l'actualiser"
' .Send
'End With
On Error GoTo 0
End Function
[/TD]
[TD]
[/TD]
[/TR]
[/TABLE]
Tu remarqueras que cela fonctionne, non ?
Donc pour tester l'envoi d'un mail, il suffit de refaire le test en supprimant la ligne
MsgBox "Test ->" _& vbCrLf & "Simule l'envoie de mail", vbExclamation, "Test"
et de dé-commenter les autres lignes de code en supprimant le guillemet en début de ligneNB. ne pas supprimer de guillemet devant les lignes vertes qui sont des lignes de commentaires.

Configuration2
On utilise une procédure Private Sub Worksheet_Change(ByVal Target As Range)
(qui comme indiqué précédemment doit être stocké dans le code de la feuille où on teste la valeur de G4)
Et on doit avoir dans un module la function Send_Mail à laquelle on aura remplacé le préfixe Private par Public.

sylvain974 · 10 Mars 2015

Re : Macro envoimail associer à fonction SI

Bonjour,

Super ça marche merci ! maintenant j'aimerai bien que ceci soit automatisé.

Je vous montre mon but final :

Engins / Entreprise/ Nom du chauffeur /Date d'entrée / Fiche d'acceuil / Autorisation de conduite /Date vérification générale (valable 6 mois)

Pelle 11T/XXXX / XXXXXXXXX / 20/01/15 / ok / oui / 20/01/15

C'est ici que ma formule interviendrait, elle envoi un mail 7 jours avant l'échéance de la VG de l'engin

Suis-je obliger de mettre manuellement la macro ? Y a-t-il un moyen d'automatisé cette action ?

Sachant que ce tableau contient plusieurs engins et plusieurs Dates de contrôles.

Merci d'avance

Staple1600 · 10 Mars 2015

Re : Macro envoimail associer à fonction SI

Bonjour à tous

sylvain974
Voici venu le temps peut-être de suivre ce judicieux conseil qui normalement n'aurait pas du t'échapper 😉

[highlight][Lien supprimé] – La possibilité de joindre des fichiers est donnée sur ce forum.
Ne pas hésiter à utiliser cette fonction, tout en veillant que les données soient bidons
et donc qu’aucune donnée confidentielle, nominative ne soit dans le fichier.[/code]

Suis-je obliger de mettre manuellement la macro ? Y a-t-il un moyen d'automatisé cette action ?

Il faudra songer à changer de lunettes, camarade 😉

Staple1600 à dit:
EDITION: On peut aussi passer par une procédure événementielle (voir exemple basique ci-dessous)
Le code ci-dessous dans le code de la feuille concernée

Cela permet l'automatisation de la chose 😉

Consulte Ce lien n'existe plus et relis mes consignes des précédents messages pour mettre cela en musique dans le cadre de ta question. 😉

sylvain974 · 10 Mars 2015

Re : Macro envoimail associer à fonction SI

Re bonjour,

heureusement avoir préciser :

PS: Désolé si mes questions ne sont pas très pertinentes je suis un amateur de VBA.

Ce n'est pas logique pour tout le monde une "procédure événementielle" .

Maintenant que j'ai pris, grâce a vous connaissance de cette procédure et de nombreux essais j'ai donc rajouté dans la feuille de code pour G4 :

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G4") > Date - 7 Then
MsgBox "attention:arrive à échéance", vbCritical
Function Send_Mail
Else
MsgBox "ok"
End If
End Sub

De plus j'ai remplacé private par public pour sendmail pour être sûr je l'ai mis dans la feuille concernée et feuille 2 (appart)

lorsque vous dites :

Configuration2
On utilise une procédure Private Sub Worksheet_Change(ByVal Target As Range)
(qui comme indiqué précédemment doit être stocké dans le code de la feuille où on teste la valeur de G4)
Et on doit avoir dans un module la function Send_Mail à laquelle on aura remplacé le préfixe Private par Public.

Vous voulez parler de feuille ?

De plus une erreur apparaît:

Erreur de compilation : End sub attendu

alors qu'il y a End function et si je rajoute End sub ou que je remplace cela n'a aucun effet .

Staple1600 · 10 Mars 2015

Re : Macro envoimail associer à fonction SI

Bonjour à tous

sylvain974
Je réitère mon précédent conseil 😉
La possibilité de joindre des fichiers est donnée sur ce forum.
Ne pas hésiter à utiliser cette fonction, tout en veillant que les données soient bidons
et donc qu’aucune donnée confidentielle, nominative ne soit dans le fichier.

En clair, peux-tu, s'il te plait, joindre un fichier Excel exemple (allégé et anonymisé) mais respectant la structure de ton classeur original ?
Cela nous permettra de tester le code VBA sur nos PC.
(et accessoirement, cela évitera à chaque membre du forum désireux de te répondre de devoir recréer un fichier qui existe déjà sur ton disque dur 😉)

A te relire bientôt donc avec un fichier Excel joint pour continuer d'aller plus loin. 😉

sylvain974 · 11 Mars 2015

Re : Macro envoimail associer à fonction SI

bonjour,

Ah merci Staple1600 pour ta clarté, oui effectivement cela sera beaucoup plus simple.

En espérant avoir était compris sur ma demande (expliquer précédemment) , qui peut être incomprise.

Staple1600 · 11 Mars 2015

Re : Macro envoimail associer à fonction SI

Bonsoir à tous

sylvain974
Certains des codes étaient collés au mauvais endroits.
Vois ci-dessous l'agencement correct des codes.
PS: Pour zommer la plage de cellules, rien d'implémenter pour le moment.

(En résumé, tu dois avoir les macros AlerteECHEANCE et Function SendMail
situées dans un module standard nommé Module1.

Enfin la procédure événementielle Worksheet_Change est dans le module de code de la Feuil1.
(donc ni dans Classe1 (un module de classe) , ni dans le Module1 (module standard) )

sylvain974 · 11 Mars 2015

Re : Macro envoimail associer à fonction SI

bonjour,

Voila j'ai tous mis au bon emplacements.

Cependant l'erreur :

Erreur de compilation : End sub attendu

Revient toujours

PS:Quel différence entre la feuille et le module pour l'emplacement du code ?

Staple1600 · 11 Mars 2015

Re : Macro envoimail associer à fonction SI

Bonjour à tous

sylvain974
Une macro doit finir par End Sub s'il elle commence par Sub
Une fonction VBA doit finir par End Function car elle commence par Function
C'est bien le cas sur ton PC ?
[TABLE="width: 800"]
[TR]
[TD]

Dans le module de la feuille

[/TD]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
Call AlerteECHEANCE
End Sub

[/TD]
[/TR]
[TR]
[TD]

Dans le module standard nommé Module1

[/TD]
[TD]Sub TestduIF()
Dim datesTest, i As Byte
datesTest = Array(1, 3, 7, 9, 11)
For i = LBound(datesTest) To UBound(datesTest)
Range("G4") = _
DateAdd("d", CDbl(datesTest(i)), Date - 15)
Next
End Sub[/TD]
[/TR]
[TR]
[TD="align: center"]

(idem - localisé dans Module1)[/TD]
[TD]Public Sub AlerteECHEANCE()
If Range("G4") > Date - 7 Then
MsgBox "Arrive à échéance.", 16, "Attention!"
Send_Mail
Else
MsgBox "ok"
End If
End Sub[/TD]
[/TR]
[TR]
[TD="align: center"]

(idem - localisé dans Module1)[/TD]
[TD]Public Function Send_Mail()
On Error Resume Next
'juste pour tester si le "SI" fonctionne
MsgBox "Test ->" _
& vbCrLf & "Simule l'envoie de mail", vbExclamation, "Test"
'With CreateObject("Outlook.Application").CreateItem(0)
' .To = "mon adresse"
' .Subject = "Fin de validité"
' .Body = "Bonjour La date de validité du" _
& "contrôle technique arrive à terme," _
& " n'oubliez pas de l'actualiser"
' .Send
'End With
On Error GoTo 0
End Function[/TD]
[/TR]
[/TABLE]

sylvain974 · 11 Mars 2015

Re : Macro envoimail associer à fonction SI

Bonjour,

Oui c'est bien le cas :

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G4") > Date - 7 Then
MsgBox "attention:arrive à échéance", vbCritical
Function envoimail()
Else
MsgBox "ok"
End If
End Function
End Sub

mais cela m’affiche toujours l'erreur pourtant:

Vous avez changer le code de Private Sub Worksheet_Change(ByVal Target As Range) et avec le nouveau ça marche !
Alors déjà un GRAND merci à vous ! 😉

Et maintenant j'aimerai que la cellule H4 (avant G4, voir mon nouveau tableau plus complet) soit variable car j'ai plusieurs dates qui peuvent arrivé à échéance et d'engins aussi !
De plus dès que je modifie quelque chose sur mon tableau excel exemple l'ajout d'une pelle la macro s'active est ce normal ? ne peut'elle pas être active juste pour les futurs cellules variable (donc des dates rentrer) et non pas a chaque modification quelconque de mon tableau ?

Je joint de nouveau mon tableau excel de support qui sera utilisé .

Après plusieurs essais

Sub AlerteECHEANCE()
If Range("H4") >= Date - 7 Then
MsgBox "Arrive à échéance", 16, "Attention!"
Send_Mail
Else
MsgBox "ok"
End If
End Sub

La fonction SI est t'elle bonne car lorsque j'essaye avec la première pelle (pelle 11T) à la date du 19/07/2015, je lance la procèdure et il m'envoi le mail or la date d'aujourd'hui n'est pas a 19/07/15 -7j donc peut etre que

Date -7

ne prend pas le Jour mais le mois ?

Staple1600 · 11 Mars 2015

Re : Macro envoimail associer à fonction SI

Re

sylvain974
Il va falloir rester concentré mon petit bonhome 😉
Code vb:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G4") > Date - 7 Then
MsgBox "attention:arrive à échéance", vbCritical
Function envoimail() 'Jamais je n'ai écrit cela dans aucun des codes que j'ai soumis dans ton fil ... ;-)
Else
MsgBox "ok"
End If
End Function
End Sub

Testes d'abord mes propositions de code sans les modifier d'un iota pour s'assurer que déjà il fonctionne en l'état 😉

sylvain974 · 11 Mars 2015

Re : Macro envoimail associer à fonction SI

Re,

J'ai déjà tester le code initial ...

Il faut savoir c'est :

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G4") > Date - 7 Then
MsgBox "attention:arrive à échéance", vbCritical
Function Send_mail() '
Else
MsgBox "ok"
End If
End Function
End Sub

ou

Private Sub Worksheet_Change(ByVal Target As Range)
Call AlerteECHEANCE
End Sub

J'ai poser plusieurs questions ensuite mais pas de réponse ...

PS:avant de me dire je n'est pas fait le code initial, le code de test je les aussi effectué ça me répète l'action sur plusieurs dates donc je les enlevé puisque j'ai vu que cela marchait .

Macro envoimail associer à fonction SI

sylvain974

XLDnaute Nouveau

Staple1600

XLDnaute Barbatruc

sylvain974

XLDnaute Nouveau

Staple1600

XLDnaute Barbatruc

sylvain974

XLDnaute Nouveau

Staple1600

XLDnaute Barbatruc

sylvain974

XLDnaute Nouveau

Staple1600

XLDnaute Barbatruc

sylvain974

XLDnaute Nouveau

Pièces jointes

Staple1600

XLDnaute Barbatruc

sylvain974

XLDnaute Nouveau

Pièces jointes

Staple1600

XLDnaute Barbatruc

sylvain974

XLDnaute Nouveau

Pièces jointes

Staple1600

XLDnaute Barbatruc

sylvain974

XLDnaute Nouveau

Discussions similaires

Macro envoimail associer à fonction SI

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Nouveau

Discussions similaires

Privacy & Transparency

Privacy & Transparency