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
| Sub EnvoiFinal()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range, x As Integer
Dim mesdestinataires As String
Dim Chemin As String, Fichier As String
Dim Wkb As Workbook
Application.ScreenUpdating = False
Set Wkb = ThisWorkbook
Chemin = Wkb.Path & "\"
Fichier = "test.xlsx"
ActiveSheet.Copy ' crée une copie de la feuille active
ActiveWorkbook.SaveAs Chemin & Fichier
Wkb.Activate
Set OutApp = CreateObject("Outlook.Application")
Sheets("Infos revue").Select
For Each cell In Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "D").Value) = "oui" Then mesdestinataires = cell.Value & "; " & mesdestinataires
Next cell
x = Len(mesdestinataires) - 2
nbritem = Left(mesdestinataires, x)
Set OutMail = OutApp.CreateItem(0)
If MsgBox("Etes-vous certain de vouloir envoyer ce mail ?", vbYesNo, "Demande de confirmation") = vbYes Then
With OutMail
.To = mesdestinataires
.Subject = "Envoi mail"
.Body = "Test test test"
.Attachments.Add Chemin & Fichier
.Display ' Ici on peut supprimer pour l'envoyer sans vérification
.Send
MsgBox "Le mail à bien été envoyé !"
End With
End If
Set OutMail = Nothing
Set OutApp = Nothing
Set Wkb = Nothing
Workbooks(test).Activate
Workbooks(test).Close
Kill test
End Sub |
Partager