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...

[Invité]

Bonsoir,
Avec VB script, il est possible se travail sur un fichier Excel, et si la structure de ton xls le permet tu peux le travailler avec ado. De ce fait tu l'ouvre même pas le fichier. En utilisant le planificateur de taches de Windows tu n'as même plus besoin de t'en procurer!

http://www.developpez.net/forums/d14...t/#post7968025

[eric4459]

Bonjour Petit excelleur,
La macro fonctionne chez moi, pour t'aider à comprendre ce qui ne fonctionne pas fais tourner la macro en mode pas à pas: ouvres l’éditeur de macro, cliques au début du code et appui sur la touche "F8" pour faire avancer ta macro en pas à pas.
Place également un espion : sélectionnes avec ta souris l'expression

Code :

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

Int(Date_Fin.Offset(i, 3) - Date_Jour)

, appuis sur Maj + F9, une fenêtre apparaît et appuis sur ajouter, tu verras une nouvelle fenêtre en bas dans l’éditeur qui contient l'expression et la valeur associée lors de chaque changement de valeur de ta variable "i".
De cette façon tu pourras déterminer ce qui ne va pas.

Sinon pour la condition à 1 jour tu ecris :

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

et pas

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

Eric

[le petit excelleur]

Ok, donc avec l'espion j'ai trouvé mon erreur.
Merci du conseil

Sinon, la dernière chose qu'il me reste en fait à régler est au niveau du mail.

Après la première ligne

Blablabla,

je souhaiterai que ça aille me recherché juste le nom prénom de la personne en fait.
De manière à ce que ça me donne en gros :

Blablabla,

blablabla de NOM PRENOM blablabla,

a la date du XXX.

Tu vois ce que je veux dire.

Mais bon sinon parfait j'ai déjà régler mon problème N°1, donc la c'est surtout pour la forme du texte

[eric4459]

Bonjour Petit excelleur,
Regardes ceci, c'est ce que j'ai indiqué plus haut il y a trois jour

Code :

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

1
2
3
4
5
6
7
8
9
10
 
 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" & _

Eric

[davido84]

Avec VB script, il est possible se travail sur un fichier Excel, et si la structure de ton xls le permet tu peux le travailler avec ado. De ce fait tu l'ouvre même pas le fichier. En utilisant le planificateur de taches de Windows tu n'as même plus besoin de t'en procurer!

Intéressant comme procédure...peux-tu expliquer pas à pas comment tu t'y prends ? As-tu un exemple précis à fournir ?
A+

[Marc-L]

Bonjour,

Excel peut être piloté en VBScript comme utiliser des ActiveX, exemple du forum VBScript …

_________________________________________________________________________________________________________

Je suis Paris, Charlie, Bruxelles, …

[Invité]

Bonjour,
VBScript est un fichier texte comme fichier.bat du Dos (Fichier.Vbs).


le VBScript n'st pas plus pauvre que Vba mais il ne connait pas le type de variable dim toto as string tu oublis dim toto ça c'est Ok!




reprenons le code d'envoi de mail comme dans le lien que je t'ai donné mais en dé-typant le variable.

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
Sub Test()
 Mail "Sujet", "Message", "Destinataire@gmail.com", ""
End Sub
Sub Mail(Sujet, Message, Destinataire, DestinataireCopy, DestinataireCopyCacher, Pj)
Set objOutlook = CreateObject("Outlook.application")
Set MailObj = objOutlook.CreateItem(olMailItem)
With MailObj
    .To = Destinataire
    .CC = DestinataireCopy
    .BCC = DestinataireCopyCacher
    .Subject = Sujet
    .BodyFormat = 2
    .HTMLBody = Message
    If Trim("" & Pj) <> "" Then
        p = Split(Pj & ";", ";")
        For I = 0 To UBound(p)
            If Trim("" & p(I)) <> "" Then .Attachments.Add Trim("" & p(I))
        Next
    End If
    .Send
End With
End Sub

le code est quasi identique.
maintenant pour travailler sur un fichier.Xls fermé il faut seconnecté via AdoDb

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Function ConnetFichier(Fichier)
Dim Cn
Set cnn = CreateObject("ADODB.Connection")
    With cnn
        .CommandTimeout = 500
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
            & Fichier & ";" & "Extended Properties=""Excel 12.0;HDR=YES;Readonly=true"";"
        .Open
    End With
Set ConnetFichier = Cn
End Function

j 'ais dis plus haut pas de variable typées
maintenant nous pouvons créer un sub générale au programme. je l'appelle Main mais toto peux fair l'affaire, je veux dire qu ce n'est pas la procédure obligatoire de lancement.

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
Sub Main()
Dim Cn
Set Cn = ConnetFichier("C)
Dim Sql
Sql = "Select * from [Mail$]"
Dim Rs
Set Rs = CreateObject("Adodb.recordset")
Rs.Open Sql, Cn
While Rs.EOF = False
     Mail "" & Rs("Sujet"), "" & Rs("Message"), "" & Rs("Destinataire"), "" & Rs("DestinataireCopyCacher"), "" & Rs("Pj") 'ici on place des "" pour éviter les valeur null
    Rs.movenext
Wend
Rs.Close
Cn.Close
Set Rs = Nothing
Set Cn = Nothing
End Sub

VBScript es comme ficher.bat donc il nous ait possible d’écrire tous le code direct à la ligne sens sub n'y function ma je trouve plus propre et surtout plus lisible d'écrire avec. le faut donc que appel ma sub Main en dehors de toute sub et function

Code :

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

Main

note que si le variables ne sont pas typées elle doivent impérativement être déclarées pas d'option implicite!




je T 'invites à téléchargé VbsFactory il est gratuit.


et voilà le code dans sa totalité.

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
Sub Mail(Sujet, Message, Destinataire, DestinataireCopy, DestinataireCopyCacher, Pj)Set objOutlook = CreateObject("Outlook.application")
Set MailObj = objOutlook.CreateItem(olMailItem)
With MailObj
    .To = Destinataire
    .CC = DestinataireCopy
    .BCC = DestinataireCopyCacher
    .Subject = Sujet
    .BodyFormat = 2
    .HTMLBody = Message
    If Trim("" & Pj) <> "" Then
        p = Split(Pj & ";", ";")
        For I = 0 To UBound(p)
            If Trim("" & p(I)) <> "" Then .Attachments.Add Trim("" & p(I))
        Next
    End If
    .Send
End With
End Sub
Function ConnetFichier(Fichier)
Set ConnetFichier = CreateObject("ADODB.Connection")
    With ConnetFichier
        .CommandTimeout = 500
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
            & Fichier & ";" & "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
        .Open
    End With
End Function
Sub Main()
Dim Cn
Set Cn = ConnetFichier("c:\fichier.Xlsx")
Dim Sql
Sql = "Select * from [Mail$]"
Dim Rs
Set Rs = CreateObject("Adodb.recordset")
Rs.Open Sql, Cn
While Rs.EOF = False
     Mail "" & Rs("Sujet"), "" & Rs("Message"), "" & Rs("Destinataire"), "" & Rs("DestinataireCopyCacher"), "" & Rs("Pj") 'ici on place des "" pour éviter les valeur null
    Rs.movenext
Wend
Rs.Close
Cn.Close
Set Rs = Nothing
Set Cn = Nothing
End Sub
 
 
Main

[Marc-L]

Je me doutais Robert que tu allais passer par là : !

[Invité]

oui je regarde tous les jours les nouvelles! j'ais déserté à la période de commérage.

je suis plus sur Access vba es Access Sql en plus c'es ma tasse de thé!

[eric4459]

oui je regarde tous les jours les nouvelles! j'ais déserté à la période de commérage.

Robert,
Je ne pense pas que ça soit du commérage , j'essaie comme je peux d'être assez clair pour quelqu'un qui débute.
Effectivement ta méthode en Vb Script est intéressante mais il s'agit d'une autre forme de VB, pour quelqu'un qui ne maitrise pas VBA et cherche à s'améliorer ce sera peut-etre l'étape suivante, tout le monde sur ce forum n'est pas developpeur les personnes dont je parle, moi y compris, ce serait plutot
Eric

[Invité]

Bonjour Eric
Je suis à la la fois d'accord et pas d'accord.


Le code Vbs que je propose fonction sur Excel Vba alors que le même code en version Excel Vba ne fonctionnerait pas en Vbs!

C'est un peut comme le sudoku c'est des chiffres mais ce n'est pas des maths! C'est limite un phobie!

Je ne pense pas que ça soit du commérage

je parlais de faits qui se sont déroulés et rien à voir avec ce qui nous préoccupe et tes remarques sont toujours pertinentes.

Et puis je n'ai fait que répondre à une sollicitude même si je suis à l'origine de cet intérêt!

[eric4459]

Pas de soucis Robert , je suis un poil parano

[davido84]

Merci pour cet exemple rdurupt mais j'ai besoin de quelques explications pour tester :
si je comprends bien l'ensemble du code doit être enregistré dans un fichier test.vbs

Concernant le fichier Excel :

Comment doit-il être configuré ?

Doit-il contenir sur chaque ligne et dans cet ordre :
- le sujet en col 1
- le message en col 2
- le mail du destinataire en col 3
- le mail du destinataire en copie caché en col 4
- le chemin complet de la PJ en col 5 ?

La feuille du fichier Excel doit être nommée "Mail".

Une fois le fichier Excel configuré et le code que tu as fourni enregistré dans le fichier test.vbs je double clique sur le fichier et les mails sont envoyés.

C'est ça ou j'ai tout faux ?

Dans ton exemple tu passes par Outlook mais je suppose que le principe est adaptable à gmail via cdo...

A+

[Invité]

Merci pour cet exemple rdurupt mais j'ai besoin de quelques explications pour tester :
si je comprends bien l'ensemble du code doit être enregistré dans un fichier test.vbs

Concernant le fichier Excel :

Comment doit-il être configuré ?
L'ordre et le nom de colonnes n'importe peux
Doit-il contenir sur chaque ligne et dans cet ordre :
Si je reprends tons exemple
Select [col 1],[col 2],ETC...
- le sujet en col 1
- le message en col 2
- le mail du destinataire en col 3
- le mail du destinataire en copie caché en col 4
- le chemin complet de la PJ en col 5 ?

Mail toto comme tu veux mais impérativement suivit de $

From [toto$]
La feuille du fichier Excel doit être nommée "Mail" Une fois le fichier Excel configuré et le code que tu as fourni enregistré dans le fichier test.vbs je double clique sur le fichier et les mails sont envoyés.
oui test.vbs ou envoiMail.vbs mais vbsfactory le fera et il rend lisible ton code télécharge leC'est ça ou j'ai tout faux ?

Dans ton exemple tu passes par Outlook mais je suppose que le principe est adaptable à gmail via cdo...
Oui tu peux utiliser la version cdo du même lien
A+

avec vbs factory tub pourra même crypter ton fichier pour protéger to login mot de pass!