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.

Try this tester Safi that use GetOpenFilename
Is this working for you ?

Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object
Dim FName As Variant

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName < False Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add FName
.Display 'or use .Send
End With
On Error GoTo 0
End If

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--

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


"Safi." wrote in message ...
Hi Sir,

Thanks a lot for your ligthening reply...hope this had help me a lot but
still i have some doubts while considering final shots.

your reply :

1.is ok

2.yes we have to make browse and attach (its for novice who cannot type
complete path of a file and if rediffmail attachement style wil help) for
every person in a range. the file resides somewhere in drive.. the present
code works when i pick file with Ctrl+K but the main drawback is that it
returns a path ..\servers\My Documents\sales.doc if I browse and select any
thing from C: drive where as if i select from other drives it retuns full
path and the file attaches comfortably...(without .display and attaching here
we minimizing interaction of manual work).
3.this is ok
Thanking you,
Safi.


"Ron de Bruin" wrote:

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.

Hi Sir,

Yes this code is working code and thanks a lot to you...can we make this as
a optional for user to attach file for some and not for some in the same
excel.

thanking you,
safi.

"Ron de Bruin" wrote:

Try this tester Safi that use GetOpenFilename
Is this working for you ?

Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object
Dim FName As Variant

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName < False Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add FName
.Display 'or use .Send
End With
On Error GoTo 0
End If

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--

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


"Safi." wrote in message ...
Hi Sir,

Thanks a lot for your ligthening reply...hope this had help me a lot but
still i have some doubts while considering final shots.

your reply :

1.is ok

2.yes we have to make browse and attach (its for novice who cannot type
complete path of a file and if rediffmail attachement style wil help) for
every person in a range. the file resides somewhere in drive.. the present
code works when i pick file with Ctrl+K but the main drawback is that it
returns a path ..\servers\My Documents\sales.doc if I browse and select any
thing from C: drive where as if i select from other drives it retuns full
path and the file attaches comfortably...(without .display and attaching here
we minimizing interaction of manual work).
3.this is ok
Thanking you,
Safi.


"Ron de Bruin" wrote:

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.

Hi safi

You can add a column in your table that named"Attach"
And use "Yes" if you want to use the browse code for that person

When you loop through the table you check this value and if it is "Yes" use the browse code and attach the file.

If you need more help post back


--

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


"Safi." wrote in message ...
Hi Sir,

Yes this code is working code and thanks a lot to you...can we make this as
a optional for user to attach file for some and not for some in the same
excel.

thanking you,
safi.

"Ron de Bruin" wrote:

Try this tester Safi that use GetOpenFilename
Is this working for you ?

Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object
Dim FName As Variant

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName < False Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add FName
.Display 'or use .Send
End With
On Error GoTo 0
End If

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--

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


"Safi." wrote in message ...
Hi Sir,

Thanks a lot for your ligthening reply...hope this had help me a lot but
still i have some doubts while considering final shots.

your reply :

1.is ok

2.yes we have to make browse and attach (its for novice who cannot type
complete path of a file and if rediffmail attachement style wil help) for
every person in a range. the file resides somewhere in drive.. the present
code works when i pick file with Ctrl+K but the main drawback is that it
returns a path ..\servers\My Documents\sales.doc if I browse and select any
thing from C: drive where as if i select from other drives it retuns full
path and the file attaches comfortably...(without .display and attaching here
we minimizing interaction of manual work).
3.this is ok
Thanking you,
Safi.


"Ron de Bruin" wrote:

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.

Hi Sir,

yes it works fine. but it is the demand of situation that I should make a
code which makes excel powerful and make use of it in boss absense for
sending mails on his behalf..hope you understand.
1. (his computer is on but he is not at his seat) even though continous
mails are fired to the respective locations for reports which is possible by
the date and time criteria in column C of my code.

2. when it comes for an attachment already fill in column E will also be
mailed silently with making browse for file ( which means presence) this is
also possible by Ctrl+K (hyperlink) (which returns complete path of the file)
assuming the file is permanent there) and

..Attachments.Add (cell.Offset(0, 4).Value)

the files are attached corresponding to the value of cell. this works awesome.
but the main problem here is that when select file is from C: drive or
Desktop or My Documents it does not display the complete path..and someone
here told that we cannot change the hyperlink values.

The Hyperlink returns text in the cell.

the main problem is
...\Servers\My Documents\Northsales.xls (it is not recongised so not attached)
instead of
C:\Desktop\Servers\My Documents\Northsales.xls (it is attached)

can we change this with this will be more efficient.

Thanking you,
Safi.





"Ron de Bruin" wrote:

Hi safi

You can add a column in your table that named"Attach"
And use "Yes" if you want to use the browse code for that person

When you loop through the table you check this value and if it is "Yes" use the browse code and attach the file.

If you need more help post back


--

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


"Safi." wrote in message ...
Hi Sir,

Yes this code is working code and thanks a lot to you...can we make this as
a optional for user to attach file for some and not for some in the same
excel.

thanking you,
safi.

"Ron de Bruin" wrote:

Try this tester Safi that use GetOpenFilename
Is this working for you ?

Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object
Dim FName As Variant

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName < False Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add FName
.Display 'or use .Send
End With
On Error GoTo 0
End If

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--

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


"Safi." wrote in message ...
Hi Sir,

Thanks a lot for your ligthening reply...hope this had help me a lot but
still i have some doubts while considering final shots.

your reply :

1.is ok

2.yes we have to make browse and attach (its for novice who cannot type
complete path of a file and if rediffmail attachement style wil help) for
every person in a range. the file resides somewhere in drive.. the present
code works when i pick file with Ctrl+K but the main drawback is that it
returns a path ..\servers\My Documents\sales.doc if I browse and select any
thing from C: drive where as if i select from other drives it retuns full
path and the file attaches comfortably...(without .display and attaching here
we minimizing interaction of manual work).
3.this is ok
Thanking you,
Safi.


"Ron de Bruin" wrote:

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.

I post code that you can use to browse
Add that to your existing code

Post you code here so we can see what you have try

--

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


"Safi." wrote in message ...
Hi Sir,

yes it works fine. but it is the demand of situation that I should make a
code which makes excel powerful and make use of it in boss absense for
sending mails on his behalf..hope you understand.
1. (his computer is on but he is not at his seat) even though continous
mails are fired to the respective locations for reports which is possible by
the date and time criteria in column C of my code.

2. when it comes for an attachment already fill in column E will also be
mailed silently with making browse for file ( which means presence) this is
also possible by Ctrl+K (hyperlink) (which returns complete path of the file)
assuming the file is permanent there) and

.Attachments.Add (cell.Offset(0, 4).Value)

the files are attached corresponding to the value of cell. this works awesome.
but the main problem here is that when select file is from C: drive or
Desktop or My Documents it does not display the complete path..and someone
here told that we cannot change the hyperlink values.

The Hyperlink returns text in the cell.

the main problem is
..\Servers\My Documents\Northsales.xls (it is not recongised so not attached)
instead of
C:\Desktop\Servers\My Documents\Northsales.xls (it is attached)

can we change this with this will be more efficient.

Thanking you,
Safi.





"Ron de Bruin" wrote:

Hi safi

You can add a column in your table that named"Attach"
And use "Yes" if you want to use the browse code for that person

When you loop through the table you check this value and if it is "Yes" use the browse code and attach the file.

If you need more help post back


--

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


"Safi." wrote in message ...
Hi Sir,

Yes this code is working code and thanks a lot to you...can we make this as
a optional for user to attach file for some and not for some in the same
excel.

thanking you,
safi.

"Ron de Bruin" wrote:

Try this tester Safi that use GetOpenFilename
Is this working for you ?

Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object
Dim FName As Variant

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName < False Then

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add FName
.Display 'or use .Send
End With
On Error GoTo 0
End If

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--

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


"Safi." wrote in message ...
Hi Sir,

Thanks a lot for your ligthening reply...hope this had help me a lot but
still i have some doubts while considering final shots.

your reply :

1.is ok

2.yes we have to make browse and attach (its for novice who cannot type
complete path of a file and if rediffmail attachement style wil help) for
every person in a range. the file resides somewhere in drive.. the present
code works when i pick file with Ctrl+K but the main drawback is that it
returns a path ..\servers\My Documents\sales.doc if I browse and select any
thing from C: drive where as if i select from other drives it retuns full
path and the file attaches comfortably...(without .display and attaching here
we minimizing interaction of manual work).
3.this is ok
Thanking you,
Safi.


"Ron de Bruin" wrote:

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.