Excel Macro to Send Mail to save sent mails rows to Sheet Dynamically

[Ben AFF]

Hi, I have a macro that upon selecting certain rows in a sheet sends a mail to various recipients.
Please can you help me modify the code so it also creates a single sheet with the selected rows for which a mail is sent and adds news rows to it when ever the macro is actioned?
Thank you.

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
For Each r In Selection
SendToMail = Range("A" & r.Row)
MailSubject = Range("J" & r.Row)
mMailBody = Range("K" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.Subject = MailSubject
.Body = mMailBody
.Send
End With
Next r
End Sub

 

[Ben AFF]

I have managed to copy the selected rows into a worksheet. Solved

 

[Fluff]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[Ben AFF]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Sure thing, here is the code that works. The only thing is that is that I want to add the time stamp of the date the paste is done. Please can you help me? Thank you.

Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
For Each r In Selection
Selection.Copy
ThisWorkbook.Worksheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
SendToMail = Range("A" & r.Row)
MailSubject = Range("J" & r.Row)
mMailBody = Range("K" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.Subject = MailSubject
.Body = mMailBody
.Display
End With
Next r
MsgBox "Success"
End Sub