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 ce...
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 I would like to send it on a specific day but all the days will be different. I was trying to put the days in a cell just like the keyword but unsure how to get the email sent on that specific day.

  • 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"

       

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

        HansVogelaar Yes! It did. I played around with it a bit more after that post. Can’t say what I did because even I don’t know how I fixed it but the error stopped showing and the email was tailored with the keyword which was awesome! Thanks Han!

Resources