Email selection as picture

Email selection as picture

I've used this a lot in the last few years to quickly sending out a selection of charts and tables via email in a format that doesn't require the end user to have office installed to view it correctly.

This can be called from a in-sheet button / or ribbon control, or called from within another Sub-Routine for a specific range/ranges.

Sub SendMailSelection(control As IRibbonControl)

Call Send_Email_range(Selection)

End Sub

Full code :

Sub Send_Email_range(SelctRang As Range)

' :: Setup object and variable to reference and store grid visibility state. ::
Dim MyGrid As Boolean, MyWindow As Window, MyPic As Picture, MySheet As Worksheet
Dim MyMailApp As Object, MyMailItem As Object, MyWordDoc As Object
' :: Grab Grid state and set to variable ::
Set MySheet = Sheets(SelctRang.Parent.Name)
Set MyWindow = Windows(SelctRang.Parent.Parent.Name)
MyGrid = MyWindow.DisplayGridlines

' ::  Turn off gridlines on referenced window ::
MyWindow.DisplayGridlines = False

' :: Copy Referenced range to Clipboard ::

' :: Paste as picture , then cut back to clipboard as picture ::

Set MyPic = MySheet.Pictures.Paste

' :: Open a new mail item ::

Set MyMailApp = CreateObject("Outlook.Application")

Set MyMailItem = MyMailApp.CreateItem(olMailItem)

' :: Open Word editor for mail item ::


Set MyWordDoc = MyMailItem.GetInspector.WordEditor

':: Paste picture to mail body ::


':: Restore defaults for Gridlines : ::
MyWindow.DisplayGridlines = MyGrid

' :: Jump back to outlook ::

End Sub