Create Macro to Send Email in Excel

[buckeyepilot67]

Alright, I have a problem I am trying to solve. I have an excel spreadsheet with emails and dates in it. If the date in the spreadsheet is less than the current date, I want an email to be sent to the email address in the database. I know how to get an email to send, but I do not know how to set the criteria up to send the email. Any help would be greatly appreciated.

 

[SkipVought]

hi,

There needs to be an event that starts a procedure that tests the dates in your sheet. Do you want that event to be someone pushing a button or some other event like when the workbook opens, or some other event?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

I'm guessing (though only you can tell us) that you want a button to kick this off.

Here's how I'd prefer to set it up... in at least 3 code pieces (by the way, being this is a code question, this would be best discussed in forum707.

So here's what I'd do:
1. Create a function that tests a value for whether it's a valid date for your scenario:

If the date in the spreadsheet is less than the current date

So the code would be like this:

Function ValidEmailDate(InputDate As Date) As Boolean
   If InputDate < Today() Then
       ValidEmailDate = False
   Else
       ValidEmailDate = True
   End If
End Function

2. Create a procedure that sends the email for each address (assuming you don't want to combine them all) - how you do this will depend upon personal preference somewhat, since there is more than one way to send emails. Call it something like SendEmail

3. Create a final procedure that loops through your records/rows, and emails those that are valid, and perhaps you want it to log whether sent or not - for that, you could also create another separate procedure or two.

So the final code might look something like:

Private Sub SendEmails
[green]'1. Loop through the records - You started in Excel, but wound up mentioning a database, so not sure if this is Excel or Access or what[/green]

[green]'2. For each record, look at the date, and if is valid, send the email[/green]
   If ValidEmailDate(MyDateField) Then 
      SendEmail
      LogSuccess
   Else
      LogFailure
   End If

[ol 1]
[li]But it would be good to provide some clarified information as Skip pointed out, such as:[/li]
[li]What application is doing the sending? Excel or Access[/li]
[li]Where is the data?
[ol a]
[li]in an Excel Workbook? If so, can the workbook change, or is it always the same? Do you want to be able to run the same code in "any" workbook opened, or just one specific workbook?[/li]
[li]in an Access Database, linked to Excel, or else referenced by a userform in Access?[/li]
[/ol][/li]
[li]What version of Office are you using?[/li]
[li]What do you have in place so far? What have you tried?[/li]
[li]How do you want the process to kick off? Via a button, off a schedule, what?[/li]
[/ol]




"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[buckeyepilot67]

thank you all for the help. It was in excel. Sorry about that. I was able to get it figured out by creating an extra column in my workseeht and refrenced that to kick off the email. Thank again.