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 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
| Private Sub UserForm_Initialize()
Dim plage As Range, cell As Range
Dim ValMin As Integer, ValSup As Integer
Dim i As Integer, j As Integer, A As Integer, z As Integer
Dim t1 As String, t2 As String, Item1 As String, Item2 As String
Dim cpt As Byte
Set plage = Sheets("Libellés").Range("A1:" & Sheets("Libellés").Range("A30").End(xlUp).Address)
ReDim Tab1(1 To plage.Count + 1, 1 To 2)
i = 0
For Each cell In plage
i = i + 1
With cell
Tab1(i, 1) = .Text
Tab1(i, 2) = .Offset(0, 1).Text
End With
Next
'****************TRIE LA LISTBOX**************************
ValMin = LBound(Tab1)
ValSup = UBound(Tab1) - 1
For i = ValMin To ValSup
For j = ValMin + A To ValSup
If Tab1(i, 1) > Tab1(j, 1) Then
t1 = Tab1(j, 1): t2 = Tab1(j, 2)
Tab1(j, 1) = Tab1(i, 1): Tab1(j, 2) = Tab1(i, 2)
Tab1(i, 1) = t1: Tab1(i, 2) = t2
End If
Next j
A = A + 1
Next i
'****************ELIMINE DOUBLONS******************
Item1 = ""
Me.ledestinataire.Clear
Me.Listecontact.Clear
cpt = 1
ReDim Tab2(1 To UBound(Tab1), 1 To 2)
For i = LBound(Tab1) To UBound(Tab1)
If Item1 = Tab1(i, 1) Then
Else
Item1 = Tab1(i, 1)
Me.ledestinataire.AddItem Item1
cpt = ledestinataire.ListCount
Tab2(cpt, 1) = Item1
Item2 = Tab1(i, 2)
Me.Listecontact.AddItem Item2
Tab2(cpt, 2) = Item2
End If
Next i
Dim varl, varh As Byte
varl = UBound(Tab2, 2)
varh = UBound(Tab2, 1)
varl = varl + 10
'****************************************************
Application.ScreenUpdating = False
Sheets("Libellés").Activate
Sheets("Libellés").Range(Cells(1, 1), Cells(varh, varl)) = Tab2()
Dim cp As Byte
cp = UBound(Tab2)
Sheets("A").Activate
Application.ScreenUpdating = True
End Sub
Private Sub Envoyer_Click()
Dim i As Byte, Nouveautablo() As String, Apt As Byte
Dim VarTab As Variant
Apt = 0
For i = 0 To Destinataire.ledestinataire.ListCount - 1 'zone de liste mails
If ledestinataire.Selected(i) = True Then
Apt = Apt + 1
ReDim Nouveautablo(Apt)
Nouveautablo(Apt) = Tab2(i + 1, 1)
End If
Next i
VarTab = Join(Nouveautablo(), ";")'****c'est ici que ça coince*****
Call envoi_Feuille
Unload Destinataire
Destinataire.Hide
End Sub |
Partager