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
| Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Recherche As Range
Set Recherche = Sheets("LCEPLE1").Columns("AS").Find("Liste Complémentaire", LookIn:=xlValues, LookAt:=xlWhole)
If Not Recherche Is Nothing Then
If Range("A2") > "" And InStr(1, Cells(Target.Row, 45), "Liste Complémentaire") > 0 Then
[BZ1].FormulaLocal = "=NBVAL(A2:A999)"
REPONSES = Range("BZ1").Value
MsgBox "Il y a " & REPONSES & " réponses LC"
ENREGISTRERMODIFETMAILLCEPLE1
EnvoimailLCEPLE1
Else
MsgBox "Il n'y a pas de réponses LC"
End If
End If
Target.Offset(1, 0).Select
End Sub
Sub EnvoimailLCEPLE1()
Application.Run ("'Wait'")
Dim destinataire As String, sujet As String, body As String, fichierjoint As String, strcommand As String
destinataire = Range("AE2")
sujet = "RESULTAT COMMISSION"
body = "Madame, Monsieur" & Range("BG2") & " " & Range("AA2") & " " & Range("AC2") & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "Veuillez trouver, ci-joint, les rsultats de la commission"
fichierjoint = "C:\Documents and Settings\X\Bureau\LISTE COMPLEMENTAIRE Y 2014 (1).pdf"
strcommand = "C:\Program Files\Mozilla Thunderbird\thunderbird"
strcommand = strcommand & " -compose " & "to='" & destinataire & "'"
strcommand = strcommand & ",subject='" & sujet & "'"
strcommand = strcommand & ",body='" & body & "'"
'strcommand = strcommand & "," & "attachment=file:///" & fichierjoint
strcommand = strcommand & "," & "attachment=file:///" & fichierjoint
Debug.Print strcommand
Call Shell(strcommand, vbNormalFocus)
End Sub |
Partager