XL 2010 Découpage feuille Excel par commercial et envoi par mail automatique

[Eaulive]

Bonjour à tous,

je viens de découvrir les bienfaits et la puissance probable du VBA dans Excel !
Mais je suis débutant en la matière.

voici ma problématique:
Je dois m'assurer de la bonne tenue des données saisies par la force de vente dans le CRM de l'entreprise.
Pour se faire, je réalise une extraction hebdomadaire de l'ensemble des projets sous excel et j'identifie en masse les actions correctives que les commerciaux doivent réaliser pour maintenir la base de données à jour.
Je souhaite leur envoyer individuellement par mail, à partir d'outlook, un fichier ne mentionnant, au choix, que les projets à corriger ou l'ensemble de leur portefeuille avec copie leur manager.
Le fichier excel comporte environ 10000 lignes. 90 commerciaux environ sont concernés.

Après recherche sur le forum, j'ai réussi, à peu près (cela ne fonctionne pas toujours...), à réaliser les taches suivantes à partir de mon fichier de travail:
1- pouvoir générer 1 fichier individuel par collaborateur.
2 - ébauche de code pour envoi d'un fichier par mail en automatique

Pouvez vous m'aider de sorte que:
1 - vérifier que le code fait pour le découpage du fichier de travail en 1 fichier par commercial soit ok.
2 - ajouter un bout de code de sorte que je puisse faire un découpage par commercial en ne prenant que les lignes pour lesquelles la colonne "action à réaliser" est non vide. (cette partie n'est pas urgente)
3 - que chaque fichier réalisé par commercial, en automatique, soit envoyé par mail au commercial en question, copie sont manager avec un sujet au mail + un peu de texte type "Bonjour, vous trouverez ci-joint blablabla". J'ai commencé mais la routine bloque dès le premier mail
4 - que je puisse faire au préalable un test d'envoi de fichier sur mon adresse mail pour 1 ou 2 fichiers histoire de vérifier que je n'envoie pas 95 mails d'un coup faux...

je vous joint le fichier.

détail du fichier:
* onglet "test": vous trouverez dans cet onglet l'extraction de l'ensemble des projets par commerciaux avec les actions de corrections à réaliser
* onglet "modèle": le modèle pour la génération des fichiers individuel par commerciaux
* onglet "codage": les adresses mails des commerciaux + des managers liés aux agences
Les datas sont fictives pour info

J'espère que j'ai été assez claire et que vous pourrez m'aider.

N'hésitez pas à me demander des éléments complémentaires.

Cordialement
Olivier

 

[Lolote83]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Salut EauLive,
Voici en fichier joint un début de réponse.
Grace à l'utilisation d'un filtre élaboré, on parcours l'ensemble des commerciaux et on affiche le résultat dans l'onglet modèle.
Pour la partie mail, je regarde et te tiens au jus
@+ Lolote83

 

[Eaulive]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Merci Lolote83.

c'est top.
j'ai avancé de mon coté et j'arrive a peu près à la même chose, mais en moins beau.
En revanche, je génère les fichiers XLS par commerciaux.

voici le code (il est possible qu'il ne fonctionne pas dans le fichier de démo car je l'ai simplifié pour le rendre compréhensible. Cependant, je travail sur mon fichier et pas la version simplifiée)

Sub CreeClasseurs()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

[A1:U10000].AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[W1], Unique:=True
For Each c In Range("W2", Range("W65000").End(xlUp))
Range("W2") = c
Sheets("Modèle").Select
[A2:I1000].Clear
Sheets("Source").[A1:S10000].AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Source").[W1:W2], CopyToRange:=Sheets("Modèle").[A1:S1], Unique:=False
ActiveSheet.Copy
ActiveSheet.Name = c
ActiveWorkbook.SaveAs Filename:=c
ActiveWorkbook.Close
Sheets("Source").Select
Next c


End Sub

En revanche, si j'ai réussi à trouver un bout de code pour envoyer des mails, je n'arrive pas à d'une part, l'intégrer dans le process précédent, mais aussi à lui demander de joindre le bon fichier généré et mettre la bonne adresse mail destinataire + copie !

le bout de code additionnel:

'envoi mail

ActiveWorkbook.SendMail Recipients:="oxx@xxx.fr", _ 'l'adresse doit évoluer en fonction du commercial ainsi que le fichié associé + ajouter en copie une adresse qui varie également en fonction du commercial
Subject:="Extraction de votre Portefeuille", _ ' il faudrait ajouter un minimum un corps de texte standard
ReturnReceipt:=True

'fin envoi mail

 

[Lolote83]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Re salut,
Merci pour ta réponse, j'avais déjà préparé une version2 beaucoup plus élaborée, mais j'attendais un peut ton retour pour voir si je ne m'embarquais pas trop sur une fausse piste.
Du coup, en attendant le version2 (demain matin car sur un autre PC)
- Gestion des personnes
- Création d'un fichier PDF correspondant à la personne
- Envoi automatique d'un mail.

Tout ceci, soit pour tout le monde, soit pour une personne en particulier
A demain
@+ Lolote83

 

[Lolote83]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Salut EauLive,
Comme promis voici donc une version un peu plus élaborée.
Deux boutons sont présents dans l'onglet MODELE (TEST pour tous et TEST un 1)
Clic sur le bouton TEST pour tous les commerciaux lance :
- Récupération dans l’onglet MODELE le contenu correspondant au commercial
- Création d’un fichier PDF portant son nom
- Envoi par mail de ce PDF aux destinataires

Clic sur le bouton TEST pour 1 commercial lance :
- Récupération dans l’onglet MODELE le contenu correspondant au commercial
- Création d’un fichier PDF portant son nom
- Envoi par mail de ce PDF aux destinataires

Le choix du nom du commercial se fait en cellule A2 via un menu déroulant.
Les fichiers PDF générés sont automatiquement enregistrés dans le même répertoire du fichier.
En espérant que cela fonctionnera bien car le fait de joindre automatiquement le fichier PDF au mail n'est pas toujours pris en compte, compte tenu que la macro simule des appuis de touche.
Le mail n'est pas automatiquement envoyé, te permettant de voir le contenu avant de l'envoyer.
Si tu veux l'envoyer automatiquement, il faut enlever la coche sur le code suivant

    If PJ <> "" Then
        'Piece jointe
        SendKeys "^+A", True            ' CTRL + MAJ + A (Envoie les touches d'ajout d'1 pièce jointe)
        Attendre 1                      ' On temporise (à règler éventuellement)
        SendKeys PJ, True               ' A ce stade le programme Attend un nom de fichier, on lui envoie
        Attendre 1                      ' On temporise
        SendKeys "{ENTER}", True        ' et on valide ce nom de fichier
        Attendre 1
    End If
    'SendKeys "^{ENTER}", True           'Envoyer (CTRL + ENTREE)
End Sub

Donc écrire

SendKeys "^{ENTER}", True 'Envoyer (CTRL + ENTREE)

à la place de

'SendKeys "^{ENTER}", True 'Envoyer (CTRL + ENTREE)

Tiens moi au courant si cela te semble OK
@+ Lolote83

 

[Eaulive]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

De la bombe atomique Lolote83 !

Mais comment tu arrives à faire tout ça! je suis mystifié !

Bon, voici le résultat de mes premiers test en version 1 ou All commerciaux:
* le filtre est parfait
* le fichier pdf est bien généré dans le répertoire download.

en revanche:
* la mise en forme du fichier pdf se fait sur 2 pages ne permettant pas un lecture correcte. Je vais essayé de changer le layout de pdf en xls afin de laisser plus de latitude au destinataire
* la génération du mail est ok. Cependant, le Cc: apparaît dans le corps du mail et pas dans la ligne dédiée cc: . Idem pour le fichier pdf à rapatrier. Il copie le lien dans le corps de texte mais ne rapatrie pas le fichier.

Je vais essayé de bosser dessus de mon coté mais, ayant déjà de beaucoup , dépassé mon seuil de compétence sur le sujet, je vais tenté de comprendre un peu plus et du bricolage par analogie.

Super boulot dans tous les cas Lolote83

Merci
Olivier

 

[Lolote83]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Re salut,
Comment ça tu n'as pas de génération de fichier PDF.

Comme tu l'indiques, je n'ai pas de génération de fichier pdf

Normalement, les fichiers PDF sont enregistrés ou est ton fichier.
Voir copie ici pour mon cas.
Tiens moi au jus
@+ Lolote83

 

[Eaulive]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Sorry Lolote83, tu es plus rapide que moi !

Je n'ai pas eu le temps d'amender mon précédent message

Le fichier pdf est bien généré dans le repertoire download mais il n'est pas rapatrié lors de la génération du mail.

A l'ecran, je vois le passage sur outlook, la création d'un nouveau mail mais les informations "cc: /copie" et le rattachement du fichier joint s'inscrive en toutes lettres dans le corps du mail mais pas dans les sections cc: et fichier attaché.

Du coup, je te renvoi à mon précédent message pour la fin du test ...

Merci

 

[Lolote83]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Re salut,
Je travaille avec Thunderbird.
Y a t-il une combinaison de touche sur Outlook pour joindre un fichier
Dans ce cas, peut être faut-il remplacer dans le code
De plus, tu dis :

Le fichier pdf est bien généré dans le repertoire download mais il n'est pas rapatrié lors de la génération du mail.

Le fichier PDF n'est pas dans le même répertoire que ton fichier (comme dans ma copie d'écran post#7) ??
Je cherche de mon coté
@+ Lolote83

 

[Eaulive]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Hi Lolote83,

Ok pour la génération du fichier pdf dans le même repertoire que celui où se situe le fichier excel.
Donc comme dans ta copie d'écran, exception faite que de mon coté il est dans un répertoire au nom différent.

En revanche, j'étais sur ton code et, au delà du destinataire en copie qui ne se mets pas dans le bon champ et le non rapatriement du pdf, tout le texte du corps de mail est absent du résultat.

 

[Lolote83]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Re salut,
Voici donc une nouvelle version avec mail OUTLOOK.
Après plusieurs recherche, cela donne donc le fichier joint
Voir également la copie d'écran pour Outlook
@+ Lolote83

 

[Eaulive]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

AAAARRRGGGGGG !!!!!
Alors là, je dis "BRAVO"

Mais comment tu fais !!!!!

C'est parfait dans Outlook !

TOUT MARCHE !

Il y a des jours, tu es super content de bosser juste pour essayer un nouvel outil de dingue !...

juste pour chipoter, 2 trucs:
* lorsque la macro tourne pour "tous les commerciaux", est-il possible de supprimer le message de retour sous excel ?
* la génération du fichier en pdf induit une difficulté d'affichage. (1 ligne sur 2 pages). Je dois faire des choix dans les dimensions des colonnes. Du coup, est-ce difficile de générer des fichiers XLS versus PDF ?

Merci Lolote83

 

[Lolote83]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Re salut,
Effectivement, je me suis apperçu que j'avais laissé le messageBox.
Donc dans le module Mod_MailOutlook, mettre une coche devant la ligne suivante comme ci-dessous et la ligne ne sera plus prise en compte

.Attachments.Add xFichierAttaché
.Display '.Send
End With
'MsgBox "Merci de vérifier que le message apparait dans -messages envoyés- dans votre messagerie OUTLOOK."
' Effacer les variables objets
Set olMail = Nothing
Set olApp = Nothing
End Sub

Deplus, tu dis :

* la génération du fichier en pdf induit une difficulté d'affichage. (1 ligne sur 2 pages). Je dois faire des choix dans les dimensions des colonnes. Du coup, est-ce difficile de générer des fichiers XLS versus PDF ?

Pourquoi 1 ligne sur 2 pages ????
Je te joint 2 pdf générés chez moi et dis moi si de ton coté cela donne le même résultat car je ne comprends pas

(1 ligne sur 2 pages)

Celui de Sophie tient sur 1 page
Celui de Yvan tient sur 2 pages
@+ Lolote83

 

[Eaulive]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Hi,

voici le PDF généré...

enfin presque. le site ne veut pas que je le partage...trop lourd je pense.

Donc je joins des copies d'écrans.

J'ai réussi à le faire tenir sur une page en réduisant la taille et masquant des colonnes.
Cela me fait dire que dans la fonction Layout, tu dois avoir en automatique un ajustement à la feuille alors que moi je dois etre en impression pdf sans spécification concernant le document de sortie

MAJ faite pour le message. C'est Ok.

 

[Lolote83]

Re : Découpage feuille Excel par commercial et envoi par mail automatique

Re salut,
Voir message privé pour mon mail
@+ Lolote83