Sometimes it is useful to send a sheet right from Excel just by clicking a button (without manual copying and saving). Here is a VBA function that sends an Excel Sheet via email.
'-------------------------------------------------------------------------
' Source: risksir.com
' Description: Sending email from Excel (launched Outlook is needed)
'-------------------------------------------------------------------------
Sub Email_SendSheet(Email_Sheet As Worksheet, Email_Send_To As Variant, Email_Subject As String)
Email_Sheet.Copy
ActiveSheet.Buttons.Delete 'deleting buttons
Range("A1:ZZ100").Value = Range("A1:ZZ100").Value 'deleting formulas
With ActiveWorkbook
.SendMail Recipients:=Email_Send_To, Subject:=Email_Subject
.Close SaveChanges:=False
End With
End Sub
Need to know:
1. This script uses MS Outlook functionality, so to send the message your Outlook should be launched.
2. When calling the function the alert can appear —"A program is trying to send an email message on your behalf. If this is unexpected, click Deny and verify your antivirus software is up-to-date". To send your email you need to click "Allow". You can disable this alert in the settings (not recommended) — read help for more info.
