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 78 79 80 81 82 83 84 85 86 87 88 89 90
|
Sub Envoi()
' Eric KERGRESSE EIRL
Dim OlApp As Outlook.Application
Dim OlItem As Outlook.MailItem
Dim MatriceEnvoi() As Variant
Dim LigneDeTitre As Long
Dim DerniereLigne As Long
Dim AireMailing As Range
Dim CelluleMailing As Range
Dim CtrI As Long
Dim Continuer As Boolean
LigneDeTitre = 1
DerniereLigne = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Set AireMailing = Range(Cells(LigneDeTitre + 1, 1), Cells(DerniereLigne, 1))
ReDim MatriceEnvoi(2, 0)
MatriceEnvoi(0, 0) = Cells(LigneDeTitre + 1, 1)
' Dénombrement des mails à envoyer
For Each CelluleMailing In AireMailing
Continuer = True
For CtrI = LBound(MatriceEnvoi, 2) To UBound(MatriceEnvoi, 2)
Select Case MatriceEnvoi(0, CtrI)
Case CelluleMailing
Continuer = False
Exit For
End Select
Next CtrI
If Continuer = True Then
ReDim Preserve MatriceEnvoi(2, UBound(MatriceEnvoi, 2) + 1)
MatriceEnvoi(0, UBound(MatriceEnvoi, 2)) = CelluleMailing
End If
Next CelluleMailing
' Chargement des adresses mail et des numéros dans MatriceEnvoi
For CtrI = LBound(MatriceEnvoi, 2) To UBound(MatriceEnvoi, 2)
For Each CelluleMailing In AireMailing
Select Case MatriceEnvoi(0, CtrI)
Case CelluleMailing
MatriceEnvoi(1, CtrI) = CelluleMailing.Offset(0, 1)
MatriceEnvoi(2, CtrI) = MatriceEnvoi(2, CtrI) & CelluleMailing.Offset(0, 2) & ", "
End Select
Next CelluleMailing
Next CtrI
' Envoi des mails
Set OlApp = CreateObject("Outlook.application")
For CtrI = LBound(MatriceEnvoi, 2) To UBound(MatriceEnvoi, 2)
Set OlItem = OlApp.CreateItem(olMailItem)
With OlItem
.To = MatriceEnvoi(1, CtrI)
.Subject = "Numéro"
.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><body>" _
& "Bonjour " & MatriceEnvoi(0, CtrI) & ",<p>" & " <p>" _
& "Voici donc le ou les numéro(s) vous concernant : <p>" _
& "<b><font color='blue'> " & MatriceEnvoi(2, CtrI) & "</font></b><br>" & " <p>" _
& "Merci encore. <br>" _
& " <p>" _
& "Bonne journée. <p>" _
& "</body><HTML>"
'.Display
.Send
End With
Next CtrI
Set AireMailing = Nothing
End Sub |
Partager