VBA auto reminder email

[ashani]

hi,

I wonder if someone could guide me please.

In my workbook I would like to setup an automatic email reminder in the following sequence :

- Email address is in cell a2
- if cell b2 shows "Outstanding" than reminder goes out to the user every other day.

many thanks

 

[ashani]

hi experts,
any idea on this one please?
thank you

 

[Logit]

Option Explicit

Sub Mail_workbook_Outlook()
'& vbNewLine & vbNewLine & _

    Dim OutApp As Object
    Dim OutMail As Object
    Dim sndEmail As String
        
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
        
        'establish TO email address
        sndEmail = Sheets("Sheet1").Range("A2").Value
                
        'On Error Resume Next
        
        'If these two conditions exist, send email ... otherwise ignore
        If Sheet1.Range("B2").Value = "Outstanding" And Sheet1.Range("C2").Value = Date Then
            
            With OutMail
            
                .To = sndEmail
                .CC = ""
                .BCC = ""
                .Subject = "Outstanding"
                .Body = "Dear colleague," & vbNewLine & vbNewLine & _
                        "We are happy to inform you that ..." & vbNewLine & vbNewLine & _
                        "Sincerely," & vbNewLine & _
                        "John Smith"
                        
                .Display                                'Display email prior to sending
                Sheet1.Range("C2").Value = Date + 2     'Change displayed date to next "every other day"
                
            End With
        
        End If
    
        On Error GoTo 0
    
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Download workbook : WORKS - Email Every Other Day.xlsm