Sub CopyAllChartsToOutlookEmail()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xStartMsg As String
Dim xEndMsg As String
Dim xChartName As String
Dim xChartName1 As String
Dim xChartPath As String
Dim xChartPath1 As String
Dim xPath As String
Dim xPath1 As String
Dim xChart As ChartObject
Dim xChart1 As ChartObject
''----------Copying chart----
On Error Resume Next
xChartName = "Chart 2"
xChartName1 = "Chart 3"
If xChartName = "" And xChartName1 = "" Then Exit Sub
Set xChart = Sheets("Dashboard").ChartObjects(xChartName)
Set xChart1 = Sheets("Dashboard").ChartObjects(xChartName1)
xChart.Height = 190
xChart.Width = 200
xChart1.Height = 180
xChart1.Width = 190
Dim msgdate1 As String
Dim msgdate2 As String
Dim msgdate3 As String
Dim msgdate4 As String
Dim msgdate5 As String
' MsgBox (xChart1)
If xChart Is Nothing Then Exit Sub
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xStartMsg = "<font size='3' color='black'>" & dt1 & " | " & Format(dt, "yyyy") & "</font>" & "<font size='5'><br><b>" & "Balance Sheet Account Reconciliation Status " & "</b><br><br></font>"
xEndMsg = "<font size='4' color='black'><b> Reconciliation Due Dates" & "</b><br></font>"
' msgdate1 = "<font size='3'><b><br>" & "High risk accounts" & "</b></font>" & "<font size='2' color='black'>" & "<ul><li>" & "Reconciliation - " & recDate1 & "</li><li>" & "Review - " & RevDate1 & "</li></ul></font>"
' msgdate2 = "<font size='3'><b>" & "Medium risk accounts " & "</b></font>" & "<font size='2' color='black'>" & "<ul><li>" & "Reconciliation - " & recDate1 & "</li><li>" & "Review - " & RevDate1 & "</li></ul></font>"
' msgdate3 = "<font size='3'><b>" & "Low risk accounts" & "</b></font>" & "<font size='2' color='black'>" & "<ul><li>" & "Reconciliation - " & recDate1 & "</li><li>" & "Review - " & RevDate1 & "</li></ul></font>"
msgdate4 = "<font size='5'><b>" & "Reconciliation Completeness | High Risk Accounts " & "</b><br></font>"
msgdate5 = "<font size='3'><ul><li><b>High risk accounts</b></li><ul><li>" & "Reconciliation - " & recDate1 & "</li><li>" & "Review - " & RevDate1 & "</li></ul><li><b>Medium risk accounts</b><ul><li>" & "Reconciliation - " & recDate1 & "</li><li>" & "Review - " & recDate1 & "</li></ul></li><li><b>Low risk accounts</b><ul><li>" & "Reconciliation - " & recDate1 & "</li><li>" & "Review - " & recDate1 & "</li></ul></li></ul></font>"
xChartPath = ThisWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".jpg"
Application.Wait (Now() + TimeValue("00:00:01"))
xChartPath1 = ThisWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".jpg"
xPath = "<p align='Left'><img src=" / "cid:" & Mid(xChartPath, InStrRev(xChartPath, "\") + 1) & """ width=100 height=100 > <br> <br>"
xPath1 = "<p align='Left'><img src=" / "cid:" & Mid(xChartPath1, InStrRev(xChartPath1, "\") + 1) & """ width=100 height=100 > <br> <br>"
xChart.Chart.Export xChartPath
xChart1.Chart.Export xChartPath1
With xOutMail
.To = "abc@123.com"
.Subject = "High risk accounts reconciliation | Status " & dt1
.Attachments.Add Environ("USERPROFILE") & "\Documents\TrinTech_Auto\Detailed Performance PG all units- high risk_not_Completed.xlsx"
.HTMLBody = xStartMsg & xEndMsg & msgdate5 & msgdate4 & "<img src=" & "'" & xChartPath & "'>" & "<img src=" & "'" & xChartPath1 & "'></html>"
.Display
' .Send
End With
Kill xChartPath
Kill xChartPath1
Set xOutMail = Nothing
Set xOutApp = Nothing
' MsgBox ("File is attached and created the email Body with the Dashboard")
End Sub
Wednesday, 18 May 2022
Send an email through outlook including excel graphs/charts
Labels:
Excel,
Excel Macros,
MS ACCESS
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment