VBA Email Cell Contents When Filled In
Good morning. I have created the following spreadsheet to log requests
A (email address) B ("yes") C (Date of Request) D (Time of Request) E (Description of Request) F (Request Due) This will be on ongoing log. I would like for when a new row is completed, that a "confirmation of request" email go out to the email address listed. I tried some things by looking at different posts and I am still getting errors. Any help would be greatly appreciated. This is the code that I currently have that is not working. Please let me know if I am way off. ..To = cell.Value ..Subject = "Request Confirmation :" & " " & _ Cells(cell.Row, "E").Value & " " & _ Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ " Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _ " Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _ " Description : " & Cells(cell.Row, "E").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "F").Value |
VBA Email Cell Contents When Filled In
Have you see
http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pt_lily" wrote in message ... Good morning. I have created the following spreadsheet to log requests A (email address) B ("yes") C (Date of Request) D (Time of Request) E (Description of Request) F (Request Due) This will be on ongoing log. I would like for when a new row is completed, that a "confirmation of request" email go out to the email address listed. I tried some things by looking at different posts and I am still getting errors. Any help would be greatly appreciated. This is the code that I currently have that is not working. Please let me know if I am way off. .To = cell.Value .Subject = "Request Confirmation :" & " " & _ Cells(cell.Row, "E").Value & " " & _ Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ " Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _ " Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _ " Description : " & Cells(cell.Row, "E").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "F").Value |
VBA Email Cell Contents When Filled In
Okay. So I worked with the code on the website. It is sending the emails
now, but I am not getting any information in the body of the email. Again, any help is appreciated. Sub EmailMacro() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("A").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail ..To = cell.Value ..Subject = "Report Request Confirmation" ..Body = " DaRon, " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ "Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _ " Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _ " Description : " & Cells(cell.Row, "E").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "F").Value ..Send End With On Error GoTo 0 Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Have you see http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pt_lily" wrote in message ... Good morning. I have created the following spreadsheet to log requests A (email address) B ("yes") C (Date of Request) D (Time of Request) E (Description of Request) F (Request Due) This will be on ongoing log. I would like for when a new row is completed, that a "confirmation of request" email go out to the email address listed. I tried some things by looking at different posts and I am still getting errors. Any help would be greatly appreciated. This is the code that I currently have that is not working. Please let me know if I am way off. .To = cell.Value .Subject = "Request Confirmation :" & " " & _ Cells(cell.Row, "E").Value & " " & _ Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ " Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _ " Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _ " Description : " & Cells(cell.Row, "E").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "F").Value |
VBA Email Cell Contents When Filled In
Your mail addresses are in A so this will blow
cell.Offset(0, -1).Value It will try to display the value in one column to the left of A If B is the name Then Try .Body = " DaRon, " & cell.Offset(0, 1).Value & vbNewLine & vbNewLine & _ "Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & cell.Offset(0, 2).Value.Value & vbNewLine & _ " Time of Request : " & cell.Offset(0, 3).Value & vbNewLine & _ " Description : " & cell.Offset(0, 4).Value & vbNewLine & _ " Deadline : " & cell.Offset(0, 5).Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pt_lily" wrote in message ... Okay. So I worked with the code on the website. It is sending the emails now, but I am not getting any information in the body of the email. Again, any help is appreciated. Sub EmailMacro() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("A").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = cell.Value .Subject = "Report Request Confirmation" .Body = " DaRon, " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ "Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _ " Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _ " Description : " & Cells(cell.Row, "E").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "F").Value .Send End With On Error GoTo 0 Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Have you see http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pt_lily" wrote in message ... Good morning. I have created the following spreadsheet to log requests A (email address) B ("yes") C (Date of Request) D (Time of Request) E (Description of Request) F (Request Due) This will be on ongoing log. I would like for when a new row is completed, that a "confirmation of request" email go out to the email address listed. I tried some things by looking at different posts and I am still getting errors. Any help would be greatly appreciated. This is the code that I currently have that is not working. Please let me know if I am way off. .To = cell.Value .Subject = "Request Confirmation :" & " " & _ Cells(cell.Row, "E").Value & " " & _ Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ " Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _ " Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _ " Description : " & Cells(cell.Row, "E").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "F").Value |
VBA Email Cell Contents When Filled In
Thank you so much for your help!! It works great now.
"Ron de Bruin" wrote: Your mail addresses are in A so this will blow cell.Offset(0, -1).Value It will try to display the value in one column to the left of A If B is the name Then Try .Body = " DaRon, " & cell.Offset(0, 1).Value & vbNewLine & vbNewLine & _ "Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & cell.Offset(0, 2).Value.Value & vbNewLine & _ " Time of Request : " & cell.Offset(0, 3).Value & vbNewLine & _ " Description : " & cell.Offset(0, 4).Value & vbNewLine & _ " Deadline : " & cell.Offset(0, 5).Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pt_lily" wrote in message ... Okay. So I worked with the code on the website. It is sending the emails now, but I am not getting any information in the body of the email. Again, any help is appreciated. Sub EmailMacro() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("A").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = cell.Value .Subject = "Report Request Confirmation" .Body = " DaRon, " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ "Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _ " Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _ " Description : " & Cells(cell.Row, "E").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "F").Value .Send End With On Error GoTo 0 Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub "Ron de Bruin" wrote: Have you see http://www.rondebruin.nl/mail/folder3/message.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pt_lily" wrote in message ... Good morning. I have created the following spreadsheet to log requests A (email address) B ("yes") C (Date of Request) D (Time of Request) E (Description of Request) F (Request Due) This will be on ongoing log. I would like for when a new row is completed, that a "confirmation of request" email go out to the email address listed. I tried some things by looking at different posts and I am still getting errors. Any help would be greatly appreciated. This is the code that I currently have that is not working. Please let me know if I am way off. .To = cell.Value .Subject = "Request Confirmation :" & " " & _ Cells(cell.Row, "E").Value & " " & _ Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ " Please confirm the following report request." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Date of Request : " & Cells(cell.Row, "C").Value & vbNewLine & _ " Time of Request : " & Cells(cell.Row, "D").Value & vbNewLine & _ " Description : " & Cells(cell.Row, "E").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "F").Value |
All times are GMT +1. The time now is 09:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com