Creation feuilles multiples a partir d'une generale et envoi

Le 28/10/2011 à 08:35

Bonjour a tous,

J'ai besoin d'aide car je voudrais creer une procedure (et suis blond) mais je ne sais pas comment/quoi faire :

Le contexte:

Chaque mois je cree a partir d'un soft CRM un rapport qui contient des milliers d'opportunites de vente. Chaque vendeur de la societe a bien sur plusieurs dizaines d'opportunites qui lui sont assignees.

Je voudrais donc, a partir de cette feuille de depart, creer autant de feuille ou d'onglet qu'il y a de vendeurs et ne voir sur une feuille, que les opportunites de ce vendeur.

Ensuite, l'idee est d'automatiser l'envoi de la feuille d'un vendeur a celui-ci par email... Le tout en un minimum d'operations !

Vous me sauveriez la vie si cela etait possible.

Merci d'avance a tous...

Olivier

Le 28/10/2011 à 10:03

Bonjour

Vous me sauveriez la vie si cela etait possible

C'est possible et nous sommes heureux de t'avoir sauvé la vie!

Plus sérieusement, éclater une feuille sur plusieurs est faisable par VBA, mais il faudrait peut-être connaitre la structure de ton fichier pour un diagnostic plus précis et surtout, une réponse adaptée.

Cordialement

Le 01/11/2011 à 10:07

Amadeus, tous,

Merci déjà de bien vouloir essayer de m'aider...!

Qu'aurais tu besoin de savoir plus précisement a propos du fichier ??

C'est un fichier assez confidentiel donc tu comprendras que je ne peux pas le divulguer comme ca sur le net ( et meme hors de la societe d'ailleurs)...

Donc je serais heureux de te donner toutes les infos necessaires pour t'aider, mais dis moi exactement ce qu'il faut !

Merci encore de votre aide !

Olivier

Le 01/11/2011 à 14:46

Bonjour,

On a besoin de la structure des données, c'est a dire de savoir comment est présentée ta feuille et sur quelle colonne on doit faire l'éclatement par vendeur. Tu mets 20 lignes de données bidons si tu veux, mais la colonne VENDEUR doit contenir plusieurs vendeurs pour que le test fonctionne.

L'autre problème est de savoir si les vendeurs sont classés, ou si l'on doit lire toutes les lignes pour générer le classeur d'un vendeur, ou s'il est autorisé de trier la colonne vendeur pour que le code s'exécute plus vite.

Concernant l'envoi par mail, on verra cela plus tard, chaque chose en son temps. Le plus simple étant activeworkbook.sendmail, mais dans ce cas il est impossible de mettre un texte dans le corps du message.

A+

Benead

Le 01/11/2011 à 14:47

Bonjour

Ce type de sujet qui consiste à dispatcher les données d'une feuille sur plusieurs autres a été traité plusieurs fois sur le Forum, en VBA.

Si tu ne peux joindre ton fichier, ce sera difficile.

Tape "dispatcher" dans Rechercher (en haut à droite) et tu verras les différents sujets traités. Mieux, enlèves les données confidentielles et envoie le fichier "anonymisé"

Cordialement

Edit: Désolé Benead, pas vu

Le 02/11/2011 à 10:41

bonjour à tous,

Voila j'ai préparé un petity fichier de test avec la meme structure que l'original...

La finalit" de l'exercice étant de créer une feuille (ou meme un autre classeur) par nom de vendeur y incluant le l'extrait du tableau d'origine lui correspondant.

Ensuite de trouver une fonction pour envoyer par email, cette feuille au vendeur en question (l'idela serait d'avoir une fonction qui envoie tous les emails d'un coup à tous les vendeurs plutot que de devoir aller sur chaque feuille et cliquer un bouton ou je ne sais quoi...

VOila j'espère que mes explications sont claires assez ? ... et surtout que vous saurez m'aider... n'oubliez pas, je suis blond : ) donc il faut tout bien expliquer !!!

merci d'avance

Olivier

70opps-sample-4-automation.xlsx (12.15 Ko)
Le 02/11/2011 à 16:57

Bonjour

Messieurs (ou dames) experts (tes) VBA, c'est à vous de jouer car là, je suis incompétant.

A tout hasard, tu peux regarder ce sujet en cours de traitement

https://forum.excel-pratique.com/excel/exporter-des-donnees-vers-plusieurs-feuilles-de-calcul-t26446.html

Cordialement

Le 03/11/2011 à 01:08

Bonsoir,

Essaie le code suivant à mettre dans le classeur d'origine qu'il faut enregistrer en xlsm :

Option Explicit

DefLng L
DefStr S

Sub EclatVendeur()
   Dim TabVendeur As New Collection, Sh As Worksheet, ShVendeur As Worksheet
   Dim lLig, lItem, vVendeur As Variant, WbkVendeur As Workbook

   Set Sh = ThisWorkbook.Worksheets(1)

   ' Création d'une table des Vendeurs (sans doublon)
   On Error Resume Next
   For lLig = 4 To Sh.UsedRange.Rows.Count
      If Not Sh.Cells(lLig, "E").Text = Empty Then _
         TabVendeur.Add Sh.Cells(lLig, "E").Text, Sh.Cells(lLig, "E").Text
   Next lLig
   On Error GoTo 0

   ' Génération des classeurs par vendeur (colonne E)
   For Each vVendeur In TabVendeur
      Set WbkVendeur = Workbooks.Add
      Set ShVendeur = WbkVendeur.Worksheets(1)
      Sh.Columns.Copy
      ShVendeur.[A1].PasteSpecial xlPasteColumnWidths
      Sh.Range("1:3").Copy ShVendeur.[A1]
      ShVendeur.Name = vVendeur
      For lLig = 4 To Sh.UsedRange.Rows.Count
         If Sh.Cells(lLig, "E").Text = vVendeur Then
            Sh.Rows(lLig).Copy ShVendeur.Rows(ShVendeur.UsedRange.Rows.Count + 1)
         End If
      Next lLig
      Application.DisplayAlerts = False
      WbkVendeur.SaveAs "D:\Test\" & vVendeur, xlOpenXMLWorkbook ' Sauvegarde
      Application.DisplayAlerts = True
'      WbkVendeur.SendMail Sh.Cells(lLig, "V").Text, "Full Pipeline Analysis", True ' Envoi du mail (ColonneV)
      WbkVendeur.Close False
   Next vVendeur
End Sub

J'ai mis l'envoi du mail en commentaire, il faut enlever l'apostrophe pour que le mail parte.

Tu ne m'as pas répondu concernant l'organisation des données, donc le code n'est pas optimisé.

J'ai considéré que l'adresse de messagerie des vendeurs se trouve en colonne V.

Je sauvegarde les fichiers en "D:\Test\" avant d'envoyer les fichiers, cela fait plus sérieux de transmettre des classeurs nommés. Il faut que tu changes le chemin du dossier en fonction de ton environnement.

.Enfin, il faut que tu adaptes le code en fonction de tes besoins.

Je joint le fichier au cas ou.

A+

Benead

66opps-sample-4-automation.xlsm (19.60 Ko)
Le 08/11/2011 à 11:15

Benead,

...tu es un Dieu ou quoi ???

Un grand merci car ça marche ! en tous cas en partie... j'ai remplacé le chemin d'accès, et enlevé l'apostrophe et remplacer les adresses mail par des adresses valides.

La macro crée bien différents classeurs sauvegardés à l'endroit spécifié - ça c'est parfait !

Par contre, aucun mail ne sort...? aussi, j'aimerais faire un message type dans lequel serait attaché le classeur concerné - et que chaque vendeur ne reçoive qu'1 mail avec le classeur qui lui est propre en attache..., est-ce possible ?

Merci encore tout plein d'avance... A +

Olivier

PS: désolé pour cette réponse tardive...qq jours de congés !

Le 09/11/2011 à 08:38

Bonjour,

Comment cela aucun mail ne sort ?

As-tu enlever l'apostrophe sur cette ligne ?

' WbkVendeur.SendMail Sh.Cells(lLig, "V").Text, "Full Pipeline Analysis", True ' Envoi du mail (ColonneV)

Pour ce qui est de mettre du texte dans le corps du mail, je sais le faire avec Outlook. Est-ce ton logiciel de messagerie ? Sinon il faut faire une recherche sur le forum, je crois avoir déjà vu passer un fil sur le sujet.

A+

Benead

Le 09/11/2011 à 11:32

Salut,

Oups ! j'avais enlevé la mauvaise apostrophe !!! voilà qui est réglé !

mais... lorsque je lance la macro, le système crée la première feuille qui correspond au vendeur 1 et ouvre un mail avec le destinataire vide et attend que je fasse une action sur ce mail avant de passer au vendeur 2, créer sa feuille puis ouvrir le message sans destinataire, et attendre action, etc... et comme il y en a plusieurs centaines... ; )

Serait il possible que :

  • les destinataires soient remplis automatiquement à partir des données de la colonne V
  • que les mails partent "tout seul" (ou avec une autre commande, ou ???)

et oui j'utilise outlook comme logiciel de messagerie pour faire un corps de message type...

Merci encore de ton aide !

Olivier

Le 09/11/2011 à 22:14

Bonsoir,

Je n'avais pas tester l'envoi. remplace tout le code par celui-ci :

Option Explicit

DefLng L
DefStr S

Sub EclatVendeur()
   Dim TabVendeur As New Collection, Sh As Worksheet, ShVendeur As Worksheet
   Dim lLig, lItem, vVendeur As Variant, WbkVendeur As Workbook, sDestinataire

   Set Sh = ThisWorkbook.Worksheets(1)

   ' Création d'une table des Vendeurs (sans doublon)
   On Error Resume Next
   For lLig = 4 To Sh.UsedRange.Rows.Count
      If Not Sh.Cells(lLig, "E").Text = Empty Then _
         TabVendeur.Add Sh.Cells(lLig, "E").Text, Sh.Cells(lLig, "E").Text
   Next lLig
   On Error GoTo 0

   ' Génération des classeurs par vendeur (colonne E)
   For Each vVendeur In TabVendeur
      sDestinataire = Empty
      Set WbkVendeur = Workbooks.Add
      Set ShVendeur = WbkVendeur.Worksheets(1)
      Sh.Columns.Copy
      ShVendeur.[A1].PasteSpecial xlPasteColumnWidths
      Sh.Range("1:3").Copy ShVendeur.[A1]
      ShVendeur.Name = vVendeur
      For lLig = 4 To Sh.UsedRange.Rows.Count
         If Sh.Cells(lLig, "E").Text = vVendeur Then
            If sDestinataire = Empty Then sDestinataire = Sh.Cells(lLig, "V").Text
            Sh.Rows(lLig).Copy ShVendeur.Rows(ShVendeur.UsedRange.Rows.Count + 1)
         End If
      Next lLig
      Application.DisplayAlerts = False
      WbkVendeur.SaveAs "D:\Test\" & vVendeur, xlOpenXMLWorkbook ' Sauvegarde
      Application.DisplayAlerts = True
      MsgBox "le classeur sera envoyé à : " & sDestinataire
      WbkVendeur.SendMail sDestinataire, "Full Pipeline Analysis", True ' Envoi du mail (ColonneV)
      WbkVendeur.Close False
   Next vVendeur
End Sub

Cela devrait mieux fonctionner.

Avec Outlook tu auras un message à chaque envoi de mail. Pour ne plus avoir à cliquer à chaque mail et outre-passer les quelques secondes d'affichage de la sécurité d'Outlook, il faut installer un petit utilitaire nommé ClicYes.exe ici :

http://www.contextmagic.com/express-clickyes/free-version.htm

Pour renseigner le corps du message on verra après ce nouveau test.

A+

Benead

Le 10/11/2011 à 09:36

Bonjour,

J'ai remplacé le code et l'ai un peu adapté à mon fichier final (car entretemps il y a eu qq modif dans les colonnes) mais bref, ça marche !!!

J'ai installé "clickyes" ... et ca marche aussi !!! Merveilleux !

Dernières choses que je voudrais faire :

  • Est-il possible de ne PAS avoir cette boite de dialogue qui dit "un message va être envoyé à..." et devoir cliquer sur OK ?
  • D'avoir un ou des destinataire(s) en copie - tjrs le(s) même(s)
  • savoir où insérer le corps du message qui sera envoyé

Voilà, après tout ça, je pense que ce sera prêt ! mais c'est déjà super comme ça !

encore mille merci ! trop cool ! suis content...

Olivier

Le 11/11/2011 à 22:20

Bonsoir,

- Est-il possible de ne PAS avoir cette boite de dialogue qui dit "un message va être envoyé à..." et devoir cliquer sur OK ?

C'est justement le travail de ClicYes : s'il est actif, tu ne devrais pas avoir à répondre à ce message.

- D'avoir un ou des destinataire(s) en copie - tjrs le(s) même(s)

- savoir où insérer le corps du message qui sera envoyé

Adapte ce code qui répond à ton besoin :

Option Explicit

'Elle utilise la technologie Automation (OLE) et nécessite donc d'ajouter la référence "Microsoft Outlook 11.0 Object Library" au projet VBA (Menu Outils-Réferences).

Sub Test()
   Dim sCorps As String, sDest As String, sObj As String, sCpy As String, sFichier As String
   sDest = "toto@titi.fr;fifi@perser.fr"
   sCpy = "ramses@titi.fr;loulou@perser.fr"
   sObj = "Mon premier test Outlook"
   sCorps = "Bonjour," & vbCr & vbCr & "Veuillez trouver ci-joint un chèque de 5€ en remboursement du trop perçu." & vbCr & vbCr & "Cordialement," & vbCr & "Olivier"
   sFichier = "D:\Test\MonClasseur.xlsx" ' Le classeur doit être fermé
   Call EnvoiMailOLE(sDest, sObj, sCorps, sFichier, sCpy)
End Sub

Sub EnvoiMailOLE(Adresse As Variant, Objet As String, Corps As String, Optional Pièce As String, Optional Cc As String, Optional Bcc As String)
    Dim MonAppliOutlook As New Outlook.Application
    Dim MonMail As Outlook.MailItem
    Dim MaPièce As Outlook.Attachments
    Set MonMail = MonAppliOutlook.CreateItem(olMailItem)
    With MonMail
        .To = Adresse
        If Not IsNull(Cc) Then .Cc = Cc
        If Not IsNull(Bcc) Then .Bcc = Bcc
        .Subject = Objet
        .Body = Corps
        If Not Pièce = "" Then
            Set MaPièce = .Attachments
            MaPièce.Add Pièce, olByValue
        End If
        .Send
    End With
End Sub

Ce code ne fonctionne qu'avec Outlook et il faut référencer "Microsoft Outlook 1x.0 Object Library". Tu dois sauvegarder le classeur que tu veux envoyer et le fermer avant de le transmettre sinon cela buggue.

A+

Benead

Le 14/11/2011 à 11:31

Bonjour,

et merci de ta réponse...

Alors, pour "clickyes", ce qu'il fait c'est "accepter" la pop up outlook de sécurité, donc je ne dois plus cliquer a chaque fois sur "allow", mais par contre, juste avant ce pop up, il y a un message qui dit "le classeur sera envoyé à : xxxx@zzz.com" et c'est ce message que je voudrait enlever (pas besoin) !

ensuite pour le code qui correspond a l'envoi de mail avec destinataire et corps de message, où dois je copier/insérer ce code dans le code que tu m'as déja fourni ?

Je te remets en copie le code original que tu as fait et que j'ai un peu adapté à mes besoins, donc si tu pouvais me dire ou mettre le reste du code pour l'email stp ?

je joins aussi le fichier en format final qui correspond au code ci-dessous car des colonnes ont été modifiées...

Option Explicit

DefLng L

DefStr S

Sub EclatVendeur()

Dim TabVendeur As New Collection, Sh As Worksheet, ShVendeur As Worksheet

Dim lLig, lItem, vVendeur As Variant, WbkVendeur As Workbook, sDestinataire

Set Sh = ThisWorkbook.Worksheets(1)

' Création d'une table des Vendeurs (sans doublon)

On Error Resume Next

For lLig = 4 To Sh.UsedRange.Rows.Count

If Not Sh.Cells(lLig, "E").Text = Empty Then _

TabVendeur.Add Sh.Cells(lLig, "E").Text, Sh.Cells(lLig, "E").Text

Next lLig

On Error GoTo 0

' Génération des classeurs par vendeur (colonne E)

For Each vVendeur In TabVendeur

sDestinataire = Empty

Set WbkVendeur = Workbooks.Add

Set ShVendeur = WbkVendeur.Worksheets(1)

Sh.Columns.Copy

ShVendeur.[A1].PasteSpecial xlPasteColumnWidths

Sh.Range("1:3").Copy ShVendeur.[A1]

ShVendeur.Name = vVendeur

For lLig = 4 To Sh.UsedRange.Rows.Count

If Sh.Cells(lLig, "E").Text = vVendeur Then

If sDestinataire = Empty Then sDestinataire = Sh.Cells(lLig, "M").Text

Sh.Rows(lLig).Copy ShVendeur.Rows(ShVendeur.UsedRange.Rows.Count + 1)

End If

Next lLig

Application.DisplayAlerts = False

WbkVendeur.SaveAs "C:\Documents and Settings\10089645\Desktop\Sales OPS 2011\TEST1\" & vVendeur, xlOpenXMLWorkbook ' Sauvegarde

Application.DisplayAlerts = True

MsgBox "le classeur sera envoyé à : " & sDestinataire

WbkVendeur.SendMail sDestinataire, "OOD Close Date & Probabilities : URGENT update required !", True ' Envoi du mail (ColonneM)

WbkVendeur.Close False

Next vVendeur

End Sub

merci d'avance encore une fois !

Olivier

22test-3-overdue.xlsm (18.61 Ko)
Le 15/11/2011 à 23:26

Bonjour,

Pour le message, il fallait juste supprimer la ligne :

MsgBox "le classeur sera envoyé à : " & sDestinataire

Il ne faut pas mettre ton code dans le module de la feuille, mais dans un module standard. Avec l'envoi via Outlook et le fichier en pièce jointe, cela donne cela

Option Explicit

DefLng L
DefStr S

Sub EclatVendeur()
   Dim TabVendeur As New Collection, Sh As Worksheet, ShVendeur As Worksheet
   Dim lLig, lItem, vVendeur As Variant, WbkVendeur As Workbook, sDestinataire
   Dim sCorps, sObj, sCpy, sFichier

   Set Sh = ThisWorkbook.Worksheets(1)

   ' Création d'une table des Vendeurs (sans doublon)
   On Error Resume Next
   For lLig = 4 To Sh.UsedRange.Rows.Count
      If Not Sh.Cells(lLig, "E").Text = Empty Then _
         TabVendeur.Add Sh.Cells(lLig, "E").Text, Sh.Cells(lLig, "E").Text
   Next lLig
   On Error GoTo 0

   ' Génération des classeurs par vendeur (colonne E)
   For Each vVendeur In TabVendeur
      sDestinataire = Empty
      Set WbkVendeur = Workbooks.Add
      Set ShVendeur = WbkVendeur.Worksheets(1)
      Sh.Columns.Copy
      ShVendeur.[A1].PasteSpecial xlPasteColumnWidths
      Sh.Range("1:3").Copy ShVendeur.[A1]
      ShVendeur.Name = vVendeur
      For lLig = 4 To Sh.UsedRange.Rows.Count
         If Sh.Cells(lLig, "E").Text = vVendeur Then
            If sDestinataire = Empty Then sDestinataire = Sh.Cells(lLig, "M").Text
            Sh.Rows(lLig).Copy ShVendeur.Rows(ShVendeur.UsedRange.Rows.Count + 1)
         End If
      Next lLig
      Application.DisplayAlerts = False
      sFichier = "C:\Documents and Settings\10089645\Desktop\Sales OPS 2011\TEST1\" & vVendeur
      WbkVendeur.SaveAs sFichier, xlOpenXMLWorkbook   ' Sauvegarde
      Application.DisplayAlerts = True
      WbkVendeur.Close False ' Fermeture du classeur

      sCpy = "ramses@titi.fr;loulou@perser.fr"
      sObj = "OOD Close Date & Probabilities : URGENT update required !"
      sCorps = "Bonjour," & vbCr & vbCr & "Veuillez trouver ci-joint un chèque de 5€ en remboursement du trop perçu." & vbCr & vbCr & "Cordialement," & vbCr & "Olivier"
      Call EnvoiMailOLE(sDest, sObj, sCorps, sFichier)

   Next vVendeur
End Sub

Sub EnvoiMailOLE(Adresse As Variant, Objet As String, Corps As String, Optional Pièce As String, Optional Cc As String, Optional Bcc As String)
   Dim MonAppliOutlook As New Outlook.Application
   Dim MonMail As Outlook.MailItem
   Dim MaPièce As Outlook.Attachments
   Set MonMail = MonAppliOutlook.CreateItem(olMailItem)
   With MonMail
      .To = Adresse
      If Not IsNull(Cc) Then .Cc = Cc
      If Not IsNull(Bcc) Then .Bcc = Bcc
      .Subject = Objet
      .Body = Corps
      If Not Pièce = "" Then
         Set MaPièce = .Attachments
         MaPièce.Add Pièce, olByValue
      End If
      .Send
   End With
End Sub

N'oublies pas de référencer "Microsoft Outlook xx.0 Object Library" (xx est la version 11 à 14)

Bien sûr il faut que tu mettes ce que tu veux en corps de message et en copie.

A+

Benead

22test-3-overdue1.xlsm (19.07 Ko)
Le 16/11/2011 à 12:33

Salut,

bon j'avais prévenu au départ que j'étais blond... : ) donc... il faut m'expliquer tout bien comme il faut en détail !

Alors j'ai fait ce que tu m'as indiqué... mais :

1. je comprends pas trop ce que tu veux dire avec "Il ne faut pas mettre ton code dans le module de la feuille, mais dans un module standard" ? mais bon j'ai repris le code complet du fichier attaché en retour avec ta derniere reponse !

2. lorsque j'ai lancé la macro, j'ai eu une erreur qui me dit "file not found"...? je suppose que c'est en rapport avec le fichier généré qui devrait être attaché ?

Il crée donc le fichier 1, je reçois cette erreur, puis c'est stoppé, plus rien ne se passe après !

Clickyes est actif et la reference Outlook activee en VBA...

Aussi, j'ai remplacé sDest par sDestinataire dans la ligne "Call EnvoiMailOLE(sDestinataire, sObj, sCorps, sFichier)" car au début il donnait une erreur ???

Je joins le dernier fichier en date...

j'espère que je ne t'embête pas trop ! et merci d'avance. je joins le fichier pour te rendre compte par toi même si tu veux !

Olivier

PS: je ne sais pas si j'avais précisé mais c'est un excel en anglais !

17test-3-overdue.xlsm (21.76 Ko)
Le 16/11/2011 à 14:07

Re-bonjour,

petite précision donc : lorsque je lance la macro, elle crée le premier fichier correspondant au vendeur 1 pui sle message "run-time error '-2147024894 (80070002)': cannot find this file. Verify path and file name are correct." s'affiche... lorsque je clique sur "debug" , il surligne la ligne "MaPièce.Add Pièce, olByValue" en jaune...

voilà je ne sais pas vérifier plus loin si les choses se passent bien (est ce qu'il rempli le bon destinataire, la bonne adresse en Cc, le bon corps de message , etc...) puisque ça en reste là !

Merci

Olivier

Le 16/11/2011 à 22:18

Bonsoir,

1) Oui tu avais mis le code dans le module de la feuille (Feuil1). Ce module est un module de classe qui est capable de gérer les évènements qui surviennent dans la feuille, par exemple l'activation de la feuille, un changement de valeur dans une cellule, le déplacement du curseur... Les macros non événementielles ne doivent pas être dans ces modules mais dans des modules dit standard.

2) En fait c'est parce quand l'on sauvegarde le fichier, il est inutile de préciser l'extension ; par contre quand on veut joindre ce fichier, il ne le trouve pas car cette extension est manquante. Corrige la ligne suivante ;

sFichier = "C:\Documents and Settings\10089645\Desktop\Sales OPS 2011\TEST1\" & vVendeur & ".xlsx"

Cela devrait fonctionner.

A+

Benead

Le 17/11/2011 à 12:07

Cool !

Tout marche super bien ! merci tout plein (sais pas pourquoi , mais meme sans clickyes activé, ca marche ??? - bref je ne cherche pas !)

Encore un super grand merci !

...et si je peux abuser... est il possible d'insérer ma signature outlook (html) directement dans ce message ? Ou dans le code et comment exactement ?

ou au pire, qu'est ce que je dois mettre et où pour utiliser une autre police et autre taille pour ce qui serait la signature ?

Merci encore

Olivier

Rechercher des sujets similaires à "creation feuilles multiples partir generale envoi"