VBA code to select the dates and put it in the subject line of the email

[Switto]

Dear All,
I have 3 macros to send emails from the excel. The purposeof the macro is to select the dates in the raw file , which is selected by theuser and send the email.
1^st macro does the selection of the datesaccording to the user input.
2^nd macro does the sending of the emails (where Irequire help)
3^rd macro sends the reminder email.
----
1^st macro creates a different sheet and put allthe selected dates and dates would be highlighted with yellow colour i.e
[TABLE="width: 463"]
<tbody>[TR]
[TD="width: 176"]

Receive Information From The Reviewee​

[/TD]
[TD="width: 135"]

Send Second Request (Reminder) To The Reviewee (If No Response Is Received)​

[/TD]
[TD="width: 132"]

Send Final Request (Reminder) To The Reviewee (If No Response Is Received)​

[/TD]
[TD="width: 175"]

Set-up QMT And Begins Procedural Review​

[/TD]
[/TR]
[TR]
[TD="width: 176"]

30-Jul-18​

[/TD]
[TD="width: 135"]

30-Jul-18​

[/TD]
[TD="width: 132"]

30-Jul-18​

[/TD]
[TD="width: 175"]

16-Dec-18​

[/TD]
[/TR]
[TR]
[TD="width: 176"]

30-Jul-18​

[/TD]
[TD="width: 135"]

30-Jul-18​

[/TD]
[TD="width: 132"]

30-Jul-18​

[/TD]
[TD="width: 175"]

30-Jul-18​

[/TD]
[/TR]
[TR]
[TD="width: 176"]

15-Dec-18​

[/TD]
[TD="width: 135"]

30-Jul-18​

[/TD]
[TD="width: 132"]

30-Jul-18​

[/TD]
[TD="width: 175"]

30-Jul-18​

[/TD]
[/TR]
</tbody>[/TABLE]

In the same sheet, in column AA all the dates will be put ina row by the 1^st macro.i.e
[TABLE="width: 77"]
<tbody>[TR]
[TD="width: 104, bgcolor: transparent"]

15-12-2018​

[/TD]
[/TR]
[TR]
[TD="width: 104, bgcolor: transparent"]

16-12-2018​

[/TD]
[/TR]
</tbody>[/TABLE]

While sending the email, I would like to copy the headercolumn and paste into the subject line.i.e.
[TABLE="width: 127"]
<tbody>[TR]
[TD="width: 169"]

“Receive Information From The Reviewee”​

(the above said line should come in the sub. Line)​

[/TD]
[/TR]
[TR]
[TD="width: 169"]

30-Jul-18​

[/TD]
[/TR]
[TR]
[TD="width: 169"]

30-Jul-18​

[/TD]
[/TR]
[TR]
[TD="width: 169"]

15-Dec-18​

[/TD]
[/TR]
</tbody>[/TABLE]

As the dates would not be static, hence we are not able todevelop a code.
Warm Regards
Switto

 

[Swayzy]

So here you have simple sub for sending a mail.
By the information given i would suggest that you trigger the code by a hotkey command and that instead of my defined ranges you use activecell.offset() to define the subject, body text etc. Then you can just select a row and it will use that rows values.

You should be able to modify the code as needed.

Sub sendmail()
Application.ScreenUpdating = False
Dim file As String


    esubject1 = Range("esubject")
    sendto1 = Range("sendto")
    ebody1 = Range("ebody")
    ccto1 = Range("ccto")
    file1 = Range("SendFile")
    
    Set app = CreateObject("Outlook.Application")
    Set itm = app.CreateItem(0)


    With itm
      
If Range("shwBfrSend") = "Yes" Then
.Display
End If
    .Subject = esubject1
    .To = sendto1
    .cc = ccto1
    .Body = ebody1
    .Attachments.Add file1
    


    MSG1 = MsgBox("Check mail and press Yes to send", vbYesNo)


    If MSG1 = vbYes Then
    .send
    End If

    End With
    Set app = Nothing
    Set itm = Nothing

Application.ScreenUpdating = True
End Sub

 

[Switto]

@ Swayzy: Thanks for the code. Let me check it and let you know the result