VBA Macro to find last col. and paste data

[Tom22]

Hi Guys,

I am looking for suggestion on how to automate my HR file.

We have daily meetings and we maintain a log, who is the presenter and what he/she presents.
After meeting if any one has any questions, he/she goes to this log (Saved on share drive) and post question/s.

He/she then send a email to the group stating that question has been added, then presenter will go in the log, answer it and again send a mail to group, mentioning Question has been answered.

I want something in the file itself, where instead of sending emails manually, they just click on the 2 buttons and mail sent to the entire group, mentioning question or answer has been posted.

According to me, code should find out last fill cell in col. C and then copy & paste whatever mentioned in col. C, D and E in subject line , So subject line should look like "Question has been raised for topic XXXXX YYYY AAAA) and same goes for whenever answer has been added " Answer has been added for topic XXXX YYYY AAAA)


Attaching one sample email, where people will post their questions/ Answers.

 

[RDAngelo]

Add the email addresses and attach the code snippets to the appropriate buttons...

Sub Question()
    Dim lRow As Long
    lRow = ActiveSheet.UsedRange.Columns("C").Find("*", searchdirection:=xlPrevious).Row
    With CreateObject("Outlook.Application").CreateItem(0)
        .Display
        .To = ""
        .Subject = "Question has been raised for topic " & _
                    Cells(lRow, 3).Value & " " & _
                    Cells(lRow, 4).Value & " " & _
                    Cells(lRow, 5).Value
    End With
End Sub

Sub Answer()
    Dim lRow As Long
    lRow = ActiveSheet.UsedRange.Columns("C").Find("*", searchdirection:=xlPrevious).Row
    With CreateObject("Outlook.Application").CreateItem(0)
        .Display
        .To = ""
        .Subject = "Answer has been provided for topic " & _
                    Cells(lRow, 3).Value & " " & _
                    Cells(lRow, 4).Value & " " & _
                    Cells(lRow, 5).Value
    End With
End Sub