Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

[Calvus]

Bonsoir à tous,

Après diverses recherches et 3 boites d'aspirine, je fais appel à vos compétences.

Le projet me paraît un peu ambitieux, et complètement hors de portée me concernant.
J'ai lu diverses choses, mais je n'ai aucune idée de la faisabilité ni de l'adaptabilité.

Je vous expose mon souhait.

J'aimerais récolter des informations dans un classeur, mensuellement, (voire peut-être 2 classeurs) concernant plusieurs personnes, les générer dans une nouvelle feuille d'un nouveau classeur, puis envoyer les dites informations à différents destinataires via Outlook.

Plusieurs contraintes : (pas forcément dans l'ordre)

1) Que le tout puisse se faire de façon automatique, sans intervention de ma part, par exemple le 2 du mois pour le mois précédent.

2) Récupérer les informations sur un classeur ressemblant à celui que j'ai joint, contenant un onglet par mois.
Il s'agit de sommes dont le total est calculé en cellules X32:X37
1ère difficulté, les champs correspondants ne sont pas toujours renseignés. Ils le sont dans ce cas par un coloriage de cellule ( plusieurs couleurs pourraient correspondre à différents montants).
Il faut donc prendre en compte le total des sommes renseignées dans chaque champ + les mises en forme.
Si ce point est trop complexe, je pourrai alors renseigner les montants ailleurs, sachant que j'aimerais tout de même pouvoir l'éviter.
Tous mes onglets sont structurés de la même manière, mis à part que j'ai parfois besoin d'insérer des lignes supplémentaires.

3) Une fois ceci fait, générer une nouvelle feuille (fichier exemple 2) où j'aurais une bdd pour le mois échu, avec les champs "personne", "genre", "montant" ( et peut être d'autres choses à venir)

4) Enfin envoyer un mail à chaque personne, contenant ses informations propres, ainsi qu'une copie à mon humble personne.

Je joins donc 2 classeurs, le 1er étant similaire à l'original, le second étant celui que je souhaiterais être généré, et enfin une image du mail tel qu'il devrait être. (J'ai mis en rouge les données modifiables par les valeurs Excel, et en classeur 2 le genre, afin de pouvoir choisir entre un texte masculin et un texte féminin)

J'ai essayé d'être le plus clair possible. Néanmoins, faites moi savoir si vous avez besoin d'explications davantage détaillées.
Si vous avez lu jusqu'ici, c'est que vous êtes sacrément motivés ! 🙂

Merci à vous tous.

 

[Staple1600]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

Bonjour à tous

Calvus
Je viens d'ouvrir ta pièce jointe... et je suis tombé de ma chaise... à cause du choc visuel.
C'est quoi toutes ces couleurs! Ca pique les yeux et c'est pas agréable à lire.
Vite, un petit ALT+F11 au moins dans VBE, mes globes sont préservés.
Bon bah tiens puisque j'y suis dans VBE , un petite suggestion en passant

Sub Copie_ValeursM()
 ' puisqu'il s'agit ici de copier les valeurs seules.
Sheets("Compil").[A2:B7].Value = Sheets("nov").[w32:x37].Value
End Sub

PS: Je ne vois pas de code relatif à Outlook dans ta PJ, et j'ai du mal à saisir ce que tu veux faire au final 😉
Tu pourrais récapituler et détailler chaque étape, stp ?

 

[Staple1600]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

Re


Et pour ce qui concerne les formules, pourquoi pas simplement en X32 ?
=SOMME(B6:X6)+SOMME(B30😛30)

 

[Calvus]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

Bonjour,

Ok, je vais à nouveau détailler ma demande.
En attendant, voici un fichier spécial Staple. Et ce n'est même pas là la surprise dont je parlais.
🙂 Cordialement

Edit

Et pour ce qui concerne les formules, pourquoi pas simplement en X32 ?
=SOMME(B6:X6)+SOMME(B30😛30)

Tout simplement parce la ligne renseignée, (Pierre ici), peut se trouver n'importe où. En A6 ou A13 ou 52.

 

[Staple1600]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

Re


N'étant un formuliste émérite, je me suis amusé avec la formule ci-dessous, qui au final fonctionne, mais j'ai le sentiment qu'on peut faire plus propre, non ?

=SOMME(DECALER(INDIRECT("B"&EQUIV(W32;A1:A24;0));;;;21))+SOMME(DECALER(INDIRECT("B"&EQUIV(W32;A30:A48;0)+29);;;;16))

PS: Un des avantages que j'y vois c'est qu'elle n'est pas matricielle, l'autre c'est que cela m'a diverti.

 

[Calvus]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

Re,

@ Staple

Staple1600
Tu pourrais récapituler et détailler chaque étape, stp ?

Ca risque d'être un peu long..

J'ai donc un classeur avec 12 onglets identiques à celui en PJ.
Les feuilles (champs B6:H24, J6😛24, etc..) sont renseignées au fur et à mesure soit positivement, soit négativement, avec une colonne de référence A6:A48.
Les formules Sommeprod me permettent d'avoir la somme totale par onglet et par genre.(Pierre, Paul, Fruits, Machin, etc..).

1) Ce qui m'intéresse pour le moment est de lister mes sommes positives. On retrouve l'index en champ W32:X37. J'ai récemment listé ces valeurs en cellule O54. On pourrait d'ailleurs se servir de cet index pour la suite du projet, car je ne serai pas limité à ce moment là par la mise en forme (spéciale Staple) de la feuille qui ne peut à priori contenir que 5 lignes (W32:X37).

Or, les cellules peuvent être renseignées soit par des valeurs, positives ou négatives, soit par une couleur.
D'où ma nécessité donc de pouvoir faire un Sommprod sur les couleurs.
On retrouve ces valeurs supposées dans le classeur 2 du premier message.
Par exemple, concernant Pierre, il faut comptabiliser les 3 sommes de la ligne 6 (400x3=1200) et les 2 sommes de la ligne 30 (400 + une couleur marron en F30 = 800).
Ma cellule X32, qui actuellement donne le résultat de 1600 (400x4) devrait donner 2000.
L'idée de la formule serait donc : Sommeprod(Pierre*champs*couleur marron), attribuant dans ce cas là 400 à la couleur marron. Si ce dernier point est trop compliqué, ce que je conçois, je pourrais affecter une couleur particulière à chaque personne, et par conséquent une valeur particulière.
Idem pour les autres lignes.

2) A partir de là, envoyer les valeurs récupérées, une fois par mois, en feuille Compil. J'ai mis sur cette dernière ce que j'obtiens aujourd'hui en colonnes A et B, et ce que je souhaiterais obtenir à coté.
Donc en compil, obtenir les résultats des différents mois, indexant les personnes et y affectant leur montant et leur genre. Avoir le mois qui s'affiche automatiquement, et intercaler une ligne entre chaque mois. Pour moi qui suis néophyte, ça me semble absolument irréalisable, mais quand je vois ce que vous réussissez à faire, je me dis et pourquoi pas ?

3) Enfin, générer un mail automatique, pour chaque personne présente dans la liste avec ses données propres, comme montré sur l'image du 1er message. Une copie des mails me serait adressée.

Suite à une remarque de Dranreb, je privilégie donc la copie sur une feuille du classeur et laisse tomber l'idée d'un nouveau classeur.

Staple1600
PS: Je ne vois pas de code relatif à Outlook dans ta PJ, et j'ai du mal à saisir ce que tu veux faire au final

Ben non, je sais pas faire et je ne parviens même pas à terminer l'étape 1. 😕

Staple1600
N'étant un formuliste émérite, je me suis amusé avec la formule ci-dessous, qui au final fonctionne, mais j'ai le sentiment qu'on peut faire plus propre, non ?
Code :
=SOMME(DECALER(INDIRECT("B"&EQUIV(W32;A1:A24;0));;;;21))+SOMME(DECALER(INDIRECT("B"&EQUIV(W32;A30:A48;0)+29);;;;16))

PS: Un des avantages que j'y vois c'est qu'elle n'est pas matricielle, l'autre c'est que cela m'a diverti.

Oui, mais le problème est que je peux avoir besoin d'insérer des lignes, voire souvent, et dans ce cas là la formule ne fonctionne plus, ce qui n'est pas le cas avec Sommprod en matriciel.


Voilà, alors dis moi (ou dites moi) si ces explications sont suffisantes ou s'il en faut d'autres.

J'ai nettoyé le nouveau faichier et gardé les macros qui m'intéressaient, renseigné l'onglet Déc en le rendant moins piquant 😉.

Cordialement.

 

[Si...]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

salut

Si... tu te contentes d'un "x" avec une MFC pour avoir la couleur marron et Si... tu as de onglets de même structure, tu pourras avec une évènementielle te contenter de ces 2 macros pour remplacer tes formules "totaux" en "SommeProd"

Dim L As Byte
Dim S1(48) As Single, S2(48) As Single, Sx(48) As Byte
Sub Toto()
  'pour chaque mois
  For L = 1 To 48
    S1(L) = 0: S2(L) = 0: Sx(L) = 0
  Next
 'sommes par nom et par x en tableau1
  For L = 6 To 24
    S1(L) = Application.Sum(Cells(L, 1).Resize(1, 24))
    Sx(L) = Sx(L) + Application.CountIf(Cells(L, 1).Resize(1, 24), "x")
  Next
  'sommes par nom et par x en tableau1 et tableau2
  For L = 30 To 48
    S2(L) = S1(L - 24) + Application.Sum(Cells(L, 1).Resize(1, 16))
    Sx(L) = Sx(L - 24) + Application.CountIf(Cells(L, 1).Resize(1, 16), "x")
  Next
  Application.ScreenUpdating = False
  For L = 30 To 48
    Call Somme(32, 37, 24) 'totaux en X (24)
    Call Somme(39, 47, 19) 'totaux en S (19)
  Next
End Sub
Private Sub Somme(Deb As Byte, Fin As Byte, Cc As Byte)
  Dim Li As Byte
  For Li = Deb To Fin
      If Cells(Li, Cc - 1) = Cells(L, 1) Then
        Cells(Li, Cc) = S2(L) + Sx(L) * 800
        If Cells(Li, Cc) = 0 Then Cells(Li, Cc) = ""
      End If
    Next
End Sub

complément : pour initialiser chaque mois

 

[Calvus]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

Bonjour,

Mère Sissi 🙂

Bon, je ne comprends rien, mais ça c'est normal !
En revanche, j'ai copié ton code dans la feuille concernée, en choisissant SelectionChange.
Dès que je veux accéde au classeur, j'ai un message d'erreur, disant : erreur de compilation End Sub attendu.
Or le End Sub est bien présent, et il semble que ce soit la deuxième ligne Dim S1(48).... qui pose problème.
Je ne peux pas rester davantage ce matin, je regarderai ça ce soir en rentrant, vers 23H.
Merci

 

[Si...]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

re

tu as dû oublier les 2 premières lignes de déclaration!
La seconde est obligatoirement située ici pour passer les valeurs de la première à la seconde macro

Voici un exemple dans ThisWorkbook pour lancer la macro en sélectionnant la cellule Y31 de n'importe quel onglet.

Dim L As Byte
Dim S1(48) As Single, S2(48) As Single, Sx(48) As Byte
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Address <> "$Y$31" Then Exit Sub
  'raz des tableaux pour chaque feuille
  For L = 1 To 48
    S1(L) = 0: S2(L) = 0: Sx(L) = 0
  Next
  'sommes par nom et par x en tableau1
  For L = 6 To 24
    S1(L) = Application.Sum(Cells(L, 1).Resize(1, 24))  'somme des montants pour chaque lignes de la plage haut
    Sx(L) = Application.CountIf(Cells(L, 1).Resize(1, 24), "x") 'nombre de x pour chaque ligne
  Next
  'sommes par nom et par x en tableau1 et tableau2
  For L = 30 To 48
    S2(L) = S1(L - 24) + Application.Sum(Cells(L, 1).Resize(1, 16)) 'cumuls par ligne des montants des 2 plages
    Sx(L) = Sx(L - 24) + Application.CountIf(Cells(L, 1).Resize(1, 16), "x") 'cumuls par ligne des x des 2 plages
  Next
  Application.ScreenUpdating = False
  For L = 30 To 48
    Call Somme(32, 37, 24)  'plage W32:W37 pour écrire en X
    Call Somme(39, 47, 19) ' plage R39:R47 pour écrire en S
  Next
End Sub
Private Sub Somme(Deb As Byte, Fin As Byte, Cc As Byte)  'macro commune aux 2 plages
  Dim Li As Byte
  For Li = Deb To Fin
      If Cells(Li, Cc - 1) = Cells(L, 1) Then
        Cells(Li, Cc) = S2(L) + Sx(L) * 800
        If Cells(Li, Cc) = 0 Then Cells(Li, Cc) = ""
        Exit For
      End If
    Next
End Sub

 

[Calvus]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

Bonjour,

Si, c'est bien merci. Pas bête de renseigner la cellule avec un "x". Mais il y a des bugs. Le montant des additions n'est pas bon. Et apparemment, si on modifie les montants à la baisse, le recalcul ne se fait pas.
Je pense à priori opter pour le code en workbook, et il si tu pouvais m'expliquer le code afin que je puisse l'adapter, ce serait nickel.

Je regarde à nouveau ce soir plus en détails. Trop à la bourre ces dernières 24H.

Cordialement

 

[Si...]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

salut

Si, c'est bien merci. Pas bête de renseigner la cellule avec un "x". Mais il y a des bugs. Le montant des additions n'est pas bon. Et apparemment, si on modifie les montants à la baisse, le recalcul ne se fait pas.

pas chez moi.
Si..., en Y6, je tape =SOMME(B6:X6), le changement de saisie sur cette plage est bien répercuté.
La première macro utilise ceci (Application.Sum...)

Pour la macro évènementielle (ce n'est peut-être pas la mieux indiquée) dans ThisWorkBook (voir image)
Regarde la pièce jointe 870770

les montants sont calculés en 3 tableaux (S1 montants lignes de 6 à 24, S2 montants précédents augmentés des montants des lignes de 30 à 48 et Sx nombres de x par lignes de 6 à 24 augmentés des nombres de x par lignes de 30 à 48*).
Certains vecteurs des tableaux ne servent à rien mais en les dimensionnant 48, cela évite de jongler avec les indices.

*Bien sûr, les noms doivent être dans le même ordre dans les plages (mais je crois que tu as une procédure de copie). Nota : le coefficient est identique pour tous les x (ici, 800).

Je t'envoie un message perso quant à la poursuite des échanges.

 

[ivan27]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

Bonjour Calvus, le forum

Je me permets de revenir sur ta demande initiale :

Point 1 : A voir en dernier
Points 3 et 4 ne semblent pas poser de problème.
Par contre, je ne comprends ce que tu souhaites en 2. Quelles sont les "mise en forme" à prendre en compte et leurs valeurs ?
Peux-tu le développer et fournir un exemple de calcul avec ces mise en formes ?

Cordialement,

Ivan

 

[Calvus]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

Bonsoir à tous, Ivan,

Bien qu'ayant eu très peu de temps pour tester correctement, ça semble tout de même fonctionner maintenant avec la dernière macro de Si, en ce qui concerne le calcul prenant en compte la mise en forme.

Par contre, je ne comprends ce que tu souhaites en 2. Quelles sont les "mise en forme" à prendre en compte et leurs valeurs ?
Peux-tu le développer et fournir un exemple de calcul avec ces mise en formes ?

La demande est détaillée en message 20, ainsi que dans le fichier joint. Peut être n'as tu pas tout lu, dans le cas contraire, dis moi si tu veux d'autres explications.

Cordialement

 

[Si...]

Re : Envoi multiple Outllook après synthèse, fichier Spécial Barbatruc VBAiste !

salut

As-tu avancé ? Je viens de jeter un coup d’œil à ta demande concernant les cumuls.
Le choix du groupement par semaines ne simplifie pas les choses. Sur une feuille tu as le mois mais tu peux avoir des jours du mois précédent, des jours du mois suivant.
Si... joint, un essai en passant par la quatrième dimension. Pour l’instant, j‘ai laissé des colonnes intermédiaires. Certains « "formulistes" pourraient te trouver de quoi distinguer les 4 parties (sommes feuille, somme jours mois -1, somme mois et sommes jours +1).
Dis moi si cela t’intéresse (avant de compléter avec les mails ou de commenter en détail mon code).