| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email from Excel with Selection of Cell Value
Hi all,
I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm. However, I would like to include the value of the cell in the subject line. For example, I would like to have the contents in row 3, col F and row 3, col G appear in the subject after "reminder" How do I include this in the codes. ..Subject = "Reminder" And is it possible to include the row's content into the body of the msg? Thank in advance. cheers, franciz |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email from Excel with Selection of Cell Value
On each page there is a link to a tips page
You can use this .Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "franciz" wrote in message ... Hi all, I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm. However, I would like to include the value of the cell in the subject line. For example, I would like to have the contents in row 3, col F and row 3, col G appear in the subject after "reminder" How do I include this in the codes. .Subject = "Reminder" And is it possible to include the row's content into the body of the msg? Thank in advance. cheers, franciz |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email from Excel with Selection of Cell Value
Hi Ron,
Thank you for your response and guide,the code will produce always for cells "F3" and "G3" in the subject. Is it possible to have Excel automatically include the cell's contents in col F and col G to the corresponsing row its send. What I have in mind is that : For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will also include the Cell contents from C5 and F7 as well as C5 and G7 in the subject line after "Reminder". ie can excel loop thru and insert the contents found in the cells Thank in advance. cheers, franciz "Ron de Bruin" wrote: On each page there is a link to a tips page You can use this .Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "franciz" wrote in message ... Hi all, I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm. However, I would like to include the value of the cell in the subject line. For example, I would like to have the contents in row 3, col F and row 3, col G appear in the subject after "reminder" How do I include this in the codes. .Subject = "Reminder" And is it possible to include the row's content into the body of the msg? Thank in advance. cheers, franciz |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email from Excel with Selection of Cell Value
Yes, that is possible
Show the code that you use now. I will change it for you then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "franciz" wrote in message ... Hi Ron, Thank you for your response and guide,the code will produce always for cells "F3" and "G3" in the subject. Is it possible to have Excel automatically include the cell's contents in col F and col G to the corresponsing row its send. What I have in mind is that : For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will also include the Cell contents from C5 and F7 as well as C5 and G7 in the subject line after "Reminder". ie can excel loop thru and insert the contents found in the cells Thank in advance. cheers, franciz "Ron de Bruin" wrote: On each page there is a link to a tips page You can use this .Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "franciz" wrote in message ... Hi all, I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm. However, I would like to include the value of the cell in the subject line. For example, I would like to have the contents in row 3, col F and row 3, col G appear in the subject after "reminder" How do I include this in the codes. .Subject = "Reminder" And is it possible to include the row's content into the body of the msg? Thank in advance. cheers, franciz |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email from Excel with Selection of Cell Value
Hi Ron,
Thanks in advance! I have attached here the table and the codes adapted from you. Here they are : Name is in Col A, Emails in Col B, Date in Col C with formula = =IF((E2-TODAY())2,"No","Yes"), Remind in Col D, Deadline in Col E, Event in Col F, Prd in Col G and Hldg in Col H. Using the below code, I always get the subject line with "Reminder : Sales xyz" for the 3 mail send. Is it possbile to have the codes loops thru the cells and put the relevant values in the subject line. Further, the msg body only appear as " I refers to the above subject event, Please be remind that the date to act on this is approaching. Detail are : " Its does not shows the cells' contents as desired, ie the contents in Col E, Col F, Col G and Col H Name Emails Date Remind Deadline Event Prd Holdg franciz Yes 6-Feb-07 Tender ABC 15 francis Yes 6-Feb-07 Sales xyz 10 Joey Yes 5-Feb-07 Auction abc 5 Option Explicit ' This marco is with courtesy from Ron de Bruin @ http://www.rondebruin.nl/tips.htm Sub Reminder2() 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("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" _ And LCase(cell.Offset(0, 2).Value) < "send" Then Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = cell.Value .Subject = "Reminder :" & " " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ "I refers to the above subject event, Please be remind that the date to act on this is approaching." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Event : " & ThisWorkbook.Sheets("sheet1").Range("F").Value & vbNewLine & _"Prd : " & ThisWorkbook.Sheets("sheet1").Range("G").Value & vbNewLine & _"Hldg : " & ThisWorkbook.Sheets("sheet1").Range("H").Value & vbNewLine & _ " Deadline : " & This Workbook.Sheets("sheet1").Range("E") .Send 'Or use Display End With On Error GoTo 0 cell.Offset(0, 2).Value = "send" Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub Appreciate your help in this. cheers, franciz "Ron de Bruin" wrote: Yes, that is possible Show the code that you use now. I will change it for you then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "franciz" wrote in message ... Hi Ron, Thank you for your response and guide,the code will produce always for cells "F3" and "G3" in the subject. Is it possible to have Excel automatically include the cell's contents in col F and col G to the corresponsing row its send. What I have in mind is that : For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will also include the Cell contents from C5 and F7 as well as C5 and G7 in the subject line after "Reminder". ie can excel loop thru and insert the contents found in the cells Thank in advance. cheers, franciz "Ron de Bruin" wrote: On each page there is a link to a tips page You can use this .Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "franciz" wrote in message ... Hi all, I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm. However, I would like to include the value of the cell in the subject line. For example, I would like to have the contents in row 3, col F and row 3, col G appear in the subject after "reminder" How do I include this in the codes. .Subject = "Reminder" And is it possible to include the row's content into the body of the msg? Thank in advance. cheers, franciz |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Email from Excel with Selection of Cell Value
Hi franciz
When you use this it use the values from the row .To = cell.Value .Subject = "Reminder :" & " " & _ Cells(cell.Row, "F").Value & " " & _ Cells(cell.Row, "G").Value .Body = " Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ " I refers to the above subject event, Please be remind that the date to act on this is approaching." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Event : " & Cells(cell.Row, "F").Value & vbNewLine & _ " Prd : " & Cells(cell.Row, "G").Value & vbNewLine & _ " Hldg : " & Cells(cell.Row, "H").Value & vbNewLine & _ " Deadline : " & Cells(cell.Row, "E").Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "franciz" wrote in message ... Hi Ron, Thanks in advance! I have attached here the table and the codes adapted from you. Here they are : Name is in Col A, Emails in Col B, Date in Col C with formula = =IF((E2-TODAY())2,"No","Yes"), Remind in Col D, Deadline in Col E, Event in Col F, Prd in Col G and Hldg in Col H. Using the below code, I always get the subject line with "Reminder : Sales xyz" for the 3 mail send. Is it possbile to have the codes loops thru the cells and put the relevant values in the subject line. Further, the msg body only appear as " I refers to the above subject event, Please be remind that the date to act on this is approaching. Detail are : " Its does not shows the cells' contents as desired, ie the contents in Col E, Col F, Col G and Col H Name Emails Date Remind Deadline Event Prd Holdg franciz Yes 6-Feb-07 Tender ABC 15 francis Yes 6-Feb-07 Sales xyz 10 Joey Yes 5-Feb-07 Auction abc 5 Option Explicit ' This marco is with courtesy from Ron de Bruin @ http://www.rondebruin.nl/tips.htm Sub Reminder2() 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("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" _ And LCase(cell.Offset(0, 2).Value) < "send" Then Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = cell.Value .Subject = "Reminder :" & " " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ "I refers to the above subject event, Please be remind that the date to act on this is approaching." & vbNewLine & vbNewLine & _ " Detail are : " & vbNewLine & vbNewLine & _ " Event : " & ThisWorkbook.Sheets("sheet1").Range("F").Value & vbNewLine & _"Prd : " & ThisWorkbook.Sheets("sheet1").Range("G").Value & vbNewLine & _"Hldg : " & ThisWorkbook.Sheets("sheet1").Range("H").Value & vbNewLine & _ " Deadline : " & This Workbook.Sheets("sheet1").Range("E") .Send 'Or use Display End With On Error GoTo 0 cell.Offset(0, 2).Value = "send" Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub Appreciate your help in this. cheers, franciz "Ron de Bruin" wrote: Yes, that is possible Show the code that you use now. I will change it for you then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "franciz" wrote in message ... Hi Ron, Thank you for your response and guide,the code will produce always for cells "F3" and "G3" in the subject. Is it possible to have Excel automatically include the cell's contents in col F and col G to the corresponsing row its send. What I have in mind is that : For example, in row 5 and 7, the cell C5 and C7 have a "yes". Excel will also include the Cell contents from C5 and F7 as well as C5 and G7 in the subject line after "Reminder". ie can excel loop thru and insert the contents found in the cells Thank in advance. cheers, franciz "Ron de Bruin" wrote: On each page there is a link to a tips page You can use this .Subject = "reminder " & ThisWorkbook.Sheets("Sheet1").Range("F3").Value & " " & ThisWorkbook.Sheets("Sheet1").Range("G3").Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "franciz" wrote in message ... Hi all, I am using the codes from Ron"s <http://www.rondebruin.nl/sendmail.htm. However, I would like to include the value of the cell in the subject line. For example, I would like to have the contents in row 3, col F and row 3, col G appear in the subject after "reminder" How do I include this in the codes. .Subject = "Reminder" And is it possible to include the row's content into the body of the msg? Thank in advance. cheers, franciz |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Macro how to create email link for the email addresses in aRange or Selection | Excel Worksheet Functions | |||
| Code to send email to address within selection in Excel workbook | Excel Discussion (Misc queries) | |||
| Sending email with selection of data from spreadsheet | Excel Programming | |||
| Need a macro to Copy a selection and paste into a new email. | Excel Discussion (Misc queries) | |||
| sending a singular email by selection | Excel Programming | |||
Hybrid Mode
