Automated Emails

[kriwstal]

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!

[#2]

@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.

[#3]

@Hans Vogelaar 

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

[#4]

@Hans Vogelaar 

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

[#5]

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

[#6]

@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?

[#7]

@Hans Vogelaar 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.

[#8]

@Hans Vogelaar 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!