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

Receive Information From The Reviewee​	Send Second Request (Reminder) To The Reviewee (If No Response Is Received)​	Send Final Request (Reminder) To The Reviewee (If No Response Is Received)​	Set-up QMT And Begins Procedural Review​
30-Jul-18​	30-Jul-18​	30-Jul-18​	16-Dec-18​
30-Jul-18​	30-Jul-18​	30-Jul-18​	30-Jul-18​
15-Dec-18​	30-Jul-18​	30-Jul-18​	30-Jul-18​

<tbody> </tbody>

In the same sheet, in column AA all the dates will be put ina row by the 1^st macro.i.e

15-12-2018​

16-12-2018​

<tbody> </tbody>

While sending the email, I would like to copy the headercolumn and paste into the subject line.i.e.

“Receive Information From The Reviewee”​ (the above said line should come in the sub. Line)​

30-Jul-18​

30-Jul-18​

15-Dec-18​

<tbody> </tbody>

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