Want to setup on automatic daily schedule. Ok, to have VBA script in Power Automate Desktop flow but if not necessary that is fine too. Below is the code I am starting with from another post and my example table. Any help would be great! Thanks
Sub send_mail()
Dim xdate As Date
xdate = Worksheets("Quoted Prospects").Range(Range("G2").End(xlDown), Range("J2").End(xlDown))
Dim OutApp As Object
Dim outmail As Object
Dim mail_list As Range
Dim cell As Range
Application.ScreenUpdating = False
On Error GoTo error_exit
Set mail_list = Range(Range("K2"), Range("K2").End(xlDown))
For Each cell In mail_list
Set outmail = OutApp.createitem(0)
On Error Resume Next
With outmail
.to = cell.Value
.Subject = "Quote Followup"
.body = "Hi " & Cells(cell.Row, "B").Value & "," & vbNewLine & vbNewLine & _
"Now that you have had time to review the Quote, let's get you going with your new policy/s. What would be the best time to call to discuss?" & vbNewLine & vbNewLine & _
"Regards," & vbNewLine & _
"Patrick"
.send
End With
On Error GoTo 0
Set outmail = Nothing
Next cell
error exit:
End Sub
Full Name | First Name | Last Name | Status | Type | Quote Date | 3rd Day | 6th Day | 30th Day | 140th Day | |
Matt Grandbury | Matt | Grandbury | No | Home | 5/29/2021 | 6/1/2021 | 6/4/2021 | 6/28/2021 | 10/16/2021 | somebody@gmail.com |
Patrick Bisket | Patrick | Bisket | No | Auto | 5/30/2021 | 6/2/2021 | 6/5/2021 | 6/29/2021 | 10/17/2021 | somebody@yahoo.com |
Gilda Winters | Gilda | Winters | No | Auto | 5/30/2021 | 6/2/2021 | 6/5/2021 | 6/29/2021 | 10/17/2021 | somebody@aol.com |
Gilda Winters | Gilda | Winters | Yes | Ten | 5/30/2021 | 6/2/2021 | 6/5/2021 | 6/29/2021 | 10/17/2021 | somebody@hotmail.com |
Sub send_mail()
Dim xdate As Date
xdate = Worksheets("Quoted Prospects").Range(Range("G2").End(xlDown), Range("J2").End(xlDown))
Dim OutApp As Object
Dim outmail As Object
Dim mail_list As Range
Dim cell As Range
Application.ScreenUpdating = False
On Error GoTo error_exit
Set mail_list = Range(Range("K2"), Range("K2").End(xlDown))
For Each cell In mail_list
Set outmail = OutApp.createitem(0)
On Error Resume Next
With outmail
.to = cell.Value
.Subject = "Quote Followup"
.body = "Hi " & Cells(cell.Row, "B").Value & "," & vbNewLine & vbNewLine & _
"Now that you have had time to review the Quote, let's get you going with your new policy/s. What would be the best time to call to discuss?" & vbNewLine & vbNewLine & _
"Regards," & vbNewLine & _
"Patrick"
.send
End With
On Error GoTo 0
Set outmail = Nothing
Next cell
error exit:
End Sub