|
Export Excel Data to an Outlook Email
Sounds simple but this is proving a headache for me. I'm looking for any
smnippest of code out there that can handle the following. In column A I have the date an email should be sent. In Column B I have the email address to which the email is to be sent. In Column C I have the subject of the email In Column D I have the body of the email. In Column E I have the name of the person sending the email. The code I'm looking for needs to work like this. If I click on any cell in column A (on any date), nothing must happen, unless the date is 'today'. If the datre is today this will trigger the data in columns b,c,d,e to be entered into an email launched from Outlook in the roder highlighted below. If there are any clever boffins out there please help me... Gordon |
Export Excel Data to an Outlook Email
Hi Gordon
Start here http://www.rondebruin.nl/mail/folder3/message.htm In the yes/no column use a formula like this =IF(C1= TODAY(),"Yes","No") See also tje tips http://www.rondebruin.nl/mail/tips2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Sounds simple but this is proving a headache for me. I'm looking for any smnippest of code out there that can handle the following. In column A I have the date an email should be sent. In Column B I have the email address to which the email is to be sent. In Column C I have the subject of the email In Column D I have the body of the email. In Column E I have the name of the person sending the email. The code I'm looking for needs to work like this. If I click on any cell in column A (on any date), nothing must happen, unless the date is 'today'. If the datre is today this will trigger the data in columns b,c,d,e to be entered into an email launched from Outlook in the roder highlighted below. If there are any clever boffins out there please help me... Gordon |
Export Excel Data to an Outlook Email
Thanks for that...
Have you got a working example of this code... Gordon "Ron de Bruin" wrote: Hi Gordon Start here http://www.rondebruin.nl/mail/folder3/message.htm In the yes/no column use a formula like this =IF(C1= TODAY(),"Yes","No") See also tje tips http://www.rondebruin.nl/mail/tips2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Sounds simple but this is proving a headache for me. I'm looking for any smnippest of code out there that can handle the following. In column A I have the date an email should be sent. In Column B I have the email address to which the email is to be sent. In Column C I have the subject of the email In Column D I have the body of the email. In Column E I have the name of the person sending the email. The code I'm looking for needs to work like this. If I click on any cell in column A (on any date), nothing must happen, unless the date is 'today'. If the datre is today this will trigger the data in columns b,c,d,e to be entered into an email launched from Outlook in the roder highlighted below. If there are any clever boffins out there please help me... Gordon |
Export Excel Data to an Outlook Email
Hi Gordon
Try this, use display in the code to test 'In column A I have the date an email should be sent. 'In Column B I have the email address to which the email is to be sent. 'In Column C I have the subject of the email 'In Column D I have the body of the email. 'In Column E I have the name of the person sending the email. 'In column F copy down this formula =IF(A1= TODAY(),"Yes","No") Sub TestFile() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "*@*" And LCase(cell.Offset(0, 4).Value) = "yes" Then Set OutMail = OutApp.CreateItem(0) With OutMail .To = cell.Value .Subject = cell.Offset(0, 1).Value .Body = cell.Offset(0, 2).Value .Send 'Or use Display End With Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Thanks for that... Have you got a working example of this code... Gordon "Ron de Bruin" wrote: Hi Gordon Start here http://www.rondebruin.nl/mail/folder3/message.htm In the yes/no column use a formula like this =IF(C1= TODAY(),"Yes","No") See also tje tips http://www.rondebruin.nl/mail/tips2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Sounds simple but this is proving a headache for me. I'm looking for any smnippest of code out there that can handle the following. In column A I have the date an email should be sent. In Column B I have the email address to which the email is to be sent. In Column C I have the subject of the email In Column D I have the body of the email. In Column E I have the name of the person sending the email. The code I'm looking for needs to work like this. If I click on any cell in column A (on any date), nothing must happen, unless the date is 'today'. If the datre is today this will trigger the data in columns b,c,d,e to be entered into an email launched from Outlook in the roder highlighted below. If there are any clever boffins out there please help me... Gordon |
Export Excel Data to an Outlook Email
Thanks Ron...
"Ron de Bruin" wrote: Hi Gordon Try this, use display in the code to test 'In column A I have the date an email should be sent. 'In Column B I have the email address to which the email is to be sent. 'In Column C I have the subject of the email 'In Column D I have the body of the email. 'In Column E I have the name of the person sending the email. 'In column F copy down this formula =IF(A1= TODAY(),"Yes","No") Sub TestFile() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "*@*" And LCase(cell.Offset(0, 4).Value) = "yes" Then Set OutMail = OutApp.CreateItem(0) With OutMail .To = cell.Value .Subject = cell.Offset(0, 1).Value .Body = cell.Offset(0, 2).Value .Send 'Or use Display End With Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Thanks for that... Have you got a working example of this code... Gordon "Ron de Bruin" wrote: Hi Gordon Start here http://www.rondebruin.nl/mail/folder3/message.htm In the yes/no column use a formula like this =IF(C1= TODAY(),"Yes","No") See also tje tips http://www.rondebruin.nl/mail/tips2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Sounds simple but this is proving a headache for me. I'm looking for any smnippest of code out there that can handle the following. In column A I have the date an email should be sent. In Column B I have the email address to which the email is to be sent. In Column C I have the subject of the email In Column D I have the body of the email. In Column E I have the name of the person sending the email. The code I'm looking for needs to work like this. If I click on any cell in column A (on any date), nothing must happen, unless the date is 'today'. If the datre is today this will trigger the data in columns b,c,d,e to be entered into an email launched from Outlook in the roder highlighted below. If there are any clever boffins out there please help me... Gordon |
| All times are GMT +1. The time now is 11:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com