macro for email reminder using specific dates

[eddyrcabrera79]

I have an excel document called (Notification) and it contains several sheets, the one I will work with is the Sheet called (Updates). In that Sheet each row will contain information about clients.

1 - I would like that an automatically email reminder be sent the date shown in the column C (Reminder Date).
2 - There is a column D (Reminder Sent), I would like to see a check mark symbol on it once the email is sent.
3 - The Subject of the email it would say (Reminder)
4 - In the body of the email
Dear NAME (Column A)
This is a reminder ......
Regards,
HHHHHH

This is a general question as I'm not a Macro Savvy. In order for this emails to be sent automatically the excel doc it has to be with the extension XLSX or xlsm?

I attached an sample document

Thank you in advance

 

[chirayu]

Just modified the code from the last one I made & added If statement to check if todays date matches that in column C. You need it in .xlsm file. This is a manual run code. So you will need to open file each day & run it.

Obviously if you have reminder dates for Saturday/ Sunday & you aren't in office then mails won't go as it checks against current day

Sub AutoRemider()

Dim MySubject, MyBody, MyTo, MyWB As String

'Source: http://www.rondebruin.nl/win/s1/outlook/amail1.htm
'Modified by: Chirayu Walawalkar

MyWB = ActiveWorkbook.Name
Range("A2").Select
Do Until IsEmpty(ActiveCell)

    If Range("C" & ActiveCell.Row) = Date Then
   
        MySubject = "Reminder"
   
        MyBody = "Dear " & Range("A" & ActiveCell.Row) & _
        vbCrLf & vbCrLf & _
        "This is a Reminder for.........." & _
        vbCrLf & vbCrLf & _
        "Regards," & vbCrLf & "Eddy"
   
        MyTo = Range("B" & ActiveCell.Row)
       
        Dim OutApp As Object
        Dim OutMail As Object
   
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
   
        On Error Resume Next
        With OutMail
            .To = MyTo
            .CC = ""
            .BCC = ""
            .Subject = MySubject
            .Body = MyBody
       
            'To attach current workbook:
              '.Attachments.Add ActiveWorkbook.FullName
     
            'To attach file stored in drive
              '.Attachments.Add ("C:\test.xlsx")
         
            'To Send the mail directly
              '.Send
         
            'To show the mail & manually click send
              '.Display
       
        End With
        On Error GoTo 0
   
        Set OutMail = Nothing
        Set OutApp = Nothing
   
        Windows(MyWB).Activate
        ActiveCell.Offset(1, 0).Select
 
    Else
    End If
 
Loop

End Sub

 

[eddyrcabrera79]

Hi Chirayu,

I'm running with a few problems, maybe I'm doing something wrong.

1 - I save the document with the extension xlsm (Notification.xlsm)
2 - I pasted the macro on it and change it to .Display for now to do a test and I changed the first date to today's date for the first client Alex.
3 - when I try to save the macro, I keep getting this error message. Privacy Warning: This documents contains macro, ActiveX control, XML expansion pack information, or web component. These May include personal information that can not be removed by the Document Inspector.
4 - I closed the document and when I try to open it just to see what happen, nothing happen, its not running automatically, but if I click run the macro runs fine
5 - Is a check mark symbol going to be put in the column (Reminder Sent) for the ones that the email notification was sent?

I'm attaching my example

 

[chirayu]

Have edited to add the tick mark for sent & cross for not sent. Forgot to add it before

For Excel 2007 Version :
You can follow the following steps to avoide the privacy warning.
1 Menu Button "on the top left of the excel window"
2 Excel Option
3 Trust Center
4 Trust Center setting
5 Privacy Options
6 Untick the "Remove personal information from file properties on save"
7 Click ok

The macro is Manual. Have changed it to autorun when file is opened. Must be opened daily if you want macro to run. If you want to autorun the macro then check below links.

http://forum.chandoo.org/threads/au...ly-based-on-weekdays-and-specific-time.24035/

http://forum.chandoo.org/threads/help-with-auto-emails.24486/

 

[eddyrcabrera79]

Awesome as usual. It worked perfect. thank you so so much.

 

[eddyrcabrera79]

one more thing I notice, If I want all this always been done from the sheet called (Updates)

should I change

MyWB = ActiveWorkbook.Name
Range("A2").Select
Do Until IsEmpty(ActiveCell)

To

MyWB = ActiveWorkbook.Updates
Range("A2").Select
Do Until IsEmpty(ActiveCell)

 

[chirayu]

code will change to

MyWB = ActiveWorkbook.Name
Worksheets("Updates").Select
Range("A2").Select
Do Until IsEmpty(ActiveCell)

 

[eddyrcabrera79]

awesome it worked, thx so much