Sub Email()
'
' Email Macro
'
Application.ScreenUpdating = False
Workbooks("Real-Time Dashboard_2.0.xlsm").Save
'Unprotect Sheet
Dim Sh As Worksheet
Dim myPassword As String
myPassword = "lucasmyass"
For Each Sh In ActiveWorkbook.Worksheets
Sh.Unprotect Password:=myPassword
Next Sh
'Email
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("E3:AB81"))
rng.SpecialCells(xlCellTypeVisible).Select
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Dim TempFilePath As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Email Details
On Error Resume Next
With OutMail
.To = "SIC2 (Student Inquiry Coordinators 2)"
.CC = ""
.BCC = ""
.Subject = "Real Time Update"
.HTMLBody = "<span LANG=EN>" _
& "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
'Create the image as a JPG file
Call createJpg("Dash1", "E4:Z80", "DashboardFile")
'we attached the embedded image with a Position at 0 (makes the attachment hidden)
TempFilePath = Environ$("temp") & "\"
.Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue, 0
'Then we add an html <img src=''> link to this image
'Note than you can customize width and height - not mandatory
.HTMLBody = .HTMLBody & "" _
& "<img src='cid:DashboardFile.jpg'" & "width='1300' height='1300'><br>" _
.Display
'.Send
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)
ThisWorkbook.Activate
Worksheets("Dash1").Activate
Set Plage = ThisWorkbook.Worksheets(Namesheet).Range(nameRange)
Plage.CopyPicture
With ThisWorkbook.Worksheets("Dash1").ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
End With
Worksheets("Dash1").ChartObjects(Worksheets("Dash1").ChartObjects.Count).Delete
Set Plage = Nothing
End Sub
