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
Bookmarks