Discussion :

[Denis_67]

Bonjour,

J'utilise avec succès depuis un moment une variable de type variant pour stocker des destinataires de mails et ensuite envoyer la feuille courante à cette liste.
Les noms des destinataires sont lus par une loop dans des cellules où j'ajoute l'extension (toujours la même pour du courrier interne).

Aujourd'hui ma liste étant plus grande que d'habitude, je me retrouve bloqué.

Deux questions:
Quelle est la capacité maximum de la zone variant
Quelle autre type de variable faudrait-il utiliser, j'ai essayé string sans succès.

Lorsque je regarde le contenu de ma variable en mode espion je vois que ma boucle stocke les noms jusqu'à un certain point
après je ne vois pas les noms s'ajouter bien que la boucle tourne sans erreur. Mais c'est peut-être le mode espion qui ne permet pas de voir l'intégralité du contenu de la variable ?

J'ai regardé le tuto de Skyroad sur les variables, mais même si string peut contenir 2 miliards d'info, je plafonne.

http://silkyroad.developpez.com/VBA/LesVariables/#LII-A

Merci pour vos suggestions.
Denis

[TripleExcel]

Pour du texte, tu ne peux utiliser que du string qui, bien que je n'en connais pas sa taille limite, est largement suffisant pour un texte.

Le type Variant autorise tout type de données. C'est son avantage. Mais je te le déconseille car il prend beaucoup de place en mémoire souvent pour rien et c'est Excel qui choisit le format qui lui semble le plus adapté.

On utilise Variant quand on ne connait pas le format (ce qui en soit est absurde) ou justement lorsque la valeur est susceptible de changer... de varier... passant par exemple au cours du traitement d'un état numérique à un état de chaîne de caractère. Ou lorsque tu souhaite utiliser une variable de type 'tableau" dans lequel sera stocké plusieurs type de données.

Et cela me permet de faire une transition avec ce qui pourrait être une solution, utiliser un tableau. A condition que j'ai bien compris le contexte de ta problématique (à ce propos, envoie un fichier c'est toujours plus parlant ).
Si je comprends bien, dans ta variable, il y a plusieurs nom de fichier?

[Denis_67]

Merci pour ces éclaircissements.

Cette macro est dans mon classeur perso, il permet d'envolyer la feuille courante d'un classeur vers une liste de destinataires fugurant dans ma feuille.
Voici le code c'est un code que j'ai en partie récupéré et réorganisé.

Le problème est dans la variable Mes_destinataires en mode espion, je ne vois pas tous les destinataires quand il y en a de trop
et au moment où j'envoie le message, j'ai une erreur 1004 la méthode show de la classe dialogue a echoué. sur la ligne
Application.Dialogs(xlDialogSendMail).Show Mes_destinataires, Mon_Objet

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
 Public Mon_Objet
Public Ma_feuille
Public Mes_destinataires
Public Dest_deb
Sub envoie_feuille_mail()
' Macro1 Macro
' Envoir la feuille courante comme mail
Dim Mes_destinataires As String           'Les destinataires
Dim Mon_Objet As String                   'L'objet du message
Msg = "Indiquer la feuille à expédier"
Title = "Sélection de la feuille"
Ma_feuille = InputBox(Msg, Title, ActiveSheet.Name)
If Ma_feuille = "" Then
Exit Sub
End If
Msg = "Saisir l'objet du mail"
Title = "Objet"
Mon_Objet = InputBox(Msg, Title, ActiveCell.Value)
If Mon_Objet = "" Then
Exit Sub
End If
Msg = "Indiquer le début de la liste de destinataires sur votre feuille exemple A1 laisser une ligne blanche en fin de liste ! OU laisser vide pour renseigner dans Lotus directement"
Title = "DESTINATAIRES"
Dest_deb = InputBox(Msg, Title)
If Dest_deb <> "" Then
' je charge ma liste de destinataires
On Error GoTo erreur_feuille
    Sheets(Ma_feuille).Activate
 
    Range(Dest_deb).Select
Do While Not IsEmpty(ActiveCell)
    Mes_destinataires = Mes_destinataires & ActiveCell.Value & "@MonOrganisation.com" & ", "
    Selection.Offset(1, 0).Select
Loop
End If
' je copie la feuille dans un nouveau classeur
      ActiveWorkbook.Sheets(Ma_feuille).Copy
 
'je l'envoie et je referme sans sauvegarder
    Application.Dialogs(xlDialogSendMail).Show Mes_destinataires, Mon_Objet  
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
Exit Sub
erreur_feuille:
Dim monMsg
monMsg = "Feuille " & Ma_feuille & " inexistante ou mal orthographiée."
MsgBox Prompt:=monMsg
Exit Sub
End Sub

[Daniel.C]

Bonjour,

Après :

Code :

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

Loop

ajoute :

Debug.Print Len(Mes_destinataires)

Pour voir la longueur du string.

[GreatTeacherOnizuka]

Bonjour,

J'ai eu ce problème récemment, c'est parce que ta variable mes_destinataire dépasse les 450 caractères, c'est une limite fixée par excel d'après ce que l'on m'a dit.

[Daniel.C]

Je remplis une variable de 1 000 000 caractères avec une variable (Var) longue de 5000 caractères :

Code :

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

1
2
3
4
5
6
7
8
9
Sub test()
    Dim txt As String, Var As String
    Var = Application.Rept("a", 5000)
    MsgBox "longueur de ""Var"" : " & Len(Var)
    Do While Len(txt) < 1000000
        txt = txt & Var
    Loop
    MsgBox Len(txt)
End Sub

Ajoute :

Code :

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

On Error GoTo 0

après :

Code :

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

Sheets(Ma_feuille).Activate

et refarde si, maintenant, tu as une erreur.

[GreatTeacherOnizuka]

Bonjour Daniel C.,

Oui on peut mettre jusqu'à 2 milliards de caractères dans un type String si mes souvenirs sont bons (à vérifier). Mais ce sont les formules Excel qui n'acceptent pas plus de 450 caractères.

J'ai pas regardé en détail mais il me semblait qu'il voulait copier justement le résultat de mes_destinataires dans un fichier Excel.

Mais peut être que je me trompe, peux tu afficher txt? J'ai pas essayé, j'essaierai quand j'ouvrirai un excel.

Bonne soirée.

[TripleExcel]

Ce qui m'étonne dans ton code c'est que tu déclares tes variables en public puis de nouveau dans ton programme.
Peut être il y a une interaction.
Pourquoi les déclarer publiquement?

Essaye de ne les déclarer que dans la macro pour voir si cela change quelque chose. Sinon, je te ferais un proposition différente.


1 ou 2 petites remarques/questions aussi, qui ne régleront pas ton problème mais qui sont plus pour toi :
ceci

Code :

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

1
2
3
If Ma_feuille = "" Then
Exit Sub
End If

écrit le ainsi

Code :

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

If Ma_feuille = "" Then Exit Sub

aussi, je ne comprends pas l'intérêt de cette ligne

Code :

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

ActiveWorkbook.Sheets(Ma_feuille).Copy

Enfin, pense à décaler ton code pour qu'il soit plus lisible

[Daniel.C]

Bonjour à tous,

@ GreatTeacherOnizuka :

La taille maxi d'une formule varie suivant les versions d'Excel (8 000 avec Excel 2013). Une cellule peut contenir 32 767 caractères.

[TripleExcel]

En fait j'ai tout relu mais c n'est pas ta variable qui pose problème mais ta boite de dialogue générée par ce code

Code :

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

Application.Dialogs(xlDialogSendMail).Show

C'est elle qui doit t'imposer une limite en nombre de destinataire.
Il faudrait donc arriver à trouver cette limite et faire l'envoie de mail en plusieurs fois au besoin.

[illight]

Comme tu l'as proposé (je crois que c'est toi), pourquoi ne pas utiliser une variable tableau ?

[Invité]

si tu veux connaitre la taille max d 'un variant!

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
57
58
59
60
61
Sub TestVariant()
Dim t As Variant
Dim I As Long
On Error Resume Next
Do While 1 <> 2
    t = t & Space(9999999)
    If Err Then Exit Do
    I = I + 9999999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(999999)
    If Err Then Exit Do
    I = I + 999999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(99999)
    If Err Then Exit Do
    I = I + 99999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(9999)
    If Err Then Exit Do
    I = I + 9999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(999)
    If Err Then Exit Do
    I = I + 999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(99)
    If Err Then Exit Do
    I = I + 99
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(9)
    If Err Then Exit Do
    I = I + 9
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(1)
    If Err Then Exit Do
    I = I + 1
    DoEvents
Loop
MsgBox I
End Sub

[Denis_67]

Merci à tous pour votre intéret et vos propositions à mon problème.

Dans l'ordre :

- Je déclare mes variables en public pour que la macro s'exécute dans tous les cas, j'ai supprimé la double déclaration merci pour ce point.
- Merci pour la correction /simplifacation de syntaxe du exit sub.
- Je copie la feuille dans un nouveau classeur pour pouvoir n'envoyer que cette feuille, Je n'ai pas trouvé d'autre solution.

Enfin la réponse à mon problème est dans la réponse de TripleExcel, la longueur maxi de la dialogue box pour le send mail est de 255 caractères (tiens donc !)

J'ai donc contourné la difficulté par un warning si j'ai trop de destinataires, je ne prends que les 255 premiers caractères et je demande à l'utilisateur de rajouter les suivant lui même dans la messagerie.
ça donne:

Code :

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

1
2
3
4
5
 
If Len(Mes_destinataires) > 255 Then
Mes_destinataires = Left(Mes_destinataires, 255)
MsgBox "Trop de destinataires ajouter les destinataires après" & Right(Mes_destinataires, 20) & " à la main"
End If

[Daniel.C]

Voici ton code modifié pour l'utilisation d'un tableau (testé avec 400 destinataires :

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
Public Mon_Objet
Public Ma_feuille
Public Dest_deb
Sub envoie_feuille_mail()
' Macro1 Macro
' Envoir la feuille courante comme mail
Dim Mes_destinataires() As String            'Les destinataires
Dim Mon_Objet As String                   'L'objet du message
Dim Ctr As String
Ctr = -1
Msg = "Indiquer la feuille à expédier"
Title = "Sélection de la feuille"
Ma_feuille = InputBox(Msg, Title, ActiveSheet.Name)
If Ma_feuille = "" Then
Exit Sub
End If
Msg = "Saisir l'objet du mail"
Title = "Objet"
Mon_Objet = InputBox(Msg, Title, ActiveCell.Value)
If Mon_Objet = "" Then
Exit Sub
End If
Msg = "Indiquer le début de la liste de destinataires sur votre feuille exemple A1 laisser une ligne blanche en fin de liste ! OU laisser vide pour renseigner dans Lotus directement"
Title = "DESTINATAIRES"
Dest_deb = InputBox(Msg, Title)
If Dest_deb <> "" Then
' je charge ma liste de destinataires
On Error GoTo erreur_feuille
    Sheets(Ma_feuille).Activate
 
    Range(Dest_deb).Select
Do While Not IsEmpty(ActiveCell)
    Ctr = Ctr + 1
    ReDim Preserve Mes_destinataires(Ctr)
    Mes_destinataires(Ctr) = ActiveCell.Value & "@MonOrganisation.com"
    Selection.Offset(1, 0).Select
Loop
End If
' je copie la feuille dans un nouveau classeur
      ActiveWorkbook.Sheets(Ma_feuille).Copy
 
'je l'envoie et je referme sans sauvegarder
    Application.Dialogs(xlDialogSendMail).Show Mes_destinataires, Mon_Objet
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
Exit Sub
erreur_feuille:
Dim monMsg
monMsg = "Feuille " & Ma_feuille & " inexistante ou mal orthographiée."
MsgBox Prompt:=monMsg
Exit Sub
End Sub

[Denis_67]

Heureuse surprise de Daniel,

J'aurais dû suivre sa proposition de tableau, mais comme j'avais pensé à tord que le problème venait de la fenêtre de dialogue qui limitait à 255 caractères, j'avias cloturé le ticket.

Donc cela fonctionne parfaitement.

Merci à tous et n'hésitez pas à utiliser ce petit utilitaire qui permet de n'envoyer qu'une feuille du fichier Excel sans enregistrer un nouveau fichier.
Le petit inconvénient est que comme Excel ne nome un fichier que lors de l'enregistrement le fichier joint s'apelle Classeur1.xls.

Tous mes projets sont suivis dans des fichiers Excel avec une liste de tache, après chaque réunion j'envoie la liste de tache avec les statuts et mon compte rendu est fait. Cchacun sait ce qu'il à faire et pour quand.

Cela évite de charger les messageries avec des gros fichiers excel.
Les fichiers complets restent accessibles sur le serveur de l'entreprise.

Encore une fois merci et bon week end.
Denis