Auto Emails through VBA on Specific Date

Hi Guys,

I have searched the forum and I couldn't find any specific/close topics.
http://www.excelforum.com/excel-prog...-in-excel.html
http://www.excelforum.com/excel-prog...ing-dates.html
http://www.excelforum.com/excel-prog...rom-excel.html
http://www.excelforum.com/excel-prog...tain-date.html


Basically, I have a file which has column of deadlines. I need Excel to auto send emails to me with the data corresponding to that deadline.
I have attached sample data. The Red Column are the deadlines, the data in the corresponding yellow cells are what I need emailing to me.

Date of email to be sent: 7 days before deadline [column A]
Time for emails to be sent: 09:30am
To: Me@abc.com (testing purposes)
Email Subject: "Product Deadline Approaching: [Date of deadline]"
Body of email:

"The below products are expiring

Deadline: [DATE - Cell A4]
Cycle:[DATE - Cell B4]

Product Deadlines
All Products
T&C: [DATE - Cell C4]
Ideal: [DATE - Cell D4]
Late with agreement: [DATE - Cell E4]

Primary Products
All Products
Product A: [DATE - Cell G4]

Primary Products
Other Products
Product B: [DATE - Cell H4]
Product C: [DATE - Cell I4]
Product D: [DATE - Cell J4]

Manufacturing Date
Product A: [DATE - Cell L4]
Product B [DATE - Cell M4]
Product C [DATE - Cell N4]

Thanks"

let me know if you need more info

Thank you for your help :)


Tai

This code can be used to delay send an email:

Please Login or Register  to view this content.

But it is a manual approach.

A better approach might be to establish a 'count down' scenario based on the DATE. When the deadline date is reached, the macro auto-calls your email macro which draws from the list of criteria
based in your table beginning with Column A. When you start your program each morning, the macro compares the current date with the list and sends the appropriate emails.

Re: "countdown"

Please Login or Register  to view this content.

Re: Which email to send based on date ... look at this:

http://stackoverflow.com/questions/3...-based-on-date

Please Login or Register  to view this content.

Hi Logit,

Thanks for the attachment, i understand. Although, I am still confused about the body and subject of the email. The reason I want it to generate the email is because the sample data contains 5 rows, and my data is annual so having an email with the body that matches the date would be great help.

Body of email:

"The below products are expiring

Deadline: [Value in Cell A4]
Cycle:[Value in Cell B4]

Product Deadlines
All Products
T&C: [Value in Cell C4]
Ideal: [Value in Cell D4]
Late with agreement: [Value in Cell E4]

Primary Products
All Products
Product A: [Value in Cell G4]

Primary Products
Other Products
Product B: [Value in Cell H4]
Product C: [Value in Cell I4]
Product D: [Value in Cell J4]

Manufacturing Date
Product A: [Value in Cell L4]
Product B [Value in Cell M4]
Product C [Value in Cell N4]

Thanks"

The above email is based on if today's date matched A4

Thank you so much for your time.

Tai

Review these resources. The process can be used for any of the email entries (TO, FROM, BODY, CC, BCC, etc).

http://stackoverflow.com/questions/2...r-selected-row

http://stackoverflow.com/questions/2...excel-contents

http://www.rondebruin.nl/win/s1/outlook/bmail5.htm



Look at the TIPS section in this resource:
http://www.rondebruin.nl/win/s1/outlook/bmail2.htm




I searched for the above URLs using this: VBA Email body reference cell row content


Take your time, study the resources. Everything you need is there. When you have something put together and need assistance come on back for more answers.

Best

Hi Logit,

This is great, thanks for all your time and help.
it's making sense. I will get back once I have generated a viable code

Thanks
Tai

Hi Logit,

Thank you so much for your help. I tweaked it a little but it fully works :D

I also added the below so I can select a range of email addresses rather than having a fixed person.

Really appreciate your help and supportive comments. I am amateur to be honest, I just read the sites you gave and put the pieces together

Thanks

Please Login or Register  to view this content.

Keep up the great work ! Nicely done.

If you haven't already, please mark this thread as solved. Up top, right side, THREAD TOOLS

Thanks Logit
Speak soon

Tai