Thursday, December 14, 2017

Excel VBA to send email from excel with HTML table in a email body

'' Excel vba script to send email from outlook & email body in HTML table format.
''Write down email body in HTML tags.

Sub Send_StatusDetails()

mlBody = ""
On Error Resume Next
Set OtlApp = GetObject(, "Outlook.application") 'gives error 429 if outlook is not open
If Err.Number = 429 Then
    Err.Clear
    Shell "outlook.exe", vbMaximizedFocus    ''''''''''''''''''''''' open outlook '''''''''''''
    Application.Wait Now + TimeValue("00:00:10")              ''''''''''''' wait for 10 seconds until all the emails will be loaded '''''
    Set OtlApp = GetObject("", "outlook.application")
End If
   
Set OtlMail = OtlApp.createitem(0)
With OtlMail
    .To = "your email address"
    .Subject = "summary details"
     mlBody = "<table border='1' style='font-size:12'>" & vbCrLf & "<tr><th>Table Name</th><th>Upload Date</th><th>Data Updated</th></tr>" & vbCrLf
     For i = 1 To UBound(statArr)
        If statArr(i, 1) = vbNullString Then Exit For
        mlBody = mlBody & "<tr><td>" & statArr(i, 1) & "</td><td>" & statArr(i, 2) & "</td><td>" & statArr(i, 3) & "</td></tr>" & vbCrLf
     Next
    .htmlbody = mlBody & "</table><br><br> Thanks <br>"
    .display
    .send
End With

No comments:

Post a Comment

Excel VBA to send email from excel with HTML table in a email body

'' Excel vba script to send email from outlook & email body in HTML table format. ''Write down email body in HTML tags...