Template & Attachment - Send with Excel Macro

Good time of day, amitbhatt!

I've solved very similar task on Russian forum. In the attachment you will find macro which allow you to form email via Excel with pictures and attachments. Code is nicely commented, so it is almost easy to modify for your needs.

Hope this sample will help. Please ask if you have additional questions.

Sub Send_Email()

'From http://vba-useful.blogspot.ru/2014/01/send-html-email-with-embedded-images.html
'Reorganized and edited by Roman Rioran Voronov at 30.06.2014 | voronov_rv@mail.ru

With Application 'Shutting down some graphical options to increase programm speed
    .Calculation = xlManual
    .ScreenUpdating = False
    .EnableEvents = False
End With

Dim OutApp As Object 'To call Outlook Application
Dim OutMail As Object 'To work with a new Outlook message
Dim TempFilePath As String 'Path to store objects

TempFilePath = Environ$("temp") & "\"

Set OutApp = CreateObject("outlook.application") 'Create a new Microsoft Outlook session
Set OutMail = OutApp.CreateItem(0) 'Create a new message

With OutMail

    .Subject = "Call-Center Daily Report" 'So called (by myself) head of letter.
    .To = ThisWorkbook.Sheets(1).Cells(18, 1).Value 'Who will receive our message.

    'First text part of the message.
    .HTMLBody = "<span LANG=EN>" & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
        & "Hello, Mr. Scrooge McDuck!<BR><BR>Please be informed about our yeasturdays success.<BR>Call-center was operating as it shown below:<BR>"
        
    '== Insert an object 1 ==
    Call Get_Pic("Graphics") 'Time to create the image as a JPG file
    .Attachments.Add TempFilePath & "Graphics.jpg", 0, 0
    
    'Let us combine object 1 with a body of message
    .HTMLBody = .HTMLBody & "<BR>" & "<img src='cid:Graphics.jpg'" & "<BR>" _
        & "<BR>And here are details with used numbers down below:<BR>"
        
    '== Insert an object 2 ==
    Call Get_Txt("Details") 'Time to create the image as a JPG file
    .Attachments.Add TempFilePath & "Details.jpg", 0, 0

    'Let us combine object 2 with a body of message
    .HTMLBody = .HTMLBody & "<BR>" & "<img src='cid:Details.jpg'" & "<BR>" _
        & "<BR>" & ThisWorkbook.Sheets(1).Cells(21, 1).Value & "<BR><BR><BR><B>Best Regards,<BR>Roman Rioran Voronov</B></font></span>"
    
    .Display 'Choose .Send if you want message to be sended automatically without displaing
    
End With

Set OutApp = Nothing
Set OutMail = Nothing

With Application 'Turning graphical options back
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub
    
Sub Get_Pic(NameFile As String)

Dim PlaceX As Shape

ThisWorkbook.Worksheets(1).Activate 'Selecting sheet with future screenshot

Set PlaceX = ThisWorkbook.Worksheets(1).Shapes(1) 'Selecting exact place to catch
PlaceX.CopyPicture

With ThisWorkbook.Worksheets(1).ChartObjects.Add(PlaceX.Left, PlaceX.Top, PlaceX.Width, PlaceX.Height)
    .Activate
    .Chart.Paste
    .Chart.Export Environ$("temp") & "\" & NameFile & ".jpg", "JPG"
End With

Worksheets(1).ChartObjects(Worksheets(1).ChartObjects.Count).Delete
    
Set PlaceX = Nothing

End Sub

Sub Get_Txt(NameFile As String)

Dim PlaceY As Range

ThisWorkbook.Worksheets(1).Activate 'Selecting sheet with future screenshot

Set PlaceY = ThisWorkbook.Worksheets(1).Range("A1:D9") 'Selecting exact place to catch
PlaceY.CopyPicture

With ThisWorkbook.Worksheets(1).ChartObjects.Add(PlaceY.Left, PlaceY.Top, PlaceY.Width, PlaceY.Height)
    .Activate
    .Chart.Paste
    .Chart.Export Environ$("temp") & "\" & NameFile & ".jpg", "JPG"
End With

Worksheets(1).ChartObjects(Worksheets(1).ChartObjects.Count).Delete
    
Set PlaceY = Nothing

End Sub