INFORMATIONAL ** THIS INFORMATION HAS NOT BEEN VERIFIED **
How to setup Excel macros and use to e-mail reports using Sage MAS Intelligence
Entry Type: Informational
Product: SMI Report Manager
Application: Report Manager
Version Reported: 1.00.0073
Subject:
How to setup Excel macros and use to e-mail reports using Sage MAS Intelligence.Possible Resolution:
Standard Excel functionality allows for the addition of macros to reports. The following is a commented macro that takes the current sheet, prompts the user to enter in the required e-mail address then creates an e-mail sending just the current sheet as the only sheet in a new workbook to the recipient. This can be modified to read from particular cells instead of prompting users to enter in the information.
MACROS Details:
Sub Send1Sheet_ActiveWorkbook()
'One variable for sheet name, one for the email address
Dim strEmail As String
Dim strSheetName As String
'Launch a prompt box that asks for the email address
strEmail = InputBox(Prompt:="Email Address To Send To", Title:="Email Address", Default:="Email Here")
'If nothing changes in the default value, or it's blank, then kill the macro
If strEmail = "Email Here" Or _
strEmail = vbNullString Then
Exit Sub
Else
'Set the variable strSheetName to equal the name of the sheet
strSheetName = ActiveSheet.Name
End If
'Create a new workbook containing just the current sheet (thereby getting around the sheet position issue
ThisWorkbook.Sheets(strSheetName).Copy
' Email the sheet using the strEmail variable, as retrieved by the InputBox
With ActiveWorkbook
.SendMail Recipients:=strEmail, Subject:="Try Me " & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
End Sub
