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
| Sub EnvoiHistorique()
'---------------------------------------------------------------------------------------
' Procedure : EnvoiHistorique
' Author : Oliv'
' Date : 28/12/2015
' Purpose :
'---------------------------------------------------------------------------------------
'
Dim Ws_histo As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs , , , , , , xlShared
.KeepChangeHistory = True
.HighlightChangesOptions When:=xlAllChanges
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = False
Set Ws_histo = .Worksheets("Historique")
If Ws_histo Is Nothing Then Exit Sub
Ws_histo.Select
End With
Const olMailItem = 0
'Copy range of interest
Dim r As Range
'Set r = Range("b10:G14")
'r.Copy
Worksheets("Historique").Copy 'copie de la feuille vers un classeur temporaire
Range([a1], [a1].SpecialCells(xlLastCell)).Select
Selection.Copy
'Open a new mail item
Dim outlookApp As Object
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Object
Set outMail = outlookApp.CreateItem(olMailItem)
outMail.To = "destinataire@adresse.com"
outMail.Subject = "Modifs dans le classeur " & ActiveWorkbook.Name
'Get its Word editor
outMail.Display
Dim wordDoc As Object
Set wordDoc = outMail.GetInspector.WordEditor
'To paste as picture
'wordDoc.Range.PasteAndFormat wdChartPicture
'To paste as a table
wordDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
'Pour envoyer le Mail décommentez
'outMail.send
ActiveWorkbook.Close False 'fermeture du classeur temporaire
'pour masquer la feuille historique du classeur partagé
ActiveWorkbook.Save
End Sub |
Partager