Sir,

I have gone through the page of your site which makes EXCEL to send
E-mail..which had opened my eyes against excel as it has immense power which
i have often ignored but when i come accross this particular idea of sending
Email through ms-outlook and excel..I have changed your VBA code to suit my
needs...as i have to report and want various reports from others i was glued
to ms-outlook. but due to typing and making my point more in force i used to
type each one..but ur simple code makes this possible and solved so many
hours of hardwork..thanks a lot.

Please have a look into the code which i have altered and help me in getting
more reinforced...thanks in advance.

My Data is like this :

A B C D E F
Name Email Criteria Subject Matter Attachment


The Code I have altered:

ub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Offset(A2).Value

.Subject = cell.Offset(0, 2).Value

.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & cell.Offset(0, 3).Value

'You can add files also like this
.Attachments.Add (cell.Offset(0, 4).Value)
.Send 'Or use Display
End With
On Error GoTo 0

Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



The Issues I want to solve and make this more powerful is that

1.Instead of yes/no in criteria I wanted have it compared with date if the
date and time matches to todays date and time then "yes" else no.

2.Browse for the attachment rather than typing a long file path in cell as
it is very difficult to remember the lengthy complete file path (shall we
make it for browse for a file then select)

3.if the date and time matches to todays date and time even though the excel
& outlook are not opened then also it must mail to the relevant email..some
sort of like startup..(nternet SMTP servers are 24 hours on. making
indenpendent.
(i fill mail id, subject and matter and date and time in criteria for a
month so that even i my absense the person from whom i want a report shuld
get my reminder keeping in view that my computer is always on)

the same when expressed briefly
1. criteria will be date and time
2. browse for the file to attach
3. run background and mail against the matched date and time mentioned in
excel.

Hope to get solution for this...

Thanking you,
safi.

Hi Safi

1)Use a formula in the column that display yes if the date is todays date
see example 2 where i also use a formula
http://www.rondebruin.nl/mail/folder3/message.htm

2) browse for a file for every person in the range ? or the same file for all ?
You can use Display instead of Send and then attach the file you want.
But if it is the same file we can add a browse code before you create the mails

3) You can run the macro one time every day
See this page
http://www.cpearson.com/excel/ontime.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Safi." wrote in message ...

Sir,

I have gone through the page of your site which makes EXCEL to send
E-mail..which had opened my eyes against excel as it has immense power which
i have often ignored but when i come accross this particular idea of sending
Email through ms-outlook and excel..I have changed your VBA code to suit my
needs...as i have to report and want various reports from others i was glued
to ms-outlook. but due to typing and making my point more in force i used to
type each one..but ur simple code makes this possible and solved so many
hours of hardwork..thanks a lot.

Please have a look into the code which i have altered and help me in getting
more reinforced...thanks in advance.

My Data is like this :

A B C D E F
Name Email Criteria Subject Matter Attachment


The Code I have altered:

ub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Offset(A2).Value

.Subject = cell.Offset(0, 2).Value

.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & cell.Offset(0, 3).Value

'You can add files also like this
.Attachments.Add (cell.Offset(0, 4).Value)
.Send 'Or use Display
End With
On Error GoTo 0

Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



The Issues I want to solve and make this more powerful is that

1.Instead of yes/no in criteria I wanted have it compared with date if the
date and time matches to todays date and time then "yes" else no.

2.Browse for the attachment rather than typing a long file path in cell as
it is very difficult to remember the lengthy complete file path (shall we
make it for browse for a file then select)

3.if the date and time matches to todays date and time even though the excel
& outlook are not opened then also it must mail to the relevant email..some
sort of like startup..(nternet SMTP servers are 24 hours on. making
indenpendent.
(i fill mail id, subject and matter and date and time in criteria for a
month so that even i my absense the person from whom i want a report shuld
get my reminder keeping in view that my computer is always on)

the same when expressed briefly
1. criteria will be date and time
2. browse for the file to attach
3. run background and mail against the matched date and time mentioned in
excel.

Hope to get solution for this...

Thanking you,
safi.