mail worksheet (multiple)

[stuart]

Is there any way to create a macro which will mail
particular worksheets to users without the whole
workbook? For example, I have 15 worksheets in my
workbook, each sheet relates to a specific person, and I
only want them to see their corresponding sheet?

I can email a workbook using the code below, but that's
it (my VBA skills are non existent!) Any help appreciated

Sub test()

Dim objOut As Outlook.Application
Dim objMess As Outlook.MailItem

Set objOut = CreateObject("Outlook.Application")
Set objMess = objOut.CreateItem(olMailItem)

strto = "(e-mail address removed)"
strsub = "test "
strbody = "Hi there"
With objMess
..To = strto
..Subject = strsub
..Body = strbody
..Display
..Attachments.Add ThisWorkbook.FullName
..Send
End With

End Sub


Stuart

 

[Ron de Bruin]

Hi Stuart

See my webpage for example code
http://www.rondebruin.nl/sendmail.htm

I think you like this one
http://www.rondebruin.nl/sendmail.htm#every

 

[Tom Ogilvy]

for each sh in Thisworkbook.worksheets
sh.copy
sName = thisworkbook.Path & "\" & sh.name & ".xls"
ActiveWorkbook.SaveAs sName
ActiveWorkbook.Close SaveChanges:=False
' no send workbook sName
' .Attachments.Add sName
Kill sName
Next


Regards,
Tom Ogilvy

 

[stuart]

Thanks for all your help - the website was particularly
useful.

Stuart

 

[Ron de Bruin]

You are welcome