email range based on date value

**'''your code here** · 03-17-2010, 07:55 PM

Hi all,
Im having trouble with a worksheet that i am running,

the worksheet data is:
B3: ACTIONS C3: DATE D3: COMMENTS
B4: TASK1 C4: 25/1/10 D4: RANDOM COMMENT HERE
B5: TASK2 C5: 4/3/10 D5: RANDOM COMMENT HERE
B6: TASK3 C6: 3/3/10 D6: RANDOM COMMENT HERE
B7: TASK4 C7: 19/5/10 D7: RANDOM COMMENT HERE

and so on untill about row 200 or so

what i need is:

if c4 > todays date + 10 then email cells b4, c4 and d4 (in the body of the email) to [email protected]
if c5 > todays date + 10 then email cells b5, c5 and d5 (in the body of the email) to [email protected]

I would prefer if the emails could be seperate rather than all tasks being on one email, but it doesnt matter

i am using this for a kinda work to list so for example if out of the whole sheet there was only 2 dates due i would recieve 2 emails and could work through them, when they are finished i would delete the emails then delete them from the excel sheet

I know its a kinda funny request but it would make my life a lot easier.

Also if anyone does come up with anything could you please explain what the different parts of the macro are doing so i can learn from it and possibly help others in the future.

P.s i use outlook express 6

thanks for your help

**Leith Ross** · 03-18-2010, 01:22 AM

Hello ''your code here,

This was quite a challenge. The macro will read the data on the worksheet, combine the cell information and then email it provided the date is greater than 10 days from today. Once the email is displayed, you will need to click Send or Cancel. This process will be repeated until there are no more emails to be sent.

What makes this tricky is allowing the user to manually send the email fro Outlook Express and return back to Excel. Seems like it should be easy. This code will show you that there is a lot more "under the hood" to make this happen. The real work is done by the API (Application Programming Interface) code.

In the macro SendEmails you will need to change the recipient's email address (SendTo) and the subject line (Subject) to what you want to use. If you want to send the emails to more than one recipient, let me know.

[bPlace this Code in a Separate VBA Module[/b]

Please Login or Register  to view this content.

**'''your code here** · 03-18-2010, 06:00 PM

wow, firstly thanks for your input, that must have taken a long time and i appreciate the effort you have put in.
now this may sound a bit cheeky but would it be possible to bypass the part where you have to send the mail manually, so it will send the mail without the outlook screen popping up, so when i click the button it will do it all in the backround.

thanks again

ps i changed a tiny piece of the code, my fault i got the > sign round the wrong way

Please Login or Register  to view this content.

**Leith Ross** · 03-18-2010, 06:12 PM

Hello your code goes here,

Actually, it didn't take me that long to do. Your question isn't cheeky. The problem with fully automating your email process is that Outlook Express does not support automation. There is no easy way to click the "Send" button nor any VBA code to directly access it. It is very easy do what you want with Outlook because it was designed for automation. Truthfully, if want to automate the process then you need to use Outlook.

**'''your code here** · 03-18-2010, 06:45 PM

ok, would it be easy to do for outlook 2003, i have that at work and i would like to use this code there aswell.
If i wanted to change the range of the selected cell, say i wanted it to search the E colum for the date and copy A4 to say F4 to the email, could you show me which bit of the code i would change to do this, im guessing its proberly something to do with these lines

Please Login or Register  to view this content.

thanks pete

**Leith Ross** · 03-18-2010, 11:07 PM

Hello Pete,

I changed the code to search for the dates in column "E" and extract the cells in column "A:F". The cells are separated by spaces. Yo may want to add new line characters to format the mail message the way you want.

Please Login or Register  to view this content.

**sanjay19961** · 03-19-2010, 08:07 AM

Originally Posted by Leith Ross

Hello Pete,

I changed the code to search for the dates in column "E" and extract the cells in column "A:F". The cells are separated by spaces. Yo may want to add new line characters to format the mail message the way you want.

Please Login or Register  to view this content.

--------------------
Hi

It is still going through outlook express. Will it not work with MIcrosoft Outlook ?

regards
sanjay

**'''your code here** · 03-20-2010, 12:21 PM

thanks for all the help, the code worked great

**sanjay19961** · 03-23-2010, 04:00 AM

Please can anyone help me in modifying the code here so that I can use it with Microsoft outlook and instead of sending email for individual row, I want a consolidatged report containing all rows where we have past 10 days.

regards
sanjay
===============================================================
Sub SendEmails()

'Send emails and allow user to send them manually one at a time.

Dim Cell As Range
Dim DueDate As Date
Dim Rng As Range
Dim RngEnd As Range
Dim Wks As Worksheet

SendTo = "[email protected]" '<<<<< Change Recipient Email Address
Subject = "Subject line text" '<<<<< Change Subject Line

Set Wks = Worksheets("Sheet1")

'Get the size the date column
Set Rng = Wks.Range("E4")
Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)

'Exit if there is no data
If RngEnd.Row < Rng.Row Then Exit Sub

'Cells A4:F4 to Ax:Fx
Set Rng = Wks.Range(Rng, RngEnd).Offset(0, -4).Resize(ColumnSize:=6)

DueDate = Int(Now()) + 10

'Check dates and send email
For Each Cell In Rng.Columns(5).Cells
If Cell > DueDate Then
Msg = Cell.Offset(0, -4) & " " _
& Cell.Offset(0, -3) & " " _
& Cell.Offset(0, -2) & " " _
& Cell.Offset(0, -1) & " " _
& Cell.Offset(0, 0) & " " _
& Cell.Offset(0, 1)
SendEmailFromOE SendTo, Subject, Msg
End If
Next Cell

End Sub

**'''your code here** · 03-23-2010, 03:12 PM

Hi sanjay,
I was recommended this website for problems like yours; http://www.rondebruin.nl/sendmail.htm
There is a lot of useful information on the page and im sure you will find your answer there.