- 0 0
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 :: SelctRang.Copy ' :: Paste as picture , then cut back to clipboard as picture :: Set MyPic = MySheet.Pictures.Paste MyPic.Cut ' :: Open a new mail item :: Set MyMailApp = CreateObject("Outlook.Application") Set MyMailItem = MyMailApp.CreateItem(olMailItem) ' :: Open Word editor for mail item :: MyMailItem.Display Set MyWordDoc = MyMailItem.GetInspector.WordEditor ':: Paste picture to mail body :: MyWordDoc.Range.Paste ':: Restore defaults for Gridlines : :: MyWindow.DisplayGridlines = MyGrid ' :: Jump back to outlook :: MyMailItem.Display End Sub