Outlook Email from Excel based on date in Cell

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

JNoji

Thread Starter
Joined
May 11, 2018
Messages
5
Hello,

I have seen posts similar to my requests but have been unable to put together a code to do everything I need it to do as I am very new to VBA coding.

I am looking for a VBA code that will generate an email when the date in a cell of a column is 7 days or less from the current date. The email will be sent to a recipient listed in another cell column and cc my coworker. I also need a code to change the data in another cell to "Approve, Email Sent" when the same cell date is between 7 and 1 days from the current date and "Expired, Email Sent" when the date equals the current date so that an email will only send if this cell does not equal "Approved, Email Sent" or "Expired, Email Sent" so that emails are not repeatedly sent every time the spreadsheet is opened. I plan on running task scheduler to open the spreadsheet every Monday so the code will run weekly at minimum.

Any help is greatly appreciated, thank you!
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
Hi, welcome to the forum.
Do you have VBA knwoledge?

This module will take care of the mailing part (using Outlook)

All you need to do is write the code for your selection (which you explained, so it shouldn't be that difficult) and then pass it to the Mailing module

Code:
Option Explicit

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, Sendto As String, CCto As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)

With Itm
    .Subject = mSubject
    .To = Sendto
    If Not IsMissing(CCto) Then .CC = CCto
    .Body = mMessage
'     .Attachments.Add (NewFileName1) ' Must be complete path
    .Display ' This property is used when you want
'    .send    ' This property sends the mail directly
'    .Save   ' this property issued to save it as Draft for later processing, 
End With
Set app = Nothing
Set Itm = Nothing
End Function
 

JNoji

Thread Starter
Joined
May 11, 2018
Messages
5
Hello Keebellah, I appreciate the response. I am very new to VBA and I am struggling putting together the code to perform the functions I explained. This is my first attempt at VBA coding.
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
You've chosen a not too easy way .
VBA is nothing more than logical thinking with a very basic and necessary requirement, well, at least two basic requirements, namely Time (very much of it) and Imagination (as much as possible)
Apart from that it's no more than a series of actions that are executed one after the other with conditions and calculations.
If you write down the actions you want done in words like you would a book then all you need to do after that is 'translate' it into (VBA) code respecting the syntax and you can go a long way with that.

Can you attach a file with the columns the way you decided with some dummy data in the cells.
I'll see if I can help you on the way. A ready-made solution is great and I will try and provide one but trying it yourself will make you less dependent of others and you can modify it to your liking and changing requirements
Believe me, once you've started you will think up more possibilities :)
 

JNoji

Thread Starter
Joined
May 11, 2018
Messages
5
Ok I have attached a sample file, so I want the email to be generated if the "approval expiration" date in column F is approaching in the next 7 days or less. Also if it is approaching in the 7 days or less I want to change the "Approval Status" in column C to "expiring, survey sent".

I appreciate the help, I just discovered VBA and can imagine all the possibilities once I am adequate at coding.

Thanks again!
 

Attachments

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
And how do you want to trigger it, not automatically I guess
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
Okay try this one, I placed a button to trigger it.
It will only Display the mail message not send it you can change that in the mail code
If you want to trigger Excel using the scheduler then invoke the macro SENDMAIL_Click, that way it is only triggered through the scheduler and not when you open the file to correct or check
You will have to pass the subject line, maybe some message and the coworker address

I'm off to bed here.
 

Attachments

JNoji

Thread Starter
Joined
May 11, 2018
Messages
5
I just tried it, looks like it runs perfect to what I need it to do. I just need to test it with the actual file. I wanted it to trigger when the workbook is opened which was why I wanted it to change the "Approval Status" cell to "Approved, email sent" so we can use that data as a trigger to not generate an email, and the same with "Expired, email sent"

I appreciate the help, you have been amazing!
 

JNoji

Thread Starter
Joined
May 11, 2018
Messages
5
I was going to use the task scheduler to open the workbook every monday to trigger the macro. I was also hoping to add an attachment to the email if possible.
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
You can include the attachment too, see the mail macro and add a parameter for the attachment, the filename passed to the macro must be the fullname, path and filename

The Bold red lines are new and you will have to add these to the existing macro named MailData

I see they do not show up as Bold Red in the code section
I have highlighted here what you need to add to the vba code (or copy the code in the code section
, Optional NewFileName1 As string

if len(trim(NewFileName1))>0 then

Code:
Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String, Optional NewFileName1 As string)
Dim eSubject As String, Sendto As String, CCto As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)

With Itm
   .Subject = mSubject
   .To = Sendto
   If Not IsMissing(CCto) Then .CC = CCto
   .Body = mMessage
if len(trim(NewFileName1))>0 then   .Attachments.Add (NewFileName1)    ' Must be complete path
   .Display ' This property is used when you want
'    .send    ' This property sends the mail directly
'    .Save   ' this property issued to save it as Draft for later processing,
End With
Set app = Nothing
Set Itm = Nothing
End Function
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top