Forum Discussion
Automated Emails
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.
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
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"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!