Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to send email to address within selection in Excel workbook
You recently helped me a lot with vba code to send an email from Excel. -
Thankyou! I have a different workbook in which I have set up a macro to send a selection (it works this time!), however I would like the email sent to an email address which is contained within the selection. I am using the RondeBruin code with adjustments for the body text and to send to an address in a particular cell - but this means that every time I change the selection I have to remember to go into Visual Basic to change the cell reference - otherwise the email for that particular selection is not sent to the relevant person. Is there a piece of code I can insert which will send the email to an address which is within the current selection, rather than a fixed range of cells? (The address would always be in the same position, relative to the start of the currently selected section, if that helps). If it helps you to know, my table contains a list of teachers for each of several different schools, together with the subjects they teach etc. I need to send an email to each school with a workbook containing ONLY their own teachers, for them to update - hence each selection of teachers needs a different email address. Very many thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to send email to address within selection in Excel workbook
I am afraid I don't understand completely how your table is setup, but could
you do something simple like search for the @ symbol, and use that cell's value? Cells.Find(What:="@", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate EmailAddress = ActiveCell.Value Just a thought, hope it helps! -- Best Regards, Luke M "vic1" wrote: You recently helped me a lot with vba code to send an email from Excel. - Thankyou! I have a different workbook in which I have set up a macro to send a selection (it works this time!), however I would like the email sent to an email address which is contained within the selection. I am using the RondeBruin code with adjustments for the body text and to send to an address in a particular cell - but this means that every time I change the selection I have to remember to go into Visual Basic to change the cell reference - otherwise the email for that particular selection is not sent to the relevant person. Is there a piece of code I can insert which will send the email to an address which is within the current selection, rather than a fixed range of cells? (The address would always be in the same position, relative to the start of the currently selected section, if that helps). If it helps you to know, my table contains a list of teachers for each of several different schools, together with the subjects they teach etc. I need to send an email to each school with a workbook containing ONLY their own teachers, for them to update - hence each selection of teachers needs a different email address. Very many thanks in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to send email to address within selection in Excel workbook
If it helps you to know, my table contains a list of teachers for each of
several different schools, together with the subjects they teach etc. I need to send an email to each school with a workbook containing ONLY their own teachers, for them to update - hence each selection of teachers needs a different email address. If I understand you correct Check out this http://www.rondebruin.nl/mail/folder3/row2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "vic1" wrote in message ... You recently helped me a lot with vba code to send an email from Excel. - Thankyou! I have a different workbook in which I have set up a macro to send a selection (it works this time!), however I would like the email sent to an email address which is contained within the selection. I am using the RondeBruin code with adjustments for the body text and to send to an address in a particular cell - but this means that every time I change the selection I have to remember to go into Visual Basic to change the cell reference - otherwise the email for that particular selection is not sent to the relevant person. Is there a piece of code I can insert which will send the email to an address which is within the current selection, rather than a fixed range of cells? (The address would always be in the same position, relative to the start of the currently selected section, if that helps). If it helps you to know, my table contains a list of teachers for each of several different schools, together with the subjects they teach etc. I need to send an email to each school with a workbook containing ONLY their own teachers, for them to update - hence each selection of teachers needs a different email address. Very many thanks in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to send email to address within selection in Excel workbo
Thank you both for your help - they weren't quite what I needed, but they did
enable me to solve the problem myself! Because I had a selection, not a Range (I didn't want to have to go back into the code to change it for every school I sent to), Ron's suggestion wasn't quite right and I wasn't 100% certain how to fit Luke's suggestion into my code. However, the solution was even simpler: I just replaced the existing code "In ThisWorkbook.Sheets("TEACHERS") _ .Range("C2:c17")" with the single word "Source" - and it does just what I needed! Thank you both very much! "Ron de Bruin" wrote: If it helps you to know, my table contains a list of teachers for each of several different schools, together with the subjects they teach etc. I need to send an email to each school with a workbook containing ONLY their own teachers, for them to update - hence each selection of teachers needs a different email address. If I understand you correct Check out this http://www.rondebruin.nl/mail/folder3/row2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "vic1" wrote in message ... You recently helped me a lot with vba code to send an email from Excel. - Thankyou! I have a different workbook in which I have set up a macro to send a selection (it works this time!), however I would like the email sent to an email address which is contained within the selection. I am using the RondeBruin code with adjustments for the body text and to send to an address in a particular cell - but this means that every time I change the selection I have to remember to go into Visual Basic to change the cell reference - otherwise the email for that particular selection is not sent to the relevant person. Is there a piece of code I can insert which will send the email to an address which is within the current selection, rather than a fixed range of cells? (The address would always be in the same position, relative to the start of the currently selected section, if that helps). If it helps you to know, my table contains a list of teachers for each of several different schools, together with the subjects they teach etc. I need to send an email to each school with a workbook containing ONLY their own teachers, for them to update - hence each selection of teachers needs a different email address. Very many thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
send 1 sheet of a workbook in email - Excel 07 | Excel Discussion (Misc queries) | |||
send wkbk as an email attachment with an email address copied from | Excel Discussion (Misc queries) | |||
How prevent email address from being hotlinked to open send msg wi | Excel Worksheet Functions | |||
How do I send/email macros from my excel workbook to someone else? | Excel Worksheet Functions | |||
Loop through email address list to send e-mails | Excel Discussion (Misc queries) |