email by rows

im using this script to send the rows one at a time by email..But I also
want to include the headers also. How can I add that to this script so
every email has headers from the sheet and the data under it only from
the row being sent ??



origanal script from Ron de Bruin

This example will send 20 columns of the row.
If you want more change this line in the function
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address, _

This will send 50 columns and not A:C for example
Source:=Range(Cells(cell.Row, 4), Cells(cell.Row, 50)).Address, _


Option Explicit
Dim cell As Range

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Subject = "Reminder"
.HTMLBody = RangetoHTML
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/

Hi Dale

You can filter on each name and send the visible cells in the range.
If you want a example I will make one for you this weekend.

Let me know if you want that

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message news
im using this script to send the rows one at a time by email..But I also
want to include the headers also. How can I add that to this script so
every email has headers from the sheet and the data under it only from
the row being sent ??



origanal script from Ron de Bruin

This example will send 20 columns of the row.
If you want more change this line in the function
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address, _

This will send 50 columns and not A:C for example
Source:=Range(Cells(cell.Row, 4), Cells(cell.Row, 50)).Address, _


Option Explicit
Dim cell As Range

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
To = cell.Value
Subject = "Reminder"
HTMLBody = RangetoHTML
Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address, _
HtmlType:=xlHtmlStatic)
Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/

yes please make an example for me. I need all the help I can get I'm not
very experienced with excel.
If you would please use the original mail code you gave me so I can cut
and paste it in to the macro.

Thanks
Dale


Ron de Bruin wrote:
*Hi Dale

You can filter on each name and send the visible cells in the range.
If you want a example I will make one for you this weekend.

Let me know if you want that

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
im using this script to send the rows one at a time by email..But I
also
want to include the headers also. How can I add that to this script
so
every email has headers from the sheet and the data under it only
from
the row being sent ??



origanal script from Ron de Bruin

This example will send 20 columns of the row.
If you want more change this line in the function
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address, _

This will send 50 columns and not A:C for example
Source:=Range(Cells(cell.Row, 4), Cells(cell.Row, 50)).Address, _


Option Explicit
Dim cell As Range

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In

Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
To = cell.Value
Subject = "Reminder"
HTMLBody = RangetoHTML
Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address, _
HtmlType:=xlHtmlStatic)
Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

Hi Dale

I add this page to my site today
http://www.rondebruin.nl/mail/folder3/row.htm

It is a example for Outlook
I hope you can use it

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message news
yes please make an example for me. I need all the help I can get I'm not
very experienced with excel.
If you would please use the original mail code you gave me so I can cut
and paste it in to the macro.

Thanks
Dale


Ron de Bruin wrote:
*Hi Dale

You can filter on each name and send the visible cells in the range.
If you want a example I will make one for you this weekend.

Let me know if you want that

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
im using this script to send the rows one at a time by email..But I
also
want to include the headers also. How can I add that to this script
so
every email has headers from the sheet and the data under it only
from
the row being sent ??



origanal script from Ron de Bruin

This example will send 20 columns of the row.
If you want more change this line in the function
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address, _

This will send 50 columns and not A:C for example
Source:=Range(Cells(cell.Row, 4), Cells(cell.Row, 50)).Address, _


Option Explicit
Dim cell As Range

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In

Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
To = cell.Value
Subject = "Reminder"
HTMLBody = RangetoHTML
Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address, _
HtmlType:=xlHtmlStatic)
Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

Ron thanks this will help me out a lot i am trying to make a work book
for teachers to help automate a lot of grading functions they have to
do and this will be a great add in for it

Dale



Ron de Bruin wrote:
*Hi Dale

I add this page to my site today
http://www.rondebruin.nl/mail/folder3/row.htm

It is a example for Outlook
I hope you can use it

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
yes please make an example for me. I need all the help I can get
I'm not
very experienced with excel.
If you would please use the original mail code you gave me so I can
cut
and paste it in to the macro.

Thanks
Dale


Ron de Bruin wrote:
*Hi Dale

You can filter on each name and send the visible cells in the
range.
If you want a example I will make one for you this weekend.

Let me know if you want that

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
im using this script to send the rows one at a time by
email..But I
also
want to include the headers also. How can I add that to this
script
so
every email has headers from the sheet and the data under it
only
from
the row being sent ??



origanal script from Ron de Bruin

This example will send 20 columns of the row.
If you want more change this line in the function
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address,
_

This will send 50 columns and not A:C for example
Source:=Range(Cells(cell.Row, 4), Cells(cell.Row, 50)).Address,
_


Option Explicit
Dim cell As Range

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In


Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes"
Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
To = cell.Value
Subject = "Reminder"
HTMLBody = RangetoHTML
Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address,
_
HtmlType:=xlHtmlStatic)
Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

Hi Dale
check out also this

A lot of teachers use one sheet for each student and mail that one
http://www.rondebruin.nl/mail/folder1/mail5.htm

Or for Outlook
http://www.rondebruin.nl/mail/folder2/mail5.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message news
Ron thanks this will help me out a lot i am trying to make a work book
for teachers to help automate a lot of grading functions they have to
do and this will be a great add in for it

Dale



Ron de Bruin wrote:
*Hi Dale

I add this page to my site today
http://www.rondebruin.nl/mail/folder3/row.htm

It is a example for Outlook
I hope you can use it

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
yes please make an example for me. I need all the help I can get
I'm not
very experienced with excel.
If you would please use the original mail code you gave me so I can
cut
and paste it in to the macro.

Thanks
Dale


Ron de Bruin wrote:
*Hi Dale

You can filter on each name and send the visible cells in the
range.
If you want a example I will make one for you this weekend.

Let me know if you want that

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
im using this script to send the rows one at a time by
email..But I
also
want to include the headers also. How can I add that to this
script
so
every email has headers from the sheet and the data under it
only
from
the row being sent ??



origanal script from Ron de Bruin

This example will send 20 columns of the row.
If you want more change this line in the function
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address,
_

This will send 50 columns and not A:C for example
Source:=Range(Cells(cell.Row, 4), Cells(cell.Row, 50)).Address,
_


Option Explicit
Dim cell As Range

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In


Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes"
Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
To = cell.Value
Subject = "Reminder"
HTMLBody = RangetoHTML
Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row, 20)).Address,
_
HtmlType:=xlHtmlStatic)
Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

is this where I change it (Ash.Range("A1:J100") if i want to go all the
way over to column U can i just change :J to :U and if i want to NOT
include say the first few coulmns would i do (Ash.Range("E1:U100") is
this correct or am i not reading this correctly

DaleL


Ron de Bruin wrote:
*Hi Dale
check out also this

A lot of teachers use one sheet for each student and mail that one
http://www.rondebruin.nl/mail/folder1/mail5.htm

Or for Outlook
http://www.rondebruin.nl/mail/folder2/mail5.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
Ron thanks this will help me out a lot i am trying to make a work
book
for teachers to help automate a lot of grading functions they have
to
do and this will be a great add in for it

Dale



Ron de Bruin wrote:
*Hi Dale

I add this page to my site today
http://www.rondebruin.nl/mail/folder3/row.htm

It is a example for Outlook
I hope you can use it

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
yes please make an example for me. I need all the help I can
get
I'm not
very experienced with excel.
If you would please use the original mail code you gave me so I
can
cut
and paste it in to the macro.

Thanks
Dale


Ron de Bruin wrote:
*Hi Dale

You can filter on each name and send the visible cells in
the
range.
If you want a example I will make one for you this weekend.

Let me know if you want that

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in
message
news
im using this script to send the rows one at a time by
email..But I
also
want to include the headers also. How can I add that to
this
script
so
every email has headers from the sheet and the data under
it
only
from
the row being sent ??



origanal script from Ron de Bruin

This example will send 20 columns of the row.
If you want more change this line in the function
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row,
20)).Address,
_

This will send 50 columns and not A:C for example
Source:=Range(Cells(cell.Row, 4), Cells(cell.Row,
50)).Address,
_


Option Explicit
Dim cell As Range

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In



Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value =
"yes"
Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
To = cell.Value
Subject = "Reminder"
HTMLBody = RangetoHTML
Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row,
20)).Address,
_
HtmlType:=xlHtmlStatic)
Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

Hi

In this example you can change the J to U
But A must stay A


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message news
is this where I change it (Ash.Range("A1:J100") if i want to go all the
way over to column U can i just change :J to :U and if i want to NOT
include say the first few coulmns would i do (Ash.Range("E1:U100") is
this correct or am i not reading this correctly

DaleL


Ron de Bruin wrote:
*Hi Dale
check out also this

A lot of teachers use one sheet for each student and mail that one
http://www.rondebruin.nl/mail/folder1/mail5.htm

Or for Outlook
http://www.rondebruin.nl/mail/folder2/mail5.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
Ron thanks this will help me out a lot i am trying to make a work
book
for teachers to help automate a lot of grading functions they have
to
do and this will be a great add in for it

Dale



Ron de Bruin wrote:
*Hi Dale

I add this page to my site today
http://www.rondebruin.nl/mail/folder3/row.htm

It is a example for Outlook
I hope you can use it

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
yes please make an example for me. I need all the help I can
get
I'm not
very experienced with excel.
If you would please use the original mail code you gave me so I
can
cut
and paste it in to the macro.

Thanks
Dale


Ron de Bruin wrote:
*Hi Dale

You can filter on each name and send the visible cells in
the
range.
If you want a example I will make one for you this weekend.

Let me know if you want that

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in
message
news
im using this script to send the rows one at a time by
email..But I
also
want to include the headers also. How can I add that to
this
script
so
every email has headers from the sheet and the data under
it
only
from
the row being sent ??



origanal script from Ron de Bruin

This example will send 20 columns of the row.
If you want more change this line in the function
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row,
20)).Address,
_

This will send 50 columns and not A:C for example
Source:=Range(Cells(cell.Row, 4), Cells(cell.Row,
50)).Address,
_


Option Explicit
Dim cell As Range

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In



Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value =
"yes"
Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
To = cell.Value
Subject = "Reminder"
HTMLBody = RangetoHTML
Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row,
20)).Address,
_
HtmlType:=xlHtmlStatic)
Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

You can add this to the sub if you not want to send A:C

After this existing line rng.Copy Nsh.Cells(1)
copy this line

Nsh.Columns("A:C").Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi

In this example you can change the J to U
But A must stay A


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message news
is this where I change it (Ash.Range("A1:J100") if i want to go all the
way over to column U can i just change :J to :U and if i want to NOT
include say the first few coulmns would i do (Ash.Range("E1:U100") is
this correct or am i not reading this correctly

DaleL


Ron de Bruin wrote:
*Hi Dale
check out also this

A lot of teachers use one sheet for each student and mail that one
http://www.rondebruin.nl/mail/folder1/mail5.htm

Or for Outlook
http://www.rondebruin.nl/mail/folder2/mail5.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news Ron thanks this will help me out a lot i am trying to make a work
book
for teachers to help automate a lot of grading functions they have
to
do and this will be a great add in for it

Dale



Ron de Bruin wrote:
*Hi Dale

I add this page to my site today
http://www.rondebruin.nl/mail/folder3/row.htm

It is a example for Outlook
I hope you can use it

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in message
news
yes please make an example for me. I need all the help I can
get
I'm not
very experienced with excel.
If you would please use the original mail code you gave me so I
can
cut
and paste it in to the macro.

Thanks
Dale


Ron de Bruin wrote:
*Hi Dale

You can filter on each name and send the visible cells in
the
range.
If you want a example I will make one for you this weekend.

Let me know if you want that

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DaleL " wrote in
message
news
im using this script to send the rows one at a time by
email..But I
also
want to include the headers also. How can I add that to
this
script
so
every email has headers from the sheet and the data under
it
only
from
the row being sent ??



origanal script from Ron de Bruin

This example will send 20 columns of the row.
If you want more change this line in the function
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row,
20)).Address,
_

This will send 50 columns and not A:C for example
Source:=Range(Cells(cell.Row, 4), Cells(cell.Row,
50)).Address,
_


Option Explicit
Dim cell As Range

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In



Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value =
"yes"
Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
To = cell.Value
Subject = "Reminder"
HTMLBody = RangetoHTML
Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Range(Cells(cell.Row, 1), Cells(cell.Row,
20)).Address,
_
HtmlType:=xlHtmlStatic)
Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

HI Ron I added your code to my workbook and it is giveing me errors at
.Publish (True)when I click the debug button this is where the yellow
line is..Publish (True)

it works part of the time .. when it does work all it will send is the
headers..i dont know what im doing diffrently to get to work those
times

This is the code I am using is it possable I can send my work book to
you and have you look it over and maybe try the code out your self ???

Dim Nsh As Worksheet

Sub Send_Row()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set Ash = ActiveSheet
Set Nsh = Worksheets.Add
Ash.Activate

On Error GoTo cleanup
For Each cell In
Ash.Columns("E").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes"
Then
Ash.Range("A1:L100").AutoFilter Field:=2,
Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
rng.Copy Nsh.Cells(1)
Nsh.Columns.AutoFit
Ash.AutoFilterMode = False

Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Subject = "Grades Aug"
.HTMLBody = RangetoHTML2
.Send 'Or use Display
End With
Set OutMail = Nothing
Nsh.Cells.Clear
End If
Next cell

cleanup:
Application.DisplayAlerts = False
Nsh.Delete
Application.DisplayAlerts = True
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub




Function RangetoHTML2()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=Nsh.Name, _
Source:=Nsh.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/