none
Need a macro code to send a reminder email before 30 days of expiration RRS feed

  • Question

  • Hi Team,

    Please i need a macro code for the following scenario.

    * My excel sheet contains 16 fields, Expiration Date field tells the expiration of software date. So i need a macro code that will popup the expiration of the particular software name and send me a reminder mail before 30 days of expiration date. The expiry date should compare with today's date.For a particular software if the expiry is more than 30 days of today's date it should automatically send a reminder mail stating " Your XXXX (Software name) is going to expire in another 30 days"

    Please help me !!!
    Friday, October 31, 2014 10:29 AM

Answers

  • Hi Sahana,

    For this requirement, you could create the reminders.

    For example:

    Dim objApt As AppointmentItem
    
     Set objApt = Application.CreateItem(olAppointmentItem)
    
     objApt.ReminderSet = True
    
     objApt.Subject = "Tuesday's meeting"
    
     objApt.ReminderMinutesBeforeStart = 30
    
     objApt.Save

    If you need to send a reminder mail, please refer to this code:

    Dim MyItem As Outlook.MailItem
    
    Set MyItem = Application.CreateItem(olMailItem)
    
    With MyItem
    
                .To = "XXX"
    
                .Subject = "subject"
    
                .SentOnBehalfOfName = "XXX"
    
                .HTMLBody = "XXX"
    
                .Importance = olImportanceHigh
    
                .FlagStatus = olFlagMarked
    
                .FlagRequest = "Follow up"
    
                .FlagDueBy = "2014/11/03" & " 14:00 AM"
    
                .ReminderTime = "2014/11/03" & " 13:01 AM"
    
                .Send
    
    End With

    To get Outlook application in excel, we could use this code below:

    Dim OutLookApp As Object
    
    Set OutLookApp = CreateObject(“Outlook.application”)

    If you want to subscript the reminder event and send mail, please refer to this code below (ThisOutlookSession module):

    Private Sub Application_Reminder(ByVal Item As Object)
      Dim objMsg As MailItem
      ' create new outgoing message
      Set objMsg = Application.CreateItem(olMailItem)
       ' your reminder notification address
      objMsg.To = "youraddress@wherever.com"
      objMsg.Subject = "Reminder: " & Item.Subject
      ' must handle all 4 types of items that can generate reminders
      Select Case Item.Class
         Case olAppointment '26
            objMsg.Body = _
              "Start: " & Item.Start & vbCrLf & _
              "End: " & Item.End & vbCrLf & _
              "Location: " & Item.Location & vbCrLf & _
              "Details: " & vbCrLf & Item.Body
         Case olContact '40
            objMsg.Body = _
              "Contact: " & Item.FullName & vbCrLf & _
              "Phone: " & Item.BusinessTelephoneNumber & vbCrLf & _
              "Contact Details: " & vbCrLf & Item.Body
          Case olMail '43
            objMsg.Body = _
              "Due: " & Item.FlagDueBy & vbCrLf & _
              "Details: " & vbCrLf & Item.Body
          Case olTask '48
            objMsg.Body = _
              "Start: " & Item.StartDate & vbCrLf & _
              "End: " & Item.DueDate & vbCrLf & _
              "Details: " & vbCrLf & Item.Body
      End Select
      ' send the message 
      objMsg.Send
      Set objMsg = Nothing
    End Sub

    There is a link about Reminiders that may benefit you:

    # Reminders Object (Outlook)

    http://msdn.microsoft.com/en-us/library/office/ff866017(v=office.15).aspx

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, November 3, 2014 8:06 AM
    Moderator

All replies

  • Hi Sahana,

    For this requirement, you could create the reminders.

    For example:

    Dim objApt As AppointmentItem
    
     Set objApt = Application.CreateItem(olAppointmentItem)
    
     objApt.ReminderSet = True
    
     objApt.Subject = "Tuesday's meeting"
    
     objApt.ReminderMinutesBeforeStart = 30
    
     objApt.Save

    If you need to send a reminder mail, please refer to this code:

    Dim MyItem As Outlook.MailItem
    
    Set MyItem = Application.CreateItem(olMailItem)
    
    With MyItem
    
                .To = "XXX"
    
                .Subject = "subject"
    
                .SentOnBehalfOfName = "XXX"
    
                .HTMLBody = "XXX"
    
                .Importance = olImportanceHigh
    
                .FlagStatus = olFlagMarked
    
                .FlagRequest = "Follow up"
    
                .FlagDueBy = "2014/11/03" & " 14:00 AM"
    
                .ReminderTime = "2014/11/03" & " 13:01 AM"
    
                .Send
    
    End With

    To get Outlook application in excel, we could use this code below:

    Dim OutLookApp As Object
    
    Set OutLookApp = CreateObject(“Outlook.application”)

    If you want to subscript the reminder event and send mail, please refer to this code below (ThisOutlookSession module):

    Private Sub Application_Reminder(ByVal Item As Object)
      Dim objMsg As MailItem
      ' create new outgoing message
      Set objMsg = Application.CreateItem(olMailItem)
       ' your reminder notification address
      objMsg.To = "youraddress@wherever.com"
      objMsg.Subject = "Reminder: " & Item.Subject
      ' must handle all 4 types of items that can generate reminders
      Select Case Item.Class
         Case olAppointment '26
            objMsg.Body = _
              "Start: " & Item.Start & vbCrLf & _
              "End: " & Item.End & vbCrLf & _
              "Location: " & Item.Location & vbCrLf & _
              "Details: " & vbCrLf & Item.Body
         Case olContact '40
            objMsg.Body = _
              "Contact: " & Item.FullName & vbCrLf & _
              "Phone: " & Item.BusinessTelephoneNumber & vbCrLf & _
              "Contact Details: " & vbCrLf & Item.Body
          Case olMail '43
            objMsg.Body = _
              "Due: " & Item.FlagDueBy & vbCrLf & _
              "Details: " & vbCrLf & Item.Body
          Case olTask '48
            objMsg.Body = _
              "Start: " & Item.StartDate & vbCrLf & _
              "End: " & Item.DueDate & vbCrLf & _
              "Details: " & vbCrLf & Item.Body
      End Select
      ' send the message 
      objMsg.Send
      Set objMsg = Nothing
    End Sub

    There is a link about Reminiders that may benefit you:

    # Reminders Object (Outlook)

    http://msdn.microsoft.com/en-us/library/office/ff866017(v=office.15).aspx

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, November 3, 2014 8:06 AM
    Moderator
  • Hi Starain,

    Thanks for your effort. But in my excel sheet i have different dates for different software's, each time when i run the macro it should compare with today's date and if there is any software which is going to expire in another 30 days it should send me an email to my outlook stating Your XXXX (Software name) is going to expire in another 30 days". The macro should also fetch the name of the software , the expiry date in excel sheet. Please help me !!

    Please find below my sample worksheet fields.

    Request ID Search Keys       Type Manufacturer   Product Name Quantity Site Licensing SME Initiate Request Expiration Date Plan Actual Budget Center Budget Account Status Comments Email

    Tuesday, November 4, 2014 7:36 AM
  • Hi Sahana,

    You need have the reminder for each record in excel. (Iterate the data in excel and create reminder)

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 5, 2014 9:47 AM
    Moderator
  • Get row n

    Check date in rowNcolWhatever

    30 days out?
    Yes - send email

    No - get next row

    Repeat until out of rows.

    Wednesday, November 5, 2014 3:26 PM