vba - Automation email in Excel

Question

I am trying to design an excel file that will help my company with recruitment.

The task is to create a sheet for candidates that the company speaks to, we will record all the records for the candidates including their first name, last name, mobile and email address. You can see a screenshot of how everything looks here: https://imgur.com/gallery/tvAIx

As you can see there are columns for when the company speaks to the candidate and when he sends his CV to us. At the end there is also a "CV reminders" column. It has the following code =IF(ISBLANK(F2), HYPERLINK("mailto:" & D2 & "?subject=" & $O$3 & "&body=" & $P$3, "Send reminder"), "All good")

The idea is so that if CV has not been received yet, you can press the cell and it will generate a reminder email for the candidate. I want to make all the process autonomous so that it can pick out the candidate name from the relevant cell and send him a generic email like:

"Hi name from cell,

Hope you are well.

We have spoken with you on date from cell. Have you had a chance to review your CV yet? Do you have any questions?"

I am sure it is possible with VBA just don't know how. Thank you.

Answer 1

You should be able to handle basic VBA usage in order to achieve this.

Below is the VBA code that sends an Outlook e-mail message for Office 2000-2016. Source is http://www.rondebruin.nl

You may put the code in the SelectionChange event of the requested cell(s) and change the Body, SendTo etc. portions according to your needs. (Appearently in your case, SendTo address and some parts of Body will come from particular cells on the row of your selected cell)

Sub Mail_small_Text_Outlook()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:	est.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub