Email worksheets via name on worksheet

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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"