Notifications from Excel

Hello,

I'm working with a excel spreadsheet of current contracts our company has with providers. Basically, I need to set up a system where I am notified when a current contract is ending (approximately 6 months prior to an end) so that we can reach out to the provider and possibly re-negotiate. We work with Outlook so I'm sure an e-mail notification would be okay, unless anyone can suggest a better process for being notified. So my questions are:

1. What would be the best process in Excel for receiving notifications for contract endings?
2. How do I set this process up?

Thanks,
-six7pab

Almir's picture

Notifications from Excel

Simple solution would be to use conditional formatting, in a way that contracts expiring in 6 months or less from current date are marked in different color in Excel. Is this good enough or you need e-mail notification?

I believe that Outlook e-mail

I believe that Outlook e-mail notifications would be most beneficial. This way, I can set up a distribution group that prompts my whole team when a contract expires and not just me who manages the tracker. I will use the conditional formatting in the tracker as well.

How can we set up e-mail notifications from Excel?

Almir's picture

Hw to set-up e-mail notification from Excel?

I guess Nick can help.

Sending email from Excel

Following is a basic example of sending Outlook email direct from Excel.
The sRecipients string is a list of email addresses separated by semi colons.
SCC is a string of BCC recipients.

Sub Send_Outlook_E_Mail()

Dim oOutlook As New Outlook.Application
Dim oNameSpace As Outlook.Namespace
Dim OMailitem As Outlook.MailItem
Dim SRecipients As String, SCC As String

SRecipients = ThisWorkbook.Sheets("Email").Range("C75").Value
SCC = ThisWorkbook.Sheets("Email").Range("C76").Value

Set oNameSpace = oOutlook.GetNamespace("MAPI")
oNameSpace.Logon '
Set OMailitem = oOutlook.CreateItem(olMailItem)
With OMailitem
.Subject = "Contract expiry...... etc"
.Recipients.Add SRecipients
.CC = SCC
.Body = "Contract expiring ...... etc"
'.SenderName
.display
'.Send
End With
oNameSpace.Logoff
Set oNameSpace = Nothing

End Sub

Almir's picture

Sorry, I forgot Andy

Sorry, I forgot Andy. Thank you.

:-)

No problem Almir :-)... Glad to be of help

Thanks everyone! Very

Thanks everyone! Very helpful!