mail worksheet (multiple)

  • Thread starter Thread starter stuart
  • Start date Start date
S

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
 
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
 
Back
Top