Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email worksheets via name on worksheet
I have used a variety of tools discovered from these communities to take rows
of data and put them into multiple worksheets, so now I have 64 worksheets with the name of the worksheet being the email address. Is there an automated way to cycle through each worksheet and generate an email containing (attaching) only the worksheet as an excel worksheet? If there needs to be a manual step of sending that would be OK as well, it might be good for me to double check the email address before sending anyway - what do you guys think? I have been experimenting all weekend with no good results. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email worksheets via name on worksheet
Have you tried something from here?
http://www.ozgrid.com/VBA/send-email.htm I'm guessing you'd change your recipient to be the worksheet name. Post some code and I'm sure someone could help. "Rookie_User" wrote: I have used a variety of tools discovered from these communities to take rows of data and put them into multiple worksheets, so now I have 64 worksheets with the name of the worksheet being the email address. Is there an automated way to cycle through each worksheet and generate an email containing (attaching) only the worksheet as an excel worksheet? If there needs to be a manual step of sending that would be OK as well, it might be good for me to double check the email address before sending anyway - what do you guys think? I have been experimenting all weekend with no good results. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email worksheets via name on worksheet
Hi,
Maybe the code below would give you some ideas. It will compose an email message using your default email application and you will have to manually click on the "Send" button. Public Sub SendEmail() Dim sht As Worksheet Dim wb As Workbook Dim wbToSend As Workbook Dim iSheetsInNewWBOrig As Integer iSheetsInNewWBOrig = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 1 Application.DisplayAlerts = False Set wb = ActiveWorkbook For Each sht In wb.Worksheets Set wbToSend = Application.Workbooks.Add sht.Copy wbToSend.Worksheets(1) wbToSend.Worksheets(2).Delete Application.Dialogs(xlDialogSendMail).Show arg1:=sht.Name, arg2:="Test Email" wbToSend.Close False Set wbToSend = Nothing Next sht Application.SheetsInNewWorkbook = iSheetsInNewWBOrig Application.DisplayAlerts = True End Sub Also, google for "Excel VBA send email", and you'll get a lot of results. The first one of which will be this link: http://www.rondebruin.nl/sendmail.htm -- Hope that helps. Vergel Adriano "Rookie_User" wrote: I have used a variety of tools discovered from these communities to take rows of data and put them into multiple worksheets, so now I have 64 worksheets with the name of the worksheet being the email address. Is there an automated way to cycle through each worksheet and generate an email containing (attaching) only the worksheet as an excel worksheet? If there needs to be a manual step of sending that would be OK as well, it might be good for me to double check the email address before sending anyway - what do you guys think? I have been experimenting all weekend with no good results. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email worksheets via name on worksheet
Do you get the same red text or error on this line?
Application.Dialogs(xlDialogSendMail).Show arg1:=sht.Name, arg2:="Test Email" Thank you, I will try to work with it and yes I love the site you recommended - it has helped me tremendously! "Vergel Adriano" wrote: Hi, Maybe the code below would give you some ideas. It will compose an email message using your default email application and you will have to manually click on the "Send" button. Public Sub SendEmail() Dim sht As Worksheet Dim wb As Workbook Dim wbToSend As Workbook Dim iSheetsInNewWBOrig As Integer iSheetsInNewWBOrig = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 1 Application.DisplayAlerts = False Set wb = ActiveWorkbook For Each sht In wb.Worksheets Set wbToSend = Application.Workbooks.Add sht.Copy wbToSend.Worksheets(1) wbToSend.Worksheets(2).Delete Application.Dialogs(xlDialogSendMail).Show arg1:=sht.Name, arg2:="Test Email" wbToSend.Close False Set wbToSend = Nothing Next sht Application.SheetsInNewWorkbook = iSheetsInNewWBOrig Application.DisplayAlerts = True End Sub Also, google for "Excel VBA send email", and you'll get a lot of results. The first one of which will be this link: http://www.rondebruin.nl/sendmail.htm -- Hope that helps. Vergel Adriano "Rookie_User" wrote: I have used a variety of tools discovered from these communities to take rows of data and put them into multiple worksheets, so now I have 64 worksheets with the name of the worksheet being the email address. Is there an automated way to cycle through each worksheet and generate an email containing (attaching) only the worksheet as an excel worksheet? If there needs to be a manual step of sending that would be OK as well, it might be good for me to double check the email address before sending anyway - what do you guys think? I have been experimenting all weekend with no good results. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email worksheets via name on worksheet
No, I don't get an error. Just to be sure, make sure everything is on one
line, or you can put it in 2 lines but put an underscore to the end of the first line. Application.Dialogs(xlDialogSendMail).Show arg1:=sht.Name, _ arg2:="Test Email" -- Hope that helps. Vergel Adriano "Rookie_User" wrote: Do you get the same red text or error on this line? Application.Dialogs(xlDialogSendMail).Show arg1:=sht.Name, arg2:="Test Email" Thank you, I will try to work with it and yes I love the site you recommended - it has helped me tremendously! "Vergel Adriano" wrote: Hi, Maybe the code below would give you some ideas. It will compose an email message using your default email application and you will have to manually click on the "Send" button. Public Sub SendEmail() Dim sht As Worksheet Dim wb As Workbook Dim wbToSend As Workbook Dim iSheetsInNewWBOrig As Integer iSheetsInNewWBOrig = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 1 Application.DisplayAlerts = False Set wb = ActiveWorkbook For Each sht In wb.Worksheets Set wbToSend = Application.Workbooks.Add sht.Copy wbToSend.Worksheets(1) wbToSend.Worksheets(2).Delete Application.Dialogs(xlDialogSendMail).Show arg1:=sht.Name, arg2:="Test Email" wbToSend.Close False Set wbToSend = Nothing Next sht Application.SheetsInNewWorkbook = iSheetsInNewWBOrig Application.DisplayAlerts = True End Sub Also, google for "Excel VBA send email", and you'll get a lot of results. The first one of which will be this link: http://www.rondebruin.nl/sendmail.htm -- Hope that helps. Vergel Adriano "Rookie_User" wrote: I have used a variety of tools discovered from these communities to take rows of data and put them into multiple worksheets, so now I have 64 worksheets with the name of the worksheet being the email address. Is there an automated way to cycle through each worksheet and generate an email containing (attaching) only the worksheet as an excel worksheet? If there needs to be a manual step of sending that would be OK as well, it might be good for me to double check the email address before sending anyway - what do you guys think? I have been experimenting all weekend with no good results. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email one worksheet to email address on worksheet | Excel Worksheet Functions | |||
Email single worksheets | Excel Discussion (Misc queries) | |||
Email worksheets | Excel Discussion (Misc queries) | |||
Email individual worksheets?? | Excel Worksheet Functions | |||
Email worksheet from a list of names and email | Excel Discussion (Misc queries) |