Discussion :

[le petit excelleur]

Salut à tous,

Je sais que beaucoup sont en mode EURO 2016, mais je fais quant même appel à la communauté restante active pour m'aider sur une de mes problématique.

J'explique mon problème (assez simple en somme).
J'ai un fichier (en PJ) pour lequel j'aimerais trouver une formule ou macro (je pense qu'une formule c'est impossible), pour envoyer un mail à deux adresse défini à chaque fois qu'une des période d'essai ou une des période de fin de contrat se rapproche.

En réalité, j'aimerai recevoir un mail à chaque fois qu'on est à une semaine d'une fin de période d'essai (ou de fin de contrat), et un autre quand on est à un jour de la fin.

Si certains ont des idées je suis vraiment preneur car là je suis un peu perdu.

SI e n'ai pas été clair ou si vous avez besoins d'autres infos, n'hésitez pas

Le petit excelleur

J'ai oublié de préciser, mais le logiciel est Outlook pour l'envoi de mail

[EricDgn]

Est-ce vraiment la bonne approche ?

Excel ne peut envoyer d'email que quand il est ouvert. Aussi, il faudra quand même ouvrir chaque jour le fichier Excel pour vérifier les dates et si nécessaire envoyer les emails. Le fichier étant ouvert, il est tout à fait facile de mettre en évidence les dates critiques, par exemple par mises en forme conditionnelles.

Maintenant, Excel pourrait aussi inscrire des tâches dans le calendrier Outlook.

Cdt

[eric4459]

Bonjour Le petit excelleur,
Comme le disait justement EricDgn, il faut qu'excel soit ouvert pour envoyer un email.
Quoiqu'il en soit voici un code qu'il te faudra lancer chaque jour, il est assez simple et il faudra l'adapter pour l'envoi du mail la veille de la fin de periode d'essai, mais tu es ici pour apprendre non ?

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
62
63
64
65
66
67
68
69
Sub Envoi_Mail()
 
Dim ol As Object
Dim myItem As Object
Dim List As String
Dim ListDest As String
Dim Des As Worksheet
Dim Dest As Range
Dim Fl1 As Worksheet
Dim Date_Fin As Range
Dim Date_Jour As Range
Dim Nb1 As Integer
Dim NbDest As Integer
Set Fl1 = ThisWorkbook.Sheets("Feuil1")
Set Date_Fin = Fl1.Range("A2")
Set Date_Jour = Fl1.Range("G1")
Set Des = ThisWorkbook.Sheets("Destinataire")
Set Dest = Des.Range("A1")
Set ol = CreateObject("outlook.application")
Set myItem = ol.CreateItem(olMailItem)
 
Date_Jour = Now ' ICI ON MET LA DATE DU JOUR EN G1
 
With Fl1
    Nb1 = .Cells(.Rows.Count, 1).End(xlUp).Row - 1 ' Compte le nombre de ligne de la feuille Feuil1 (On précise "-1" pour ne pas compter l'intitulé de la colonne
End With
 
With Sheets("Destinataire")
    NbDest = .Cells(.Rows.Count, 1).End(xlUp).Row  ' On compte le nombre de ligne de la liste de destinataire
End With
 
Application.DisplayAlerts = False
 
For i = 0 To NbDest
If Dest.Offset(i, 0) <> "" Then
List = List & ";" & Dest.Offset(i, 0) 'ici on génère la liste des destinataires
                                      '(toutes les adresses mail sont en colonne A de la feuille "Destinataire")
End If
Next i
 
For i = 0 To Nb1
'Date_Fin.Offset(i, 3).NumberFormat = "[$-410]dd-mmm-yy;@"
 If Date_Fin.Offset(i, 3) <> "" And Int(Date_Fin.Offset(i, 3) - Date_Jour) = 7 Then 'Si la date de fin prévue n'est pas vide et si la différence = 7 (1 semaine)
    ListDest = List ' Ici on affecte tous les destinataires
    myItem.To = ListDest ' Ici on rempli le champ destinataires
    myItem.Subject = "Fin de Periode d'Essai " & Dest.Offset(0, 2) & "."  ' Titre du mail
                                                            ' Ci-dessous on génère le texte du mail
    myItem.Body = "BlaBla," & _
    vbCrLf & vbCrLf & _
    "BlaBlaBlaBla" & _
    vbCrLf & _
    "A la date du : " & Date_Fin.Offset(i, 3) & "." & _
    vbCrLf & _
    " La période d'Essai sera terminée " & _
    vbCrLf & _
    "BlaBlaBlaBla" & _
    vbCrLf & _
    "Cordialement" & _
    vbCrLf & _
    "Moi-meme"
 
    myItem.Send
    Set ol = Nothing
 
 End If
 Next i
 
Application.DisplayAlerts = True
End Sub

A noter que j'ai renommé une feuille pour y mettre la liste des destinataires et que tu ne pourra pas vérifier le corps du message avant envoi, à moins bien sur de glisser un bout de code pour faire apparaitre le texte (MsgBox)

Eric

[le petit excelleur]

Salut à tout les deux,

Tout d'abord merci pour vos réponses, et oui, tu as raison je suis la (et je suis volontaire) pour apprendre.

Alors le fichier est ouvert tous les jours sans exception donc la n'est absolument pas le problème dans mon cas.

Ce que je ne comprends pas en revanche, c'est qu'avec ta macro il faut que je la modifie tous les jours ?

En fait je pensais sinon pour simplifier faire une cellule avec une sorte de compte à rebours jusqu'à la fin de la période d'essai ou de fin de contrat.

Comme cela ça serait peut être plus simple de dire à Excel d'envoyer un mail à mes X destinataires quand le compte à rebours est à 30, 14, 7 et 1, plutôt que de faire une macro qui cherche dans les deux colonnes non non?

Une autre chose, pour le texte de mon mail, je peux lui dire d'aller directement chercher le nom de la personne dont la période d'essai se termine ou je dois avoir un texte fixe ?

Merci quand même pour le temps que vous prenez pour m'aider

[eric4459]

Bonjour Le petit excelleur,
Pas besoin de modifier le macro tous les jours, mais simplement la lancer quand tu le souhaites.
La macro calcule en fait la différence entre la date de fin qui figure en colonne D "Date de fin de période d'essai" et la date du jour

Code :

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

Date_Jour = Now

.
Si le résultat est égal à 7, alors on envoi le mail, pour la faire fonctionner 1 jour avant la date de fin, il te faut ajouter une condition:

Code :

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

Si différence égal 1 alors...

Pour aller chercher le nom de la personne concernée, il faut faire référence aux cellules adéquates:
Comme on boucle ligne par ligne, il faut lorsque la condition est vraie, aller chercher ces cellules.

Par exemple:

Tu as 10 lignes, cette partie va te donner ce nombre de lignes

Code :

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

1
2
3
With Fl1
    Nb1 = .Cells(.Rows.Count, 1).End(xlUp).Row - 1 ' Compte le nombre de ligne de la feuille Feuil1 (On précise "-1" pour ne pas compter l'intitulé de la colonne
End With

On va boucler sur toutes les lignes

Code :

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

For i = 0 To Nb1

Si par exemples lorsque i = 4 ta condition est vraie ( lorsque i=4 en fait Date_Fin.Offset(4,3) correspond à la cellule D6 car ta référence est en "A2"

Code :

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

Set Date_Fin = Fl1.Range("A2")

(A2.Offset(4,3) => cellule A2 décalée de 4 lignes et de 3 colonnes)

Code :

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

If Date_Fin.Offset(i, 3) <> "" And Int(Date_Fin.Offset(i, 3) - Date_Jour) = 7 Then

La condition est : il faut que ta cellule ne soit pas vide, elle peut être vide quand la période de fin est révolue, et que la partie entière de la différence soit égale à 7.

Je précise partie entière car la fonction "Now" renvoie la date mais aussi l'heure et dans ce cas la différence ne sera jamais un entier.

Donc la ligne 6 (pour i =4) répond à ta condition, pour avoir le nom, il faut faire référence a la cellule "A6" et cette référence est

Code :

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

Date_Fin.Offset (i,0)

("A2" décalée de 4 lignes et de 0 colonne),

le prénom sera lui en

Code :

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

 Date_Fin.Offset(i,1)

("A2" décalée de 4 lignes et de 1 colonne)

Dans le texte tu dois donc mettre par exemple:

Code :

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

1
2
3
4
5
6
7
8
9
 myItem.Body = "BlaBla," & _
    vbCrLf & vbCrLf & _
    "BlaBlaBlaBla" & _
    vbCrLf & _
    "A la date du : " & Date_Fin.Offset(i, 3) & "." & _
    vbCrLf & _
    " La période d'Essai de " & Date_Fin.Offset(i,0) & " " & Date_Fin.Offset(i,1) & " sera terminée " & _
    vbCrLf & _
    "BlaBlaBlaBla" & _

Voila, j’espère que c'est un peu plus clair comme ceci, sinon reviens nous voir pour plus d'explications ou de détails.
Eric

[le petit excelleur]

Salut,

Merci pour ton retour,

J'ai essayé la macro (je suis en réalité en train de le faire maintenant), mais quand je la lance, rien ne se passe...

J'ai pourtant bien rajouter ma feuille Destinataire avec mes adresses mail en colonne A, et insérer ma date du jour en G1

Et j'ai aussi par mesure de test mis une date de fin de période d'essai et une de fin de contrat au 5/7/16 soit dans 7 jours.

J'avoue que je ne comprend pas trop pourquoi elle ne fonctionne pas étant donné que pour moi j'ai bien suivi le process.


Sinon si je veux rajouter une condition (envoi à un jour avant la fin), la seule chose que j'ai à faire est en réalité de mettre à la place de :

Code VBA :

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

 If Date_Fin.Offset(i, 3) <> "" And Int(Date_Fin.Offset(i, 3) - Date_Jour) = 7 Then

=>

Code VBA :

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

 If Date_Fin.Offset(i, 3) <> "" And Int(Date_Fin.Offset(i, 3) - Date_Jour) = 7 Or If Date_Fin.Offset(i, 3) <> "" And Int(Date_Fin.Offset(i, 3) - Date_Jour) = 7 Then.

C'est ça non?

Mais la j'avoue que je suis déjà bloqué par ce problème d'envoi de mail à 7 jours qui ne marche pas...