Send outlook email on date

[always_confused]

Hello,

I am trying to set up a reminder system for a duty that is to be done everyday by a different person. I have a spreadsheet that looks like this:

Name	Email	Day of month
John A	john.a@gmail.com	01
Sarah F	sarah.f@gmail.com	02
...	...	...
Mike K	mike.k@gmail.com	31

I would like to make a macro that would email everyone on the list using outlook the same message "Today is your day for ..."

I am honestly not sure where to even start. I think I could figure out looping through the list and checking who's day it is etc, but I have no idea how to set up the actual email part.

Thanks for any help or tips.

 

[JamesCanale]

I might skip excel and put this into outlook, with a button on your ribbon to run it.

Sub remindermail()

Dim myOlApp As Object
Dim MyItem As MailItem
Dim people(31, 2)
people(1, 1) = "John A": people(1, 2) = "john.a@gmail.com"
people(2, 1) = "Mary B": people(2, 2) = "maryb@gmail.com"
people(3, 1) = "Fred C": people(3, 2) = "fredc@gmail.com"
people(4, 1) = "Bill A": people(4, 2) = "billa@gmail.com"
'...
people(28, 1) = "Ted K": people(28, 2) = "tedk@gmail.com"
people(29, 1) = "Silly G": people(29, 2) = "sillyg@gmail.com"
people(30, 1) = "Harry S": people(30, 2) = "harrys@gmail.com"
people(31, 1) = "Mark K": people(31, 2) = "markk@gmail.com"

a = Day(Now())

Set myOlApp = CreateObject("Outlook.Application")
Set MyItem = myOlApp.CreateItem(olMailItem)
MyItem.BodyFormat = olFormatPlain
MyItem.Body = "Today is the day for " & people(a, 1) & ".  Have a nice day."
MyItem.Subject = "Daily Email Reminder"
MyItem.To = people(a, 2)
MyItem.Display ' use .send if you don't need to look at it
End Sub

Instead of hardcoding the list into outlook like this, you can have outlook read a csv file that you maintain in excel.

You can do the same thing from excel really, but then you'd load the file each time. Presumably, Outlook is always running and you can just push a button.

 

[always_confused]

I might skip excel and put this into outlook, with a button on your ribbon to run it.

Sub remindermail()

Dim myOlApp As Object
Dim MyItem As MailItem
Dim people(31, 2)
people(1, 1) = "John A": people(1, 2) = "john.a@gmail.com"
people(2, 1) = "Mary B": people(2, 2) = "maryb@gmail.com"
people(3, 1) = "Fred C": people(3, 2) = "fredc@gmail.com"
people(4, 1) = "Bill A": people(4, 2) = "billa@gmail.com"
'...
people(28, 1) = "Ted K": people(28, 2) = "tedk@gmail.com"
people(29, 1) = "Silly G": people(29, 2) = "sillyg@gmail.com"
people(30, 1) = "Harry S": people(30, 2) = "harrys@gmail.com"
people(31, 1) = "Mark K": people(31, 2) = "markk@gmail.com"

a = Day(Now())

Set myOlApp = CreateObject("Outlook.Application")
Set MyItem = myOlApp.CreateItem(olMailItem)
MyItem.BodyFormat = olFormatPlain
MyItem.Body = "Today is the day for " & people(a, 1) & ".  Have a nice day."
MyItem.Subject = "Daily Email Reminder"
MyItem.To = people(a, 2)
MyItem.Display ' use .send if you don't need to look at it
End Sub

Instead of hardcoding the list into outlook like this, you can have outlook read a csv file that you maintain in excel.

You can do the same thing from excel really, but then you'd load the file each time. Presumably, Outlook is always running and you can just push a button.

This is a great solution, thank you very much