Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
All,
I am looking for a macro that sends an email to two recipients based on the cell values in a range. I have a table of 150 rows where a cell value can fall below 0 (or conditional formatting turns it red), and for each row where the value is less than 0 (or is red), send an email with a subject heading which includes the row identification. EG. Cell A1: 80163 Cell B1: -15 Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs Email with the subject heading "80163 - Over allocation" Message: "80163 has been over allocated by 15 man-days" Repeat this for all rows, therefore if B2 is (positive) 15, an email is not sent. If changes were made to other calculations which resulted in B1 becoming positive, I don't want the macro to loop through the entire list again and re-send emails. I would prefer the this re-sending loop to occur every fortnight on a Wednesday. Hope this makes sense! If anyone can help with this, it would be greatly appreciated. James -- J |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
More info please about the info in the columns
Start here(see also example 2) http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... All, I am looking for a macro that sends an email to two recipients based on the cell values in a range. I have a table of 150 rows where a cell value can fall below 0 (or conditional formatting turns it red), and for each row where the value is less than 0 (or is red), send an email with a subject heading which includes the row identification. EG. Cell A1: 80163 Cell B1: -15 Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs Email with the subject heading "80163 - Over allocation" Message: "80163 has been over allocated by 15 man-days" Repeat this for all rows, therefore if B2 is (positive) 15, an email is not sent. If changes were made to other calculations which resulted in B1 becoming positive, I don't want the macro to loop through the entire list again and re-send emails. I would prefer the this re-sending loop to occur every fortnight on a Wednesday. Hope this makes sense! If anyone can help with this, it would be greatly appreciated. James -- J |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Ron,
Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... Therefore the macro should send an email to two recipients (to be specified), for project numbers 80163 and 80190 that say that they have too many man-days allocated to them. Subject header should include the project number to make it easy to know where to amend the data. The email message should include the figure in column B. If in a fortnight, cell B1 (project number 80163) is positive, the macro should re-send the email for project number 80190 (assuming B4 is still negative). Let me know if you still need info. Thanks for the link. I have been looking at your example 2 on http://www.rondebruin.nl/mail/change.htm I am thinking a combination of the two might be the way to go?? -- J "Ron de Bruin" wrote: More info please about the info in the columns Start here(see also example 2) http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... All, I am looking for a macro that sends an email to two recipients based on the cell values in a range. I have a table of 150 rows where a cell value can fall below 0 (or conditional formatting turns it red), and for each row where the value is less than 0 (or is red), send an email with a subject heading which includes the row identification. EG. Cell A1: 80163 Cell B1: -15 Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs Email with the subject heading "80163 - Over allocation" Message: "80163 has been over allocated by 15 man-days" Repeat this for all rows, therefore if B2 is (positive) 15, an email is not sent. If changes were made to other calculations which resulted in B1 becoming positive, I don't want the macro to loop through the entire list again and re-send emails. I would prefer the this re-sending loop to occur every fortnight on a Wednesday. Hope this makes sense! If anyone can help with this, it would be greatly appreciated. James -- J |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Hi James
I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... Therefore the macro should send an email to two recipients (to be specified), for project numbers 80163 and 80190 that say that they have too many man-days allocated to them. Subject header should include the project number to make it easy to know where to amend the data. The email message should include the figure in column B. If in a fortnight, cell B1 (project number 80163) is positive, the macro should re-send the email for project number 80190 (assuming B4 is still negative). Let me know if you still need info. Thanks for the link. I have been looking at your example 2 on http://www.rondebruin.nl/mail/change.htm I am thinking a combination of the two might be the way to go?? -- J "Ron de Bruin" wrote: More info please about the info in the columns Start here(see also example 2) http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... All, I am looking for a macro that sends an email to two recipients based on the cell values in a range. I have a table of 150 rows where a cell value can fall below 0 (or conditional formatting turns it red), and for each row where the value is less than 0 (or is red), send an email with a subject heading which includes the row identification. EG. Cell A1: 80163 Cell B1: -15 Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs Email with the subject heading "80163 - Over allocation" Message: "80163 has been over allocated by 15 man-days" Repeat this for all rows, therefore if B2 is (positive) 15, an email is not sent. If changes were made to other calculations which resulted in B1 becoming positive, I don't want the macro to loop through the entire list again and re-send emails. I would prefer the this re-sending loop to occur every fortnight on a Wednesday. Hope this makes sense! If anyone can help with this, it would be greatly appreciated. James -- J |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
If I understand you correct?
Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... Therefore the macro should send an email to two recipients (to be specified), for project numbers 80163 and 80190 that say that they have too many man-days allocated to them. Subject header should include the project number to make it easy to know where to amend the data. The email message should include the figure in column B. If in a fortnight, cell B1 (project number 80163) is positive, the macro should re-send the email for project number 80190 (assuming B4 is still negative). Let me know if you still need info. Thanks for the link. I have been looking at your example 2 on http://www.rondebruin.nl/mail/change.htm I am thinking a combination of the two might be the way to go?? -- J "Ron de Bruin" wrote: More info please about the info in the columns Start here(see also example 2) http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... All, I am looking for a macro that sends an email to two recipients based on the cell values in a range. I have a table of 150 rows where a cell value can fall below 0 (or conditional formatting turns it red), and for each row where the value is less than 0 (or is red), send an email with a subject heading which includes the row identification. EG. Cell A1: 80163 Cell B1: -15 Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs Email with the subject heading "80163 - Over allocation" Message: "80163 has been over allocated by 15 man-days" Repeat this for all rows, therefore if B2 is (positive) 15, an email is not sent. If changes were made to other calculations which resulted in B1 becoming positive, I don't want the macro to loop through the entire list again and re-send emails. I would prefer the this re-sending loop to occur every fortnight on a Wednesday. Hope this makes sense! If anyone can help with this, it would be greatly appreciated. James -- J |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Ron,
Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... Therefore the macro should send an email to two recipients (to be specified), for project numbers 80163 and 80190 that say that they have too many man-days allocated to them. Subject header should include the project number to make it easy to know where to amend the data. The email message should include the figure in column B. If in a fortnight, cell B1 (project number 80163) is positive, the macro should re-send the email for project number 80190 (assuming B4 is still negative). Let me know if you still need info. Thanks for the link. I have been looking at your example 2 on http://www.rondebruin.nl/mail/change.htm I am thinking a combination of the two might be the way to go?? -- J "Ron de Bruin" wrote: More info please about the info in the columns Start here(see also example 2) http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... All, I am looking for a macro that sends an email to two recipients based on the cell values in a range. I have a table of 150 rows where a cell value can fall below 0 (or conditional formatting turns it red), and for each row where the value is less than 0 (or is red), send an email with a subject heading which includes the row identification. EG. Cell A1: 80163 Cell B1: -15 Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs Email with the subject heading "80163 - Over allocation" Message: "80163 has been over allocated by 15 man-days" Repeat this for all rows, therefore if B2 is (positive) 15, an email is not sent. If changes were made to other calculations which resulted in B1 becoming positive, I don't want the macro to loop through the entire list again and re-send emails. I would prefer the this re-sending loop to occur every fortnight on a Wednesday. Hope this makes sense! If anyone can help with this, it would be greatly appreciated. James -- J |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Ok Try this one
Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... Therefore the macro should send an email to two recipients (to be specified), for project numbers 80163 and 80190 that say that they have too many man-days allocated to them. Subject header should include the project number to make it easy to know where to amend the data. The email message should include the figure in column B. If in a fortnight, cell B1 (project number 80163) is positive, the macro should re-send the email for project number 80190 (assuming B4 is still negative). Let me know if you still need info. Thanks for the link. I have been looking at your example 2 on http://www.rondebruin.nl/mail/change.htm I am thinking a combination of the two might be the way to go?? -- J "Ron de Bruin" wrote: More info please about the info in the columns Start here(see also example 2) http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... All, I am looking for a macro that sends an email to two recipients based on the cell values in a range. I have a table of 150 rows where a cell value can fall below 0 (or conditional formatting turns it red), and for each row where the value is less than 0 (or is red), send an email with a subject heading which includes the row identification. EG. Cell A1: 80163 Cell B1: -15 Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs Email with the subject heading "80163 - Over allocation" Message: "80163 has been over allocated by 15 man-days" Repeat this for all rows, therefore if B2 is (positive) 15, an email is not sent. If changes were made to other calculations which resulted in B1 becoming positive, I don't want the macro to loop through the entire list again and re-send emails. I would prefer the this re-sending loop to occur every fortnight on a Wednesday. Hope this makes sense! If anyone can help with this, it would be greatly appreciated. James -- J |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Ron,
Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... Therefore the macro should send an email to two recipients (to be specified), for project numbers 80163 and 80190 that say that they have too many man-days allocated to them. Subject header should include the project number to make it easy to know where to amend the data. The email message should include the figure in column B. If in a fortnight, cell B1 (project number 80163) is positive, the macro should re-send the email for project number 80190 (assuming B4 is still negative). Let me know if you still need info. Thanks for the link. I have been looking at your example 2 on http://www.rondebruin.nl/mail/change.htm I am thinking a combination of the two might be the way to go?? -- J "Ron de Bruin" wrote: More info please about the info in the columns Start here(see also example 2) http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... All, I am looking for a macro that sends an email to two recipients based on the cell values in a range. I have a table of 150 rows where a cell value can fall below 0 (or conditional formatting turns it red), and for each row where the value is less than 0 (or is red), send an email with a subject heading which includes the row identification. EG. Cell A1: 80163 Cell B1: -15 Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs Email with the subject heading "80163 - Over allocation" Message: "80163 has been over allocated by 15 man-days" Repeat this for all rows, therefore if B2 is (positive) 15, an email is not sent. If changes were made to other calculations which resulted in B1 becoming positive, I don't want the macro to loop through the entire list again and re-send emails. I would prefer the this re-sending loop to occur every fortnight on a Wednesday. Hope this makes sense! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Hi James
A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Use .HTMLBody = "This is line 1.<br" & _ "This is line 2.<br" & _ "This is line 3.<br" & _ "This is line 4.<br<br<br" & _ RangetoHTML(rng) Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" It will create a htm file to create the html in your temp folder At the end of the function it delete this file 'Delete the htm file we used in this function Kill TempFile Finally, the code only generates the email, it doesn't send automatically. What do I need to change? .Display 'or use .Send -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... Therefore the macro should send an email to two recipients (to be specified), for project numbers 80163 and 80190 that say that they have too many man-days allocated to them. Subject header should include the project number to make it easy to know where to amend the data. The email message should include the figure in column B. If in a fortnight, cell B1 (project number 80163) is positive, the macro should re-send the email for project number 80190 (assuming B4 is still negative). Let me know if you still need info. Thanks for the link. I have been looking at your example 2 on http://www.rondebruin.nl/mail/change.htm I am thinking a combination of the two might be the way to go?? -- J "Ron de Bruin" wrote: More info please about the info in the columns Start here(see also example 2) http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... All, I am looking for a macro that sends an email to two recipients based on the cell values in a range. I have a table of 150 rows where a cell value can fall below 0 (or conditional formatting turns it red), and for each row where the value is less than 0 (or is red), send an email with a subject heading which includes the row identification. EG. Cell A1: 80163 Cell B1: -15 Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs Email with the subject heading "80163 - Over allocation" Message: "80163 has been over allocated by 15 man-days" Repeat this for all rows, therefore if B2 is (positive) 15, an email is not sent. If changes were made to other calculations which resulted in B1 becoming positive, I don't want the macro to loop through the entire list again and re-send emails. I would prefer the this re-sending loop to occur every fortnight on a Wednesday. Hope this makes sense! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Thanks Ron,
The .Send / .Display section was obvious when I re-read the code! Thanks a lot for your help. James -- J "Ron de Bruin" wrote: Hi James A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Use .HTMLBody = "This is line 1.<br" & _ "This is line 2.<br" & _ "This is line 3.<br" & _ "This is line 4.<br<br<br" & _ RangetoHTML(rng) Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" It will create a htm file to create the html in your temp folder At the end of the function it delete this file 'Delete the htm file we used in this function Kill TempFile Finally, the code only generates the email, it doesn't send automatically. What do I need to change? .Display 'or use .Send -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Ron,
Sorry to be a pain, I can't get the pre-defined message to appear. This is the code I am using (cut and paste from VBA): .HTMLBody = "Joe,<br" & _ "There are a number of projects that have been over-allocated<br" & _ "The following table shows the project number and the over-allocation in terms of man-days<br" & _ "<br<br<br" & _ RangetoHTML(rng) -- J "Ron de Bruin" wrote: Hi James A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Use .HTMLBody = "This is line 1.<br" & _ "This is line 2.<br" & _ "This is line 3.<br" & _ "This is line 4.<br<br<br" & _ RangetoHTML(rng) Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" It will create a htm file to create the html in your temp folder At the end of the function it delete this file 'Delete the htm file we used in this function Kill TempFile Finally, the code only generates the email, it doesn't send automatically. What do I need to change? .Display 'or use .Send -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Ron,
Found another problem (not with the code). I set up a dummy spreadsheet at home to test out the code. The code works fine and generates an email from Outlook with the required data. I have now tried to do exactly the same thing at work. The problem is that the macro code generates the email, but there is no table, only my HTML signature. Is there anything I can change in the code? Thanks James -- J "Ron de Bruin" wrote: Hi James A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Use .HTMLBody = "This is line 1.<br" & _ "This is line 2.<br" & _ "This is line 3.<br" & _ "This is line 4.<br<br<br" & _ RangetoHTML(rng) Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" It will create a htm file to create the html in your temp folder At the end of the function it delete this file 'Delete the htm file we used in this function Kill TempFile Finally, the code only generates the email, it doesn't send automatically. What do I need to change? .Display 'or use .Send -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
If Word is your mail editor in Outlook it can give problems
Try to uncheck it in Outlook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Found another problem (not with the code). I set up a dummy spreadsheet at home to test out the code. The code works fine and generates an email from Outlook with the required data. I have now tried to do exactly the same thing at work. The problem is that the macro code generates the email, but there is no table, only my HTML signature. Is there anything I can change in the code? Thanks James -- J "Ron de Bruin" wrote: Hi James A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Use .HTMLBody = "This is line 1.<br" & _ "This is line 2.<br" & _ "This is line 3.<br" & _ "This is line 4.<br<br<br" & _ RangetoHTML(rng) Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" It will create a htm file to create the html in your temp folder At the end of the function it delete this file 'Delete the htm file we used in this function Kill TempFile Finally, the code only generates the email, it doesn't send automatically. What do I need to change? .Display 'or use .Send -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. 80163, 80176. They are not necessarily in a logical order, but will always be 5 characters in length. Column B, cells 1-150 will be a formula result that compares two other cell values. Therefore man-days allowed MINUS man-days required to give the value in the appropriate cell of column B (either positive or negative). If the cell value is less than 0, the cell colour turns red. For arguments sake man-days allowed would be column C and man-days required column D. The man-days allowed figures (column C) will change very rarely. The man-days required figures (column D) will change, therefore an email sent one week might not need to be sent in three weeks time. Recipients: Will always be the same two people, so they could be defined as "jbloggs...." etc. Sample data: Column A Column B Column C Column D 80163 -15 20 35 80176 15 35 20 80187 0 20 20 80190 -10 20 30 and so on... |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Is there a way I can build this into the macro?
The reason I ask is that the company require the email signature to be used on all emails. Turning off this email editor won't be acceptable for all other emails sent externally. This email will be sent internally, so I see no reason why we can uncheck for the purposes of the email, and then reselect once sent. If this can be done in the current macro that would be brilliant, if not is there another macro that can do the same job? Thanks for sticking with this. Everything else works great. James -- J "Ron de Bruin" wrote: If Word is your mail editor in Outlook it can give problems Try to uncheck it in Outlook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Found another problem (not with the code). I set up a dummy spreadsheet at home to test out the code. The code works fine and generates an email from Outlook with the required data. I have now tried to do exactly the same thing at work. The problem is that the macro code generates the email, but there is no table, only my HTML signature. Is there anything I can change in the code? Thanks James -- J "Ron de Bruin" wrote: Hi James A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Use .HTMLBody = "This is line 1.<br" & _ "This is line 2.<br" & _ "This is line 3.<br" & _ "This is line 4.<br<br<br" & _ RangetoHTML(rng) Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" It will create a htm file to create the html in your temp folder At the end of the function it delete this file 'Delete the htm file we used in this function Kill TempFile Finally, the code only generates the email, it doesn't send automatically. What do I need to change? .Display 'or use .Send -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Never try this James
No time to try/test it now Ask it in a Outlook group -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Is there a way I can build this into the macro? The reason I ask is that the company require the email signature to be used on all emails. Turning off this email editor won't be acceptable for all other emails sent externally. This email will be sent internally, so I see no reason why we can uncheck for the purposes of the email, and then reselect once sent. If this can be done in the current macro that would be brilliant, if not is there another macro that can do the same job? Thanks for sticking with this. Everything else works great. James -- J "Ron de Bruin" wrote: If Word is your mail editor in Outlook it can give problems Try to uncheck it in Outlook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Found another problem (not with the code). I set up a dummy spreadsheet at home to test out the code. The code works fine and generates an email from Outlook with the required data. I have now tried to do exactly the same thing at work. The problem is that the macro code generates the email, but there is no table, only my HTML signature. Is there anything I can change in the code? Thanks James -- J "Ron de Bruin" wrote: Hi James A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Use .HTMLBody = "This is line 1.<br" & _ "This is line 2.<br" & _ "This is line 3.<br" & _ "This is line 4.<br<br<br" & _ RangetoHTML(rng) Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" It will create a htm file to create the html in your temp folder At the end of the function it delete this file 'Delete the htm file we used in this function Kill TempFile Finally, the code only generates the email, it doesn't send automatically. What do I need to change? .Display 'or use .Send -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J "Ron de Bruin" wrote: If I understand you correct? Why not simple filter for negative values in the B column and send the visible data to the two Recipients If this is what you want I wiil create a example for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi James I read it and reply tomorrow after work Bed time for me now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Column A, cells 1-150 are project numbers that are entered by the user, i.e. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
Ron,
It's just to make life slightly easier. I am going to accept the fact that this little step should not be done and just explain to user that they need to add their own messages to the data table. Thanks for all your help. Very good code. -- J "Ron de Bruin" wrote: Never try this James No time to try/test it now Ask it in a Outlook group -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Is there a way I can build this into the macro? The reason I ask is that the company require the email signature to be used on all emails. Turning off this email editor won't be acceptable for all other emails sent externally. This email will be sent internally, so I see no reason why we can uncheck for the purposes of the email, and then reselect once sent. If this can be done in the current macro that would be brilliant, if not is there another macro that can do the same job? Thanks for sticking with this. Everything else works great. James -- J "Ron de Bruin" wrote: If Word is your mail editor in Outlook it can give problems Try to uncheck it in Outlook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Found another problem (not with the code). I set up a dummy spreadsheet at home to test out the code. The code works fine and generates an email from Outlook with the required data. I have now tried to do exactly the same thing at work. The problem is that the macro code generates the email, but there is no table, only my HTML signature. Is there anything I can change in the code? Thanks James -- J "Ron de Bruin" wrote: Hi James A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Use .HTMLBody = "This is line 1.<br" & _ "This is line 2.<br" & _ "This is line 3.<br" & _ "This is line 4.<br<br<br" & _ RangetoHTML(rng) Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" It will create a htm file to create the html in your temp folder At the end of the function it delete this file 'Delete the htm file we used in this function Kill TempFile Finally, the code only generates the email, it doesn't send automatically. What do I need to change? .Display 'or use .Send -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email based on cell value
I hope to have time soon to find a fix for it
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, It's just to make life slightly easier. I am going to accept the fact that this little step should not be done and just explain to user that they need to add their own messages to the data table. Thanks for all your help. Very good code. -- J "Ron de Bruin" wrote: Never try this James No time to try/test it now Ask it in a Outlook group -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Is there a way I can build this into the macro? The reason I ask is that the company require the email signature to be used on all emails. Turning off this email editor won't be acceptable for all other emails sent externally. This email will be sent internally, so I see no reason why we can uncheck for the purposes of the email, and then reselect once sent. If this can be done in the current macro that would be brilliant, if not is there another macro that can do the same job? Thanks for sticking with this. Everything else works great. James -- J "Ron de Bruin" wrote: If Word is your mail editor in Outlook it can give problems Try to uncheck it in Outlook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Found another problem (not with the code). I set up a dummy spreadsheet at home to test out the code. The code works fine and generates an email from Outlook with the required data. I have now tried to do exactly the same thing at work. The problem is that the macro code generates the email, but there is no table, only my HTML signature. Is there anything I can change in the code? Thanks James -- J "Ron de Bruin" wrote: Hi James A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Use .HTMLBody = "This is line 1.<br" & _ "This is line 2.<br" & _ "This is line 3.<br" & _ "This is line 4.<br<br<br" & _ RangetoHTML(rng) Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" It will create a htm file to create the html in your temp folder At the end of the function it delete this file 'Delete the htm file we used in this function Kill TempFile Finally, the code only generates the email, it doesn't send automatically. What do I need to change? .Display 'or use .Send -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Genius! A couple of modifications needed. Firstly can I add a pre-defined message to the body of the email? Something like: Joe, These are the projects that have been over-allocated The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..." Also, what does this line do: TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Finally, the code only generates the email, it doesn't send automatically. What do I need to change? Thanks for your help. -- J "Ron de Bruin" wrote: Ok Try this one Change this in the code 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) Then set the filter field (B in your code i believe ?) 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" It will display the mail first this example so you can view the mail first Copy the macro and function below in a standard module in your excel workbook Sub Test_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim WS As Worksheet 'Name of the worksheet with the data Set WS = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = WS.Range("A1:D" & Rows.Count) 'Firstly, remove the AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = False .ScreenUpdating = False End With 'This example filters on the second column in the range (change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... rng.AutoFilter Field:=2, Criteria1:="<0" Set rng = Nothing On Error Resume Next Set rng = WS.AutoFilter.Range On Error GoTo 0 If rng Is Nothing Then GoTo EndMacro 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" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 EndMacro: 'Close AutoFilter WS.AutoFilterMode = False With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "James" wrote in message ... Ron, Filtering would work and would probably be the easiest option. As long as the macro filters the list, sends the range and then unfilters the list at the end, it won't be a problem. It would be great if you could set something up. Thanks alot for your help. James -- J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send email based on date value | Excel Programming | |||
can I get excel to send an email based on a cell result? | Excel Discussion (Misc queries) | |||
Automatically Send Email based on query | Excel Programming | |||
Send Email Based on Spreadsheet Criteria | Excel Programming | |||
Using VBA to send email based on form response | Excel Programming |