Forum Discussion

kriwstal's avatar
kriwstal
Copper Contributor
Jan 09, 2023

Automated Emails

Hi everyone,

 

I was able to work out how to get Microsoft Excel to send automated reminder on certain dates. However, I was wondering if I was able to specialize the email so it reminds me of a certain 'keyword' in the email that will be all different on different dates and if yes, how do I go about it?

 

This is the current VBA code that I am using.

 

Sub send_mail()
Dim OutApp As Object
Dim outmail As Object
Dim mail_list As Range
Dim cell As Range

Application.ScreenUpdating = False

Set OutApp = CreateObject("Outlook.Application")
On Error GoTo error_exit

Set mail_list = Range(Range("C9"), Range("C9").End(xlDown))

For Each cell In mail_list
Set outmail = OutApp.createitem(0)
On Error Resume Next
With outmail
.to = cell.Value
.Subject = "Reminder - Qualification about to Expire"
.body = "Hi " & Cells(cell.Row, "A").Value & "," & vbNewLine & vbNewLine & _
"There is a qualification about to expire!" & vbNewLine & vbNewLine & _
"Regards" & vbNewLine & _
"May"
.send
End With
On Error GoTo 0
Set outmail = Nothing

Next cell

error_exit:
Set OutApp = Nothing

Application.ScreenUpdating = True
End Sub

 

Thanks all!

 

developer
excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 09, 2023

    kriwstal 

    You might create a list with two columns: dates in the first column and keywords in the second column.

    You can then look up the current date in the first column, take the corresponding keyword in the second column, and insert that into the body of the message.

    • kriwstal's avatar
      kriwstal
      Copper Contributor
      Jan 10, 2023

      HansVogelaar 

      Sorry, I just have another question. How do I set the VBA code to only send the email on a specific day?

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 10, 2023

        kriwstal 

        Do you want to send it on a specific date, e.g. 10-Jan-2023, or on a specific day of the week or month?

    • kriwstal's avatar
      kriwstal
      Copper Contributor
      Jan 10, 2023

      HansVogelaar 

      Thanks for helping! I have the keyword in a different cell (F9)

      I've tried to add an extra code in the email just like the 'name value' but it doesn't work. 

       

      & Cells(cell.Row, "A").Value &

       

      This is the code that I use to bring the name across to the email.

       

      The keyword is in F9 and I am trying to add that to the body of the email.

       

      Are you able to assist with this?

       

      I am unable to get it to work as there is a compiled error.

       

      Sub send_mail()
      Dim OutApp As Object
      Dim outmail As Object
      Dim mail_list As Range
      Dim cell As Range

      Application.ScreenUpdating = False

      Set OutApp = CreateObject("Outlook.Application")
      On Error GoTo error_exit

      Set mail_list = Range(Range("C9"), Range("C9").End(xlDown))

      For Each cell In mail_list
      Set outmail = OutApp.createitem(0)
      On Error Resume Next
      With outmail
      .to = cell.Value
      .Subject = "Reminder - Qualification about to Expire"
      .body = "Hi " & Cells(cell.Row, "A").Value & "," & vbNewLine & vbNewLine & _
      "There is a qualification about to expire!" & Cells(cell.Row, "F").Value & "." & vbNewLine & vbNewLine & _

      "Regards" & vbNewLine & _
      "May"
      .send
      End With
      On Error GoTo 0
      Set outmail = Nothing

      Next cell

      error_exit:
      Set OutApp = Nothing

      Application.ScreenUpdating = True
      End Sub

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 10, 2023

        kriwstal 

        Does this work for you?

            .Body = "Hi " & Cells(cell.Row, "A").Value & "," & vbNewLine & vbNewLine & _
        "There is a qualification about to expire! " & Cells(cell.Row, "F").Value & _
        "." & vbNewLine & vbNewLine & "Regards" & vbNewLine & "May"

         

Resources