If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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/ |
#12
|
|||
|
|||
email by rows
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/ |
#13
|
|||
|
|||
email by rows
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/ |
#14
|
|||
|
|||
email by rows
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/ |
#15
|
|||
|
|||
email by rows
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/ |
#16
|
|||
|
|||
email by rows
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/ |
#17
|
|||
|
|||
email by rows
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/ |
#18
|
|||
|
|||
email by rows
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/ |
#19
|
|||
|
|||
email by rows
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/ |
#20
|
|||
|
|||
email by rows
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/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
msimn.exe - Application error | Ken | Outlook Express | 11 | July 19th, 2004 12:55 PM |
Inserting multiple rows into a table while maintaining data? | JAnderson | General Discussion | 2 | July 8th, 2004 05:45 PM |
can't open email | Jeff Philips | Outlook Express | 13 | June 27th, 2004 07:35 PM |