Loop - Add Excel File as an Attachment and also Paste in Email Body in outlook

[jenitak87]

I have a basic macro, where different files can be attached to send a mail

Where i am stuck is , if there is a possibility to copy that excel in E-mail body as well

Can you help tweak below Code, My destination path is in path_ = cell.Offset(1, 8).Value

The attachment only has table , the path is in the last columns,

Based on the path how can I attached as well as copy the range and paste in the EMAIL body

Sub SendEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim email_ As String
Dim email2_ As String
Dim cc As String
Dim subject_ As String
Dim body_ As String
Dim path_ As String
Dim intX As String
Dim intY As String
Dim intZ As String
Dim Body
Set mainWB = ActiveWorkbook



Sheets("Duplicates").Select
Range("A1").Select

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

' Loop through the rows
For Each cell In Columns("a").Cells.SpecialCells(xlCellTypeConstants)

email_ = cell.Offset(1, 4).Value
email2_ = cell.Offset(1, 5).Value
cc = cell.Offset(1, 6).Value
subject_ = cell.Offset(1, 7).Value
path_ = cell.Offset(1, 8).Value



'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email_ & ";" & email2_
.cc = cc
.Subject = subject_
.htmlbody = "Dear All," & "</p>" _
& "<br>" _
& "Please find updated statement of accounts for your reference and payment persual.<br>" _
& "<br>" _
& "Kindly review all Line items and confirm payments whichs needs to be settled before the end of the month.<br>" _
& "<br>" _
& "Kindly feedback on status of past due invoice and do let me know shall you require anything else.<br>" _
& "<br>" _
& "<br>" _
& "<br>" _
& "Thanks and Regards,<br>" _
& "XXXXX<br>" _
& "XXXXX,<br>" _
& "XXXXX<br>" _
& "XXXXX<br>" _
& "XXXXX<br>" _
& "XXXXX<br>" _


.Attachments.Add path_

.DeleteAfterSubmit = False
.Send
End With
Next
End Sub

 

[John_w]

See Paste excel range into e-mail as JPEG (VBA)

Please use VBA code tags - the VBA icon on the message editor toolbar.