1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

how to send reminder date from excel to outlook email

Discussion in 'Business Applications' started by sonu313131, Mar 14, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. sonu313131

    sonu313131 Thread Starter

    Joined:
    Mar 14, 2016
    Messages:
    3
    hi !
    I have a spread sheet of 100 of employees , i like every time the expiry date come for there id a notification email come to me , i attach the example excel sheet please help me with that, i am just learning VBA not very good in it i am using windows 8
     

    Attached Files:

  2. Sponsor

  3. sonu313131

    sonu313131 Thread Starter

    Joined:
    Mar 14, 2016
    Messages:
    3
    any one can help me with micro
     
  4. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,342
  5. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    Here is a possible solution. You can change the .Display to .Send to automatically send the email without displaying it

    The question I have is, what triggers this event to occur?

    1) does it trigger when you open the workbook?
    2) does it trigger after a specified amount of time (say every few hours)?
    3) or is it manually triggered?

    anyways, you should be able to modify this to suite.

    Dim uRange
    Dim lRange
    Dim BCell As Range
    Dim EmailTo As String
    Dim EmailBody As String
    Dim EmailSubject As String
    Dim EmailString As String

    Sub CheckExpiry()

    Set uRange = Range("B2")
    Set lRange = Range("B" & Rows.Count).End(xlUp)

    EmailTo = Empty
    EmailSubject = Empty
    EmailSubject = Empty
    EmailBody = Empty
    EmailString = Empty

    For Each BCell In Range(uRange, lRange)

    If DateDiff("d", Format(Now(), "dd/mm/yyyy"), BCell.Value) <= 0 Then

    EmailString = EmailString & BCell.Offset(0, -1) & " " & BCell.Value & vbCrLf

    End If

    Next BCell

    EmailBody = "Hello," & vbCrLf & vbCrLf & "The following employees contract has expired" & vbCrLf & vbCrLf & EmailString
    EmailTo = "[email protected]"
    EmailSubject = "Expiry Dates"

    SendReminderMail
    End Sub

    Sub SendReminderMail()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
    .To = EmailTo
    .CC = ""
    .BCC = ""
    .Subject = EmailSubject
    .Body = EmailBody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Display 'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    End Sub
     
  6. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,342
    @sonu313131: did you check out the link I attached?
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1167957