Aide sur Macro

**3688marie** · 06/04/2011, 15h56

Bonjour à tous,
Je suis nouvelle sur le site et je ne sais pas très bien comment m’y prendre ! Aussi je souhaiterais que vous puissiez m’aider.
Je suis ouverte bien sûr à toute critique et à tout commentaire ne correspondant pas à ce forum, ou tout simplement à ma demande.
Je connais Excel et les tableaux croisés dynamiques, mais je suis complètement novice en Macro ou langage VBA.
Depuis quelque temps, j’essaie de faire une macro qui me permettrait de gérer les centres de formations pour lesquels je travaille et d’envoyer automatiquement un mail à la personne responsable de ce centre pour un rappel trois mois avant la date d’échéance du retour de prêt.
Je parcours votre site, je lis beaucoup d’information, mais je n’arrive pas à le mettre en application.
Je suis tout à fait prête à m’investir, mais j’aurais besoin d’une aide.
Je travaille sous Windows 7, Excel 2010 et Microsoft Office Outlook 2007.
Dans mon tableau Excel qui comprend sept colonnes et une cinquantaine de lignes, j’ai insérer deux formules :
=MOIS.DECALER(D2;-3) en colonne D et
=E2<=AUJOURDHUI() en colonne F
Mon Email dont l’objet est « Info. Return loan order » devrait se composer de la façon suivant :
Hello,
For your information, loan no. 41193855 EGES - Valencia, will end on 20/04/2011.
Thanks a lot for your prompt action.
Kind regards,
Marie
Je suis vraiment désolée, mais je travaille sur l’Europe et je dois envoyer ce mail en anglais. Mais je peux vous le communiquer en français car dans une macro, je sais corriger le texte (ce n’est pas grand-chose me direz-vous…).
Je vous joins une partie de mon tableau et vous remercie de votre compréhension,
Cordialement,
3688marie

**casefayere** · 06/04/2011, 16h30

Bonjour Marie, re le forum,

comment veux-tu envoyer le mail ? :
- automatiquement, suivant la date en D (si le fichier est ouvert) ?
- en appuyant sur un bouton de commande ?
- en cliquant sur l'adresse mail du destinataire ?
- Pour avancer quel est ton serveur SMTP ? (EX : smtp.free.fr, smpt.orange.fr...)
ensuite par souci de discrétion, j'introduirai deux inputbox qui demanderont de confirmer, un l'expéditeur, l'autre le destinataire.
tu pourras les supprimer par la suite quand tu auras compris,
dernière question enverras-tu toujours par l'intermédiaire d'outlook ?

**3688marie** · 06/04/2011, 17h03

Bonjour Dom,
Merci beaucoup pour cette réponse rapide.
Je souhaiterai envoyer le mail automatiquement suivant la date en D : Est-il obligatoire que le fichier Excel soit ouvert ?
Mon serveur SMTP est numericable
Effectivement, je ne l’ai pas précisé dans ma demande, mais je souhaiterais deux inputbox.
J’enverrai toujours par l’intermédiaire d’Outlook, même si je transfert ce dossier sur mon ordinateur professionnel, je travaille à la maison !
Encore merci pour ton aide,
Bonne journée,
Marie

**casefayere** · 06/04/2011, 17h17

Je travaille dessus mais j'ai oublié de demander s'il y aura une pièce jointe avec le message.
Je ne connais pas le serveur "smtp" de numéricable" est-ce "SMTP.numericable.fr" ?

**3688marie** · 06/04/2011, 17h28

Dom,
Il n'y aura pas de pièces jointes à ce message, les personnes qui traitent les dossiers ont juste besoin d'un rappel avec le texte que j'ai fourni.
Effectivement le serveur "SMTP" de numéricable" est "smtp.numericable" et le serveur entrant est "pop.numericable.fr"
Bon courage à toi,
Marie

**casefayere** · 06/04/2011, 17h58

Une première approche, voir fichier joint,
à l'ouverture du classeur (je ne sais pas faire avec classeur fermé)
la macro se lance, tu pourras temporairement te mettre en destinataire pour controler, après on modifiera si tu veux.
Attention, pour cette macro, dans l'éditeur VBA penses à selectionner la reference à Outlook, pour moi c'est "Microsoft Outlook 12.0 Object Library"
Ne tiens pas compte, pour l'instant de tout ce qui est en vert

**3688marie** · 06/04/2011, 18h06

Merci Dom,
Je teste et reviens vers toi pour te tenir au courant.
Je me regarde aussi pour voir s'il n'est pas possible d'envoyer le rappel automatiquement sans ouvrir Excel.
Si je trouve je ne manquerai pas de te donner la solution.
Bonne Après midi,
Marie

**casefayere** · 06/04/2011, 18h08

Et si ça fonctionne, on adaptera pour envoyer à tous les detinataires concernés en une seule fois

**3688marie** · 06/04/2011, 18h16

Dom,
Merci pour l'instant cela fonctionne, je vais modifier des dates pour voir demain si l'envoi se fait dans la journée!
Désolée, mais serait-il possible d'avoir un accusé de réception me confirmant l'envoi de ce mail et la lecture de celui-ci ?
Très bonne soirée à toi,
Marie

**casefayere** · 06/04/2011, 18h27

Désolée, mais serait-il possible d'avoir un accusé de réception me confirmant l'envoi de ce mail et la lecture de celui-ci ?

il te suffira de regarder dans tes éléments envoyés dans Outlook,
sinon j'aurai une autre solution mais.......demain, là je rentre, et serai sur mon PC perso ce soir, après 18 h30

**3688marie** · 07/04/2011, 10h14

Bonjour,
Hier soir j'ai modifié mon tableau en mettant la date d'aujourd'hui dans la colonne D, quand je l'ouvre la colonne F m'indique "Vrai", mais aucun mail ne part.
Je ne comprends pas pourquoi!
Merci beaucoup pour votre aide,
Marie

**casefayere** · 07/04/2011, 10h21

Bonjour Marie, re le forum,
je n'ai pas fait attention à cette colonne, change cette ligne de code :

Code :

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

If cdate(cel.Offset(0, 1).Value)) < CDate(Date - 91) Then

pour

Code :

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

If cel.Offset(0, 2).Value ="VRAI" Then

et dis-moi

Bonne journée

**3688marie** · 07/04/2011, 10h57

Bonjour Dom,
J'ai modifié la ligne que tu m'as indiqué, malheureusement plus rien ne marche.
Je te joints le tableau afin que tu puisse si tu as le temps regarder.
Merci pour ton aide et très bonne journée,
Marie

**casefayere** · 07/04/2011, 11h11

JE NE FAIS QUE DES CONN....

change la ligne

Code :

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

If cel.Offset(0, 2) = VRAI Then

sans guillemets

**3688marie** · 07/04/2011, 12h20

Je crois que c'est moi qui fais des CON...J'ai fais les modifs comme tu me l'as dis.
Plusieurs choses se passent :

La Date -3 Months est au : 31/05/2011
Voulez vous envoyer la relance?
Je fais Oui pour voir et je reçois le mail suivant 5 fois :
"Hello,
For your information, loan no. 41193855 EGES - Valencia, will end on 20/04/2011.
Thanks a lot for your prompt action.
Kind regards,
Marie "

En fait le message dans Excel correspond à la ligne 6 et la colonne F indique Faux, normale car la date d'échéance est au 31/08/2011 en colonne D.

Le mail que je reçois correspond à la ligne 2 pour le Centre et le numéro de loan, mais la date est fausse car elle devrait être 30/03/2011.

Je ne vois vraiment pas ce qui se passe, car hier tout fonctionnait.
Merci pour ton aide,
Marie

**3688marie** · 07/04/2011, 12h38

Dom,
Je ne suis absolument pas pressée, j'ai aussi d'autres dossiers en cours et je crois effectivement qu'à un moment ou un autre nos neurones doivent se reposer.
Je vais aussi regarder ceci tranquillement et te tiendrais informer.
Encore merci pour ton aide,
Marie

Bonsoir Dom,
En fait, je me demandais si je ne devais pas supprimer les colonnes E et F des mon tableau et demander à la macro de calculer les "-3 mois" automatiquement puis d'envoyer un mail à la personne concernée?
DateRef=DateAdd("m",-3,Date) ou qq chose comme ça car je ne suis que novice.
Merci et bonne soirée.
Marie

**casefayere** · 07/04/2011, 21h01

Voila,
Pour l'instant, je suis resté sur la notion de "Vrai" de la colonne F et rassemblé les personnes à relancer en un seul mail, ça devrait fonctionner, par la suite, si tu veux, on enlevera les messages superflus. Voici le fichier

**casefayere** · 07/04/2011, 21h36

Re Marie,
ci-dessous un code qui ne prend que la référence à la colonne D,
je laisse toujours avec "'", les éventuels changements que l'on pourra faire, exemple si tu veux recevoir une copie du mail ou si Outlook a un problème pouvoir envoyer le mail en direct (j'ai un autre code pour ça)

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
Private Sub Workbook_Open()
Dim ol As New Outlook.Application
Dim olmail As MailItem, plage As Range, laDate As Date
Dim admail As String, cel As Range, j As Long, derlg As Long, nom As String
Dim messmail As String, secours As String, mess As String
With Sheets("feuil1")
   derlg = .Range("A" & .Rows.Count).End(xlUp).Row
   Set plage = .Range("D2:D" & derlg)
   For Each cel In plage
      laDate = DateSerial(Year(cel), Month(cel) - 3, Day(cel))
      If laDate <= Date Then
         If admail = "" Then
            admail = cel.Offset(0, 3).Value
            nom = cel.Offset(0, -2).Value
         Else
            admail = admail & ";" & cel.Offset(0, 3).Value
            nom = nom & Chr(13) & cel.Offset(0, -2).Value
         End If
      End If
   Next cel
   If admail <> "" Then
      mess = MsgBox("les personnes suivantes : " & Chr(13) & Chr(13) & nom & Chr(13) & Chr(13) & " ne sont pas à jour " & Chr(13) & Chr(13) _
         & "Voulez-vous envoyer la relance?", vbOKCancel)
      If mess = 1 Then
         admail = InputBox("destinataire", , admail)
         'For j = 1 To 2
         On Error Resume Next
         Shell """C:\Program Files (x86)\Microsoft Office\Office12\OUTLOOK.EXE""" 'ou
         'Shell """C:\Program Files\Microsoft Office\Office12\OUTLOOK.EXE""" 'a vérifier
         Set ol = New Outlook.Application
         Set olmail = ol.CreateItem(olMailItem)
         messmail = "Hello," & Chr(10) & Chr(10) & "For your information, loan no. 41193855 EGES - Valencia, will end on 20/04/2011." _
            & Chr(10) & Chr(10) & "Thanks a lot for your prompt action." & Chr(10) & Chr(10) & "Kind regards," _
            & Chr(10) & Chr(10) & Chr(10) & "Marie"
         'If Err Then
         'secours = MsgBox("Problème avec le serveur de messagerie, Envoyer en direct ?", vbOKCancel)
         'If secours = 1 Then
         'Call mail_direct: Exit Sub
         'Else
         'Exit Sub
         'End If
         'Else
         With olmail
            .To = admail
            .Subject = "Info. Return loan order" 'Sujet
            .Body = messmail 'Corps du mail
            .Send '.Display     'On peut switcher entre .send et .display selon que l'on veut envoyer le mail (send) ou seulement le préparer et le vérifier(display)
         End With
         On Error GoTo 0
         'End If
         'Next j
      End If
   Else
   End If
End With
End Sub

attention à cette ligne, à vérifier

Code :

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

Shell """C:\Program Files (x86)\Microsoft Office\Office12\OUTLOOK.EXE"""

il faut regarder ou est "OUTLOOK.EXE"

possible ici

Code :

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

Shell """C:\Program Files\Microsoft Office\Office12\OUTLOOK.EXE"""

bonne nuit

**3688marie** · 08/04/2011, 10h51

Dom,
C'est vraiment moi qui est fait une CON... et ceci dans mon premier post.
C'est pour cela que je reçois toujours le même mail
J'avais écris :
"Dans mon tableau Excel qui comprend sept colonnes et une cinquantaine de lignes, j’ai insérer deux formules :
=MOIS.DECALER(D2;-3) en colonne D et
=E2<=AUJOURDHUI() en colonne F
Mon Email dont l’objet est « Info. Return loan order » devrait se composer de la façon suivant :
Hello,
For your information, loan no. 41193855 EGES - Valencia, will end on 20/04/2011.
Thanks a lot for your prompt action.
Kind regards,
Marie"

Seulement, il fallait que j'écrive :
Dans mon tableau Excel qui comprend sept colonnes et une cinquantaine de lignes, j’ai insérer deux formules :
=MOIS.DECALER(D2;-3) en colonne D et
=E2<=AUJOURDHUI() en colonne F
Mon Email dont l’objet est « Info. Return loan order » devrait se composer de la façon suivant :
Hello,
For your information, loan no. "Colonne C" "Colonne B", will end on "Colonne D.
"Thanks a lot for your prompt action.
Kind regards,
Marie

Vraiment désolée de t'infliger tout ça, je vérifie le reste.
Bonne journée,
Marie

Bonsoir,
J'ai repris le premier tableau qui fonctionnait le mieux et j'ai modifié au niveau de :
messmail="Hello,"....
Seulement maintenant j'ai une erreur de syntaxe que je n'arrive pas à corriger.
Pourrais-tu m'aider stp.
Je te joins le dossier.
Bon WE,
Marie

**casefayere** · 09/04/2011, 23h55

Bonsoir Marie, le forum,
tu n'as pas dû suivre ce que je t'avais préparé car déjà, pourquoi cette boucle qui ne sert à rien (pour le moment) :

Code :

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

1
2
For j = 1 To 2 'la, tu envoies deux fois au meme destinataire sans rien changer
........ Next j

maintenant :
1- c'est quoi ta variable "lig" ?
2- cette instruction est incomplete et inutile

Code :

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

Format(Cells(lig,2)

, si c'est la colonne B, il suffit de

Code :

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

Cells(lig,2)

et ça c''est n'importe quoi :

Code :

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

Celles(Lig,4).Value

Ps : je viens de revoir cette ligne de code, je donne en dessous du code les adresses des cellules :

Code :

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

1
2
3
messmail = "Hello," & Chr(10) & Chr(10) & "For your information, loan no. " & cel.Offset(0, -1).Value_ & cel.Offset(0, -2) & "will end on" & cel.Offset(0, 1).Value _
 & Chr(10) & Chr(10) & "Thanks a lot for your prompt action." & Chr(10) & Chr(10) & "Kind regards," _
  & Chr(10) & Chr(10) & Chr(10) & "Marie"