Envois d'un mail dès qu'une date est atteinte en excel

Question

Bonjour à tous,

après quelques recherches et un manque de compréhension totale, j'aimerais automatiser une tâche dans mon fichier excel. ( excel 2003 )
En effet, j'ai une colonne date échéance et une colonne email en rapport à la colonne date échéance.
Comment faire pour envoyer un mail dès que la date arrive à échéance.
Avez-vous des indications, exemples ou tout simplement un code qui me permettrait d'effectuer cette action.

Soyez indulgent car si il faut se diriger du côté VBA je suis nul. Mais évidemment je ne demande qu'a apprendre.

Merci pour vos réponses.

kbalist · Answer

Salut,

Tu peux préciser ta demande ?

Que contient ta colonne 'date' ? Quel est le format de la cellule ?
Que contient ta colonne 'email' ? l'adresse mail ? Quel est le document à joindre ou le texte du mail ? etc...

Je vais me pencher sur le problème

ozone083 · Answer

Bonjour,

J'ai le même besoin que ecolom.
Ai un fichier Excel avec des cellules contenant une date (au format DATE)
Je souhaite que 5 jours avant l'échéance, un mail soit envoyé automatiquement à une personne, via Lotus Notes.
Le contenu du mail serait par exemple : "Ne pas oublier de faire".

Merci pour votre aide (ne connais rien en VBA)

Flocreate · Answer

Bonjour,
Il m'ai avis que l'envoie du mail en lui même n'est pas difficile et que bien des sources en donnent les clées.

En revenche ce que vous ne dites pas , ou alors g pas compris, c'est comment la macro est-elle lancée, dans quelle circonstance et comment les mails à envoyer sont-ils trouvés ? comment la comparaison date actuelle / colone d'échéance est elle faite (par formule + filtre ou par boucle dans macro ?)

cordialement,

ozone083 · Answer

Bonjour

Idéalement, il faudrait que le mail soit envoyé même si le fichier Excel est fermée.
Dans la macro, il y'aurait un test : si aujourd'hui = (echeance - 5 jours) alors envoyer un mail à prénom.nom@xxx.fr
La personne recevrait un mail avec en titre du mail un message 1 et dans le corps du mail un message 2

J'espère que cela est plus clair maintenant.

Merci

ecolom · Answer

Bonjour,

désolé pour ma réponse tardive.
En fait pour répondre à Kbalist. La colonne date, contient la date d'échéance et la colonne email contient l'adresse du mail qui devrait être envoyé quand un date arrive à son terme calculée évidemment sur la date du jour.
Exemple: Date échéance ( 19/05/2010 ) | email ( toto.le.heros@yahoo.fr )
Dès que j'active mon fichier excel, il doit vérifier les dates d'échéances par rapport à la date du jour et envoyer un mail à la (aux ) personne(s) concernée(s).
J'ai vu qu'il y avait des scripts pour envoyer le mail. Mais comment mettre cela en application.
Faut-il qu'il y ait un script vb ou peut-on créer une formule qui analyse la date d'échéance et qui enverrait un mail.

Je n'ai pas avancé bcp sur le sujet faute de ne pas trouver d'exemples concrets.

Avez-vous une idée ou un début de code pour que je puisse chercher...

Merci à tous

kbalist · Answer

salut ecolom,

voici le code que je te propose, il est à insérer dans un module de l'éditeur VBA (ALT-F11) quand on est sous Excel.
Pour lancer la macro "TesteDate", tu peux lui associer une combinaison de touche (genre CTRL-e) ou créer un bouton dans ta feuille de calcul Excel. Pense à adapter le paramètrage des constantes avec tes valeurs (paragraphes Initialisations de la macro TesteDate)

Option Explicit

Sub TesteDate()
'envoie un mail si la date est dépassée
Dim sSujet, sBody, sAdresseMail, sAdresseRetour As String 'chaines pour le sujet, corps, adresse d'envoi, adresse de retour
Dim duree As Integer 'nbre de jours entre aujourd'hui et la date à tester
Dim Lig_Deb, Lig_Fin As Integer 'ligne de début, de fin
Dim sDates_Col, sMails_Col As String 'colonnes qui contiennent les dates à tester et les adresses mail
Dim i As Integer

'initialisation des constantes de la macro :
Lig_Deb = 2 'dans ma feuille Excel, les dates à tester commencent en ligne 2
sDates_Col = "C" ' et elles sont en colonne C ( 3 ième colonne)et les adresses mail sont en colonne D à côté

'initialisation des données du mail envoyé :
sSujet = "Votre facture :"
sBody = "Vous me devez des sous !" + vbNewLine + "A payer tout de suite !!" + vbNewLine
sAdresseRetour = "adresse_valide@free.fr"

'Ligne de fin =1ère cellule vide dans la colonne des dates
Lig_Fin = Val(Range(sDates_Col & CStr(Lig_Deb)).End(xlDown).Row)

' boucle de test dans la plage des dates (=> )
For i = Lig_Deb To Lig_Fin
Range(sDates_Col & CStr(i)).Select 'activer la cellule testée
duree = Now - ActiveCell.Value ' la date est dans la cellule active
If duree > 0 Then 'la date est dépassée
   sAdresseMail ActiveCell.Offset(0, 1).Value 'l'adresse mail est dans la colonne suivante offset (0,1)
 'MsgBox ("Envoi de courrier à " & sAdresseMail)
 '  envoyer le mail :
  CDO_SendMail sSujet, sBody, sAdresseMail, sAdresseRetour
Else
  'MsgBox ("La date n'est pas atteinte")
End If

Next i

End Sub

Sub CDO_SendMail(ByVal sSujet As String, ByVal sBody As String, ByVal sAdresseMail As String, ByVal sAdresseRetour)
'MARCHE IMPEC, sans demande de confirmation    ;-)))))
'on peut préciser : le sujet, le corps , l'adresse mail, l'adresse de retour
    Dim iMsg As Object
    Dim iConf As Object
    
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
   
    With iMsg
        .Configuration = iConf
        .To = sAdresseMail
        .Sender = sAdresseRetour 'adresse de l'expéditeur pour le rapport envoyé
        .From = sAdresseRetour 'adresse de l'expéditeur du mail
        .ReplyTo = sAdresseRetour 'adresse à laquelle sera envoyée la réponse
        .CC = ""
        .BCC = ""
        .Subject = sSujet 'sujet du message
        .TextBody = sBody 'corps du message
        '.AddAttachment Fichier 'fichier joint
         .DSNOptions 14 'confirmation demandée dans tous les cas (voir ci-dessous avec 14 8 + 4 + 2)
         '  (0=pas réponse ; 2=rapport si échec ; 4=rapport si réussi ; 8=rapport si délai)
         
         ' pour demander des confirmations de réception ou d'envoi :
        .Fields("urn:schemas:mailheader:return-receipt-to") = sAdresseRetour
        .Fields("urn:schemas:mailheader:disposition-notification-to") = sAdresseRetour
        ' Update fields
        .Fields.Update
        
        ' envoi
        .Send
    End With

End Sub

ozone083 · Answer

Bonjour Kbalist

Je me suis inspiré du code que tu as réalisé pour ecolom pour répondre à mon besoin et cela fonctionne : merci.
Cependant, j'aurai 2 choses encore à ajouter :
     - préciser que la date se trouve dans un onglet spécial (appelé par exemple toto)
     - serait il possible que la macro s'exécute même excel fermé ? (afin de pouvoir recevoir directement le mail sans forcément penser à ouvrir le fichier Excel)

Voici le code (certainement perfectible) :

Public Sub SendNotesMail()
                         
    Dim Maildb As Object      'La base des mails
    Dim UserName As String    'Le nom d'utilisateur
    Dim MailDbName As String  'Le nom de la base des mails
    Dim MailDoc As Object     'Le mail
    Dim AttachME As Object    'L'objet pièce jointe en RTF
    Dim Session As Object     'La session Notes
    Dim EmbedObj As Object    'L'objet incorporé
    Dim destinataire As Variant
    Dim sSujet, sBody, sAdresseMail, sAdresseRetour As String 
    Dim duree As Integer
    Dim Lig_Deb, Lig_Fin As Integer 'ligne de début, de fin
    Dim sDates_Col As  String 'colonnes qui contiennent les dates à tester et les adresses mail
    Dim i As Integer
    'Crée une session notes
    Set Session = CreateObject("Notes.NotesSession")
   
  'initialisation des constantes de la macro :
    Lig_Deb = 2 'dans ma feuille Excel, les dates à tester commencent en ligne 2
    sDates_Col = "C" ' et elles sont en colonne C ( 3 ième colonne)
   
   'Ligne de fin =1ère cellule vide dans la colonne des dates
    Lig_Fin = Val(Range(sDates_Col & CStr(Lig_Deb)).End(xlDown).Row)
    
    'Récupère le nom d'utilisateur et crée le nom de la base des mails
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
   
    'Ouvre la base des mails
    Set Maildb = Session.GETDATABASE("", MailDbName)
    If Not Maildb.IsOpen Then Maildb.OPENMAIL
       
    ' boucle de test dans la plage des dates (=> )
    For i = Lig_Deb To Lig_Fin
    Range(sDates_Col & CStr(i)).Select 'activer la cellule testée
    duree = Now - ActiveCell.Value ' la date est dans la cellule active
    If duree < 5 Then 'echeance est dans 5 jours
    destinataire = "email"
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.sendto = destinataire
    MailDoc.Subject = "Echéance "
    MailDoc.Body = "Ne pas oublier de faire ...."
    MailDoc.SAVEMESSAGEONSEND = SaveIt
    
    MailDoc.PostedDate = Now()
    MailDoc.SEND 0, Recipient
    
    End If
    
    Next i
        
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
End Sub

Un grand merci pour votre aide

ozone083 · Answer

bon finalement ai résolu le premier point via :
 Sheets("toto").Select  

Pour le second, cela serait super si vous pouviez m'aider...

Merci

kbalist · Answer

Je vais réfléchir au problème...

Ca serait un programme en .net qui ouvrirait le fichier excel, lancerait la macro et refermerait le fichier excel ?

ecolom · Answer

Merci Kbalist. J'ai suivi les instructions que tu m'as donné mais je reçois un message erreur VB "Dépassement de capacité" 
Actuellement, j'ai une date en C2 avec la cellule en format date et l'email en D2. 
J'ai créé un bouton pour exécuter la macro.
Que faut-il faire d'autre ?

L'idée d'ozone083 est intèressante ( exécuter la macro avec le fichier excel fermé ). Je suis preneur mais avant tout, il faudrait que cette macro fonctionne avec le fichier excel ouvert..

Merci

ozone083 · Answer

Oui Kbalist, idéalement il faudrait que, par exple, cela soit un fichier .bat qui s'execute au démarrage de windows, active la macro sous Excel et m'envoie ou non un mail ....
Suis peut être un peu exigeant....ou rêveur  

Merci

ecolom · Answer

Autres indications, quand j'ajoute une seconde date en C4 et email en D4 et que la date est égal à la date du jour, je reçois un message erreur. 
> erreur système &H80040220 (-214720960)

Quand j'inscris une date supérieure à la date du jour, plus de message. il semble que dans ce cas la fonction de test fonctionne.
Par contre, je vais peut-être dire un bétise mais tant pis ( j'suis débutant, on peut rire de moi ) 
Dans le code nous avons cette ligne : 
Dim sDates_Col, sMails_Col As String 
et je remarque que tu déclares la variable sDATE_col : 
Lig_Deb = 2 
sDates_Col = "C"
et pourquoi n'y a t-il pas la variable sMails_col = "D"

Est-ce normal ?

Merci

kbalist · Answer

A ecolom :

j'ai essayé sur un fichier Excel qui avait 7 ou 8 dates et mails, pas de message d'erreur...
tu as combien de dates ?
A quel moment reçois-tu le message "Dépassement de capacité" ?

tu parles de seconde date en C4, cela devrait être la 3ième car le test s'arrête dès qu'une cellule vide (C3 en l'occurrence) est rencontrée

la variable sMails_col = "D" n'est pas nécessaire car dans la boucle je prends l'adresse mail dans la cellule qui est juste à droite de la cellule courante avec l'instruction
sAdresseMail = ActiveCell.Offset(0, 1).Value

pour les dates, je viens de retester, je n'ai aucun message d'erreur le jour même (le message ne part que si la date est dépassée : le lendemain de l'échéance)

tu peux m'envoyer ton fichier par MP ?

ecolom · Answer

Oups désolé, je me suis emmêlé les pinceaux...
J'ai simplement copié ton code dans une nouvelle feuille et ajouté deux lignes dates dans les zones prédéfinies.
J'ai écrit C4 et D4 alors que j'ai bien suivi l'ordre des colonnes. Il fallait lire C3 et D3. J'suis trop fatigué..
Je savais que si il y avait un blanc que la procédure s'arrêterait.

Par contre, j'ai activé les messages box pour visualiser les résultats et je remarque que dès que les dates sont correctes, le message affiche " La date n'est pas atteinte"  mais...dès que la date est passée, le message m'indique qu'il va envoyer un mail. Dès que je réponds OK, le message erreur apparait. J'ai l'impression que le problème apparaît spécifiquement lors de l'envoie du mail et nom pas des conditions de tests.
Attention, j'utilise outlook en standard et je ne pourrai pas changer de soft. 
Je t'envoie le fichier via mp.

Merci pour ton aide.

ozone083 · Answer

Ca y  est, je suis presque au bout ; ai crée un fichier .bat qui execute automatique la macro contenu dans le fichier excel

Mon seul probleme maintenant : je veux que ce fichier se mette à jour via une la liaison avec un fichier source. Mais il faut que cette mise à jour automatique n'ait lieu qu'entre ces 2 fichiers (je ne peux donc utiliser la solution : Outils\Modifications\Confirmation de la mise à jour automatique des liens)

Il faut donc que j'ajoute une ligne dans ma macro
mais laquelle ????

merci

ozone083 · Answer

je progresse mais ce n'est pas encore parfait.
Ai finalement réussi à trouver une ligne de code permettant de faire la liaison entre les 2 fichiers.

Par contre ce matin ai lancé la macro automatiquement (ie via un fichier .BAT) au démarrage du PC.
Cela ne fonctionne pas car Lotus Notes n'est pas ouvert.
Que faut il faire ? pourriez vous m'aider svp ?? 

Sinon, autre point (mais mineur) :
 Comment faire pour que le texte qui apparaisse dans le mail soit en gras, ou en couleur.

Un grand merci pour votre aide

CYKY34 · Answer

bonjour, 

Pouvez vous me dire pourquoi j'ai cette erreur.
En vous remerciant
Cordialement

CYKY34 · Answer

Bonsoir,

J'ai une nouvelle erreur dans le code:


J'ai été cochée la case dans outil référence Microsoft CDO for Windows  2000 Library :


cela ne change rien toujours même message:


Je ne vois pas d’où vient l'erreur, a-t-il une autre case à cocher dans visual basic ,outil,référence? Pouvez vous m'aider.
En vous remerciant 

Cordialement,

KI · Answer

Bonjour, 

    le code fonctionne parfaitement merci ! 
   Je souhaite rajouter un petit texte dans le corps du mail (pour ne pas l'envoyer vide) ! Est-ce que c'est possible ? 

PS: je viens de commencer sur VBA du coup je trouverais du mal à comprendre sans exemple !

Envois d'un mail dès qu'une date est atteinte en excel

19 réponses

Votre réponse

Discussions similaires