Automatically sending email based on Excel values:

I'm working on a solution for my work, who is trying to track the licensing of medical professionals and have a fool-proof, centralized system for tracking these people from multiple sites. I've already designed an Excel spreadsheet that shows the individuals and calculates a "X days until renewal" value. I'm trying to write a script that will automatically pull these values and email the proper parties when they get within 60 days of their renewal. Can anyone help? I've played with various VBA scripts that I've seen online, but I'm not having the results I want.

Re: Automatically sending email based on Excel val

Sure! Here's a real basic layout of what I'm doing. Not complex in the least. When the data hits certain parameters (here, I've set it to <30 days), conditional formatting will kick in to draw attention to those rows. However, I also want to write a script for Outlook that will check this Excel spreadsheet and email the people in column A regarding the number of days left until the licenses need renewal.

It should work with any version of Excel and any e-mail client. See the attached version of the sample workbook. I have placed a button on the worksheet (from the Forms toolbar) that will call the macro.

Re: Automatically sending email based on Excel val

Thanks, but is there a more automated way to do this? I basically want a macro in Outlook to check the spreadsheet without a human having to open Excel, and automatically email the people involved. Is this possible?

Re: Automatically sending email based on Excel val

I'm not sure that running code from Outlook is a good idea. Someone will have to run the macro anyway, unless you create some kind of scheduled task that runs at specified times.

Here is a version (still to be run from the workbook) that will send the e-mails through Outlook. You'll run into Outlook's security guard, there are several ways to handle this, see <post:=488,173>post 488,173</post:>.

Sub SendMail()
Dim r As Long
Dim m As Long
Dim objOL As Object
Dim objMsg As Object
Dim blnStart As Boolean

On Error Resume Next

Set objOL = GetObject(, "Outlook.Application")
If objOL Is Nothing Then
Set objOL = CreateObject("Outlook.Application")
If objOL Is Nothing Then
MsgBox "Can't start Outlook", vbExclamation
Exit Sub
End If
blnStart = True
End If

Re: Automatically sending email based on Excel val

Great, as an autorun that works perfectly. My only problem at this point then is that I'm looking for a way to lock this macro so it only works when certain people open the file, and I also don't want it to email people about the same row over and over again. Any ideas there? Thanks!