Private Sub Workbook_Open()
Dim m As Integer
Dim n As Integer
For m = 1 To Sheets.Count
For n = 1 To Sheets(m).Range("M65536").End(xlUp).Row
Select Case Sheets(m).Range("M" & n).Value
Case Is = "consultation"
Text = Text & "ATTENTION, le marché n° " & Sheets(m).Range("A" & n) & " prend fin dans 6 mois. Contacter le service concerné. " & " " & Chr(10)
Case Is = "reconduire"
Text = Text & "Veuillez reconduire le marché n° " & Sheets(m).Range("A" & n) & Chr(10)
End Select
Next n
Next m
MsgBox Text
ActiveWorkbook.Save
End Sub