Envoi de relance mail automatique
Bonjour,
Je cherche à créer une boucle sous condition de relance de mail si pas de réponse afin d'automatiser cette procédure de relance qui est chronophage.
Je veux que ma macro scanne mon tableau, compare la date du jour avec celle de la date d'envoi initial et si cette différence est supérieure à 30 jours envoie un mail de relance.
Pour cela j'ai créé une boucle mais elle ne passe pas à la seconde ligne de mon tableau. est ce que quelqu'un aurait une idée sur mon erreur?
Voici mon code :
Sub boucle_while()
Dim numero As Integer
Dim sysdate As Date
Dim datetestee As Date
Dim nbr_jr As Integer
Dim NbLig As Integer
'recherche du nombre de lignes dans mon tableau
NbLig = Range("A1").End(xlDown).Row - 1
numero = 2 'Numéro de départ (correspond ici au n° de ligne)
sysdate = Date
Do While numero <= NbLig 'TANT QUE la variable numero est <= NbLig, la boucle est répétée
datetestee = Cells(numero, 13) 'la date d'envoi du mail initial en colonne M
nbr_jr = sysdate - datetestee 'différence entre date du jour et date d'envoi du mail initial
If nbr_jr > 30 Then '
Call EnvoiMailrelancebis 'lancer macro d'envoi du mail
numero = numero + 1 'Le numéro est augmenté de 1 à chaque boucle
Else
numero = numero + 1 'Le numéro est augmenté de 1 à chaque boucle
End If
Loop
MsgBox "Les mails de relance ont bien été envoyés", vbExclamation, "Confirmation" 'afficher message confirmant l'envoi des relances
End Sub
J'espère avoir été clair.
Merci par avance de l'aide que vous pourriez m'apporter.
Bonjour Titwo,
A première vue, ton code semble correct.
Cependant, je m'interroge sur le calcul de la borne inférieure :
NbLig = Range("A1").End(xlDown).Row - 1Je te suggère le remplacer par :
Dim oSheet as Worksheet
Set oSheet = Thisworkbook.Worksheets("NomFeuille")
NbLig = oSheet.Usedrange.Rows.CountBien entendu en remplaçant "NomFeuille" par le nom de ta feuille.
Merci beaucoup pour cette aide précieuse, j'avais effectivement fait une coquille sur le calcul de borne inférieure. J'ai revu ma copie depuis mais ça ne fonctionne toujours pas, le mail de la première ligne part, mail pas celui de la seconde pourtant je n'arrive pas à comprendre d'où vient mon erreur.
Pour simplifier la présentation de mon code sur ce forum, j'ai intégré toute la création du mail et son envoi avec outlook dans le même codage (je me suis demandé si la mise en route d'une autre procédure ne faisait pas disjoncter ma boucle donc j'ai préféré tout mettre dans une même procédure). Peut être un soucis avec outlook? Est ce que quelqu'un aurait une idée pour résoudre mon problème? Merci par avance
Sub boucle_finale()
Dim numero As Integer
Dim sysdate As Date
Dim datetestee As Date
Dim nbr_jr As Integer
Dim NbLig As Integer
Dim oSheet As Worksheet
'variables mail et corps de texte
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim maildest As String
Dim Sujetmail As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set oSheet = ThisWorkbook.Worksheets("2018")
NbLig = oSheet.UsedRange.Rows.Count
numero = 2 'Numéro de départ (correspond ici au n° de ligne)
sysdate = Date 'sysdate egal date du jour
Do While numero <= NbLig 'TANT QUE la variable numero est <= Nblig, la boucle est répétée
datetestee = Cells(numero, 13) 'datetestée egale date envoi initial
nbr_jr = sysdate - datetestee
If nbr_jr > 30 And Cells(numero, 11).Value = "oui" And Cells(numero, 14).Value <> "oui" Then
'condition vérifier si mail déjà envoyé depuis plus de 30 jours et pas de réponse
maildest = Cells(numero, 10).Value
'création corps de texte du mail
strbody = "<font size=""3"" face=""Calibri"">" & _
"Bonjour " & Cells(numero, 19).Value & "," & "<br><br>" & _
"Dans le cadre de ........, nous vous serions reconnaissants de nous transmettre en réponse vos avis sur ces dossiers. <br><br> " & _
Cells(numero, 2).Value & _
"<br><br> " & _
"" & Cells(numero, 6).Value & "" _
& "</A>" & "<br><br>Cordialement," & _
"<br><br>Titwo</font>"
Sujetmail = "Votre avis "
'création mail
With OutMail
.To = maildest
.CC = ""
.BCC = ""
.Subject = Sujetmail
.HTMLBody = strbody
.Send
End With
On Error GoTo 0
Cells(numero, 11).Value = "relance" 'mettre dans tableau que la relance a été effectuée
Cells(numero, 13).Value = Cells(numero, 13).Value & " relance le " & Date 'mettre date envoi mail et relance dans tableau
Set OutMail = Nothing
Set OutApp = Nothing
numero = numero + 1 'Le numéro est augmenté de 1 à chaque boucle
Else
numero = numero + 1 'Le numéro est augmenté de 1 à chaque boucle
End If
Loop
MsgBox "Les mails de relance ont bien été envoyés", vbExclamation, "Confirmation"
End Sub
Bonjour Titwo,
A première vue, ton code semble correct.
Cependant, je m'interroge sur le calcul de la borne inférieure :
NbLig = Range("A1").End(xlDown).Row - 1Je te suggère le remplacer par :
Dim oSheet as Worksheet Set oSheet = Thisworkbook.Worksheets("NomFeuille") NbLig = oSheet.Usedrange.Rows.CountBien entendu en remplaçant "NomFeuille" par le nom de ta feuille.
Salut
1. Il serait plus facile de travailler sur des ranges nommés, plutôt que sur des cells numéroté
set nomDestinaire = range(....)
set msgenvoye = range(...)
set msgnonrepondu=range(...)
et pour peu que tu aies nommé les range par des nom, tu les appelles facilement
2. ta boucle, plutôt que d'utiliser un nbLig pourrait être faite ac un while(cellule non vide)
3. pour la question qui est tienne, pourquoi mets tu
Set OutMail = Nothing
Set OutApp = Nothing
au coeur de ta boucle ? Tu détruis l'object ?
Ne faut il pas le mettre bien plus loin dans la boucle ?
Amicalement
Titwo,
Je vois plusieurs pbs dans ton code :
-Effectivement comme l'a pointé Jeb, tu détruis les objets "outmail" et "outapp" dans la boucle, ce qui t'interdis de les réutiliser dans le occurrences suivantes.
-Tu renseignes la colonne "date de relance" avec du texte et dates :
Cells(numero, 13).Value = Cells(numero, 13).Value & " relance le " & Date 'mettre date envoi mail et relance dans tableauce qui t'interdis de réutiliser cette cellule comme une cellule contenant une date dans l'ordre :
datetestee = Cells(numero, 13) 'datetestée egale date envoi initialPeux-tu déjà corriger ça et on peut poursuivre si ça ne règle pas tes problèmes
Merci à tous pour ces pistes.
J'ai suivi vos remarques et corrigé en fonction. Je reporterai la date de relance dans une autre colonne.
J'ai mis la destruction de mes objets après la boucle.
J'ai cependant toujours le même soucis. Le mail de la première ligne de mon tableau part bien mais pas celui de la seconde.
En mode débugage, je vois que la seconde ligne est bien prise en compte, je retrouve les valeurs de mon tableau dans mon corps de mail, etc...
J'ai la ligne
.To = mailchir
qui s'affiche en jaune
Si vous avez d'autres idées?
Amicalement.
Titwo,
Essais de placer l'ordre
Set OutMail = OutApp.CreateItem(0)juste avant le :
With OutMailmets un espion ou un debug sur ton mailchir (pourquoi n'est ce plus destmail?)
Merci beaucoup à vous tous, Gérard et Jeb,
Vos pistes m'ont permis de réfléchir aux endroits ou je plaçais mes variables, de faire murir ma méthodologie de codage et de remarquer mes étourderies. j'ai finalement décidé de séparer à nouveau la procédure "boucle" de celle de "création du mail". Cela fonctionne parfaitement. J'ai rajouté en plus un compteur du nombre de mails envoyés (affiché sur la messagebox de fin de boucle) pour pouvoir contrôler le fonctionnement, testé tous les cas de figures pour m'assurer que mes conditions sont bien prises en compte et ça fonctionne. Merci encore pour vos lumières, ça permet vraiment de se sentir moins seul devant son éditeur VBA.
Bonnes fêtes de fin d'année à vous.