email by rows

hello All:

I'm looking for a macro that will go down a sheet and look for the
email address and mail every thing in that row to *ONLY* that person no
matter if it has 10 columns or 20 columns I have a sheet set up like
this
Employee Name LAB Degree Section E-Mail Address paper1 paper2
paper3

I will be adding more columns as the year goes on that is why it need
to be able to send different size rows.

Any help would be appreciated

Dale


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

Hi DaleL

Which mail program do you use

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


"DaleL " wrote in message news
hello All:

I'm looking for a macro that will go down a sheet and look for the
email address and mail every thing in that row to *ONLY* that person no
matter if it has 10 columns or 20 columns I have a sheet set up like
this
Employee Name LAB Degree Section E-Mail Address paper1 paper2
paper3

I will be adding more columns as the year goes on that is why it need
to be able to send different size rows.

Any help would be appreciated

Dale


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

Ihave outlook and also outlook express installed and running, also have
raiden mail system that is used for smtp.
I use one of your scripts now Ron your site was passed on to me by
CSmith from pennysaver. I use the one that has column A=email B=name
C=yes/no if i could only get that script to send every thing else in
the same row it would be great

Dale


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

Hi Dale

Try this

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


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


"DaleL " wrote in message news
Ihave outlook and also outlook express installed and running, also have
raiden mail system that is used for smtp.
I use one of your scripts now Ron your site was passed on to me by
CSmith from pennysaver. I use the one that has column A=email B=name
C=yes/no if i could only get that script to send every thing else in
the same row it would be great

Dale


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

I copied the script from here in to module1
and checked Microsoft outlook 9.0 object libary
when i run the code nothing happens i have my email adress in column B

DaleL


Ron de Bruin wrote:
*Hi Dale

Try this

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


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


"DaleL " wrote in message
news
Ihave outlook and also outlook express installed and running, also
have
raiden mail system that is used for smtp.
I use one of your scripts now Ron your site was passed on to me by
CSmith from pennysaver. I use the one that has column A=email
B=name
C=yes/no if i could only get that script to send every thing else
in
the same row it would be great

Dale


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


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

Hi

and checked Microsoft outlook 9.0 object libary
You don't have to set a reference,I use Late binding

Change
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)

To
Columns("B").Cells.SpecialCells(xlCellTypeConstant s)

And be sure the sheet with the email adress in column B is active

***Dim cell As Range***
must be above the sub and function

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


"DaleL " wrote in message news
I copied the script from here in to module1
and checked Microsoft outlook 9.0 object libary
when i run the code nothing happens i have my email adress in column B

DaleL


Ron de Bruin wrote:
*Hi Dale

Try this

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


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


"DaleL " wrote in message
news
Ihave outlook and also outlook express installed and running, also
have
raiden mail system that is used for smtp.
I use one of your scripts now Ron your site was passed on to me by
CSmith from pennysaver. I use the one that has column A=email
B=name
C=yes/no if i could only get that script to send every thing else
in
the same row it would be great

Dale


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


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

it works if i want to have email adress in row E can i just change B
to E ??

also every email that goes out I have to click yes for outlook to send
it.
Is there a way to get past it?? With sending several hundred email
every day that would get old fast ??

DaleL


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

Hi Dale

it works if i want to have email adress in row E can i just change B
to E ??

Yes, if yes/no is in F


Is there a way to get past it?? With sending several hundred email
every day that would get old fast ??

Look here
http://www.rondebruin.nl/mail/prevent.htm

CDO is maybe a option for you

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


"DaleL " wrote in message news
it works if i want to have email adress in row E can i just change B
to E ??

also every email that goes out I have to click yes for outlook to send
it.
Is there a way to get past it?? With sending several hundred email
every day that would get old fast ??

DaleL


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

Ron de Bruin wrote:
*Hi Dale

it works if i want to have email adress in row E can i just
change B
to E ??

Yes, if yes/no is in F

Thanks agian this so far is the best I have gotten I have a work book
that has a few other bulit in features I would like to have this
added to..kinda like an automated grade book im trying to make for
teachers where I work at.
Would you be willing to look at it off this list and try to help me
out with some of the email functions??
If your busy that is fine to..


Is there a way to get past it?? With sending several hundred
email
every day that would get old fast ??

Look here
http://www.rondebruin.nl/mail/prevent.htm

CDO is maybe a option for you

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


"DaleL " wrote in message
news
it works if i want to have email adress in row E can i just
change B
to E ??

also every email that goes out I have to click yes for outlook to
send
it.
Is there a way to get past it?? With sending several hundred
email
every day that would get old fast ??

DaleL


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


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

DaleL wrote:
* *
Is there any way to get the headers to go with this so when the mail
goes out it kinda helps to know what it is your looking at

Name Email Yes/No paper1 paper2 paper3 paper4 paper5 paper6

can this be down with this macro ??


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