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 |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
email by rows
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/ |
#3
|
|||
|
|||
email by rows
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/ |
#4
|
|||
|
|||
email by rows
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/ |
#5
|
|||
|
|||
email by rows
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/ |
#6
|
|||
|
|||
email by rows
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/ |
#7
|
|||
|
|||
email by rows
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/ |
#8
|
|||
|
|||
email by rows
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/ |
#9
|
|||
|
|||
email by rows
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 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 every day that would get old fast ?? DaleL --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
#10
|
|||
|
|||
email by rows
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/ |
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 |