Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a cell is set to email contents-cannot undo-changes my formatting | Excel Discussion (Misc queries) | |||
How can i email the contents of a userform? | Excel Programming | |||
send email from cell contents | Excel Discussion (Misc queries) | |||
Email Range Contents | Excel Programming | |||
Excel contents to Outlook email | Excel Programming |