Prakash Nair
New Member
Dear Sir,
I don’t know much about VBA. But managed to copy the following code for birth day reminder mail from internet. Modified it to send item expiry date (column 5) reminder mail via Outlook to ID in Column 10. The code after sending the mail will put ”Y” in col 13 and mail details in Col 12. It works fine.
Grateful if anyone could help me to add one more logic to this code so that, after the mail is send, If the expiry date is changed to a future date, the “Y” from Col13 and mail details from Col 12 should disappear and its ready to send mail 30 days before the NEW expiry date.
The Code:
Private Sub Workbook_Open()
Dim i As Long
Dim OutApp, OutMail As Object
Dim strto, strcc, strbcc, strsub, strbody As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
For i = 6 To Range("E65536").End(xlUp).Row
If Cells(i, 13) <> "Y" Then
If Cells(i, 5) - 30 < Date Then
Set OutMail = OutApp.CreateItem(0)
strto = Cells(i, 10).Value 'email address
strsub = "item " & Cells(i, 1).Value & " is due on Due date " & Cells(i, 5).Value 'email subject
strbody = "Dear " & Cells(i, 11).Value & vbNewLine & "Please raise TQ/Action the Item: " & Cells(i, 1).Value & vbNewLine & "P/N: " & Cells(i, 2).Value & vbNewLine & "After actioning Please send a reply to ALL" & vbNewLine & " Advising action taken to avoid duplication" & vbNewLine & " ALSO on receipt of new item Change the Expiry/check dates to new dates" & vbNewLine & " And delete Y in Column M" & vbNewLine & " Then SAVE AND CLOSE" & vbNewLine & " Thanks Nd Brgds" 'email body
With OutMail
.To = strto
.Subject = strsub
.Body = strbody
.Send
'.display
End With
On Error Resume Next
Cells(i, 12) = "Mailed " & Now()
Cells(i, 13) = "Y"
End If
End If
Next
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
I don’t know much about VBA. But managed to copy the following code for birth day reminder mail from internet. Modified it to send item expiry date (column 5) reminder mail via Outlook to ID in Column 10. The code after sending the mail will put ”Y” in col 13 and mail details in Col 12. It works fine.
Grateful if anyone could help me to add one more logic to this code so that, after the mail is send, If the expiry date is changed to a future date, the “Y” from Col13 and mail details from Col 12 should disappear and its ready to send mail 30 days before the NEW expiry date.
The Code:
Private Sub Workbook_Open()
Dim i As Long
Dim OutApp, OutMail As Object
Dim strto, strcc, strbcc, strsub, strbody As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
For i = 6 To Range("E65536").End(xlUp).Row
If Cells(i, 13) <> "Y" Then
If Cells(i, 5) - 30 < Date Then
Set OutMail = OutApp.CreateItem(0)
strto = Cells(i, 10).Value 'email address
strsub = "item " & Cells(i, 1).Value & " is due on Due date " & Cells(i, 5).Value 'email subject
strbody = "Dear " & Cells(i, 11).Value & vbNewLine & "Please raise TQ/Action the Item: " & Cells(i, 1).Value & vbNewLine & "P/N: " & Cells(i, 2).Value & vbNewLine & "After actioning Please send a reply to ALL" & vbNewLine & " Advising action taken to avoid duplication" & vbNewLine & " ALSO on receipt of new item Change the Expiry/check dates to new dates" & vbNewLine & " And delete Y in Column M" & vbNewLine & " Then SAVE AND CLOSE" & vbNewLine & " Thanks Nd Brgds" 'email body
With OutMail
.To = strto
.Subject = strsub
.Body = strbody
.Send
'.display
End With
On Error Resume Next
Cells(i, 12) = "Mailed " & Now()
Cells(i, 13) = "Y"
End If
End If
Next
Set OutMail = Nothing
Set OutApp = Nothing
End Sub