E-mail Macro: MS Outlook

[John]

How do I create a macro that will send an entire Excel
file to one person as an attachment, then send only the
active worksheet to 3 people in the body of an e-mail
(mail recipient method), and then print only 5 of the 10
worksheets in the file?

Any help with this will be greatly appreciated for if I
spend any more time trying to figure this out on my own as
as a macro novice, my head is going to explode.

 

[Paul Falla]

The easiest way to do this is to record the macro
(Tools/macros/record new macro), but if this doesnot go
far enough for what you need to do, here is some code that
may help you. I use it for emailing reports to users on a
monthly basis.

Sub EmailReports()
'
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail



'====================================
'| '.To = emailadress |
'| '.CC = more emailadresses |
'| '.Subject = "subject" |
'| '.Body = "" |
'====================================

'Email recipients sheets'
Set objOutlook = CreateObject
("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
.To = "email address of recipient"
.Subject = "Subject line of email"
.Body = vbCrLf & "Dear recipient" & vbCrLf & "
Please find attached, the regular monthly reports" &
vbCrLf & vbCrLf & "Kind Regards" & vbCrLf & vbCrLf & "Your
Name" & vbCrLf & vbCrLf & "Your title" & vbCrLf
.Attachments.Add "The network location of the
relevant workbook"
.display
End With

Copy the 'email recipients sheets' section above and
change the email address for each of the relevant
recipients in turn - IE if you have 5 different
recipients, repeat this section 5 times changing the email
addresses accordingly.

For the printing part of your question, the following code
may help you:

Sub PrintSelectedSheets()
'This section selects the sheets to print.
Sheets(Array("Name of sheet1", "Name of
sheet2", "Name of sheet3", "Name of sheet4", "Name of
sheet5",)).Select
Sheets("Name of sheet1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True

End Sub

You will have to make a couple of changes to the above
sets of code (like sheet names, email addresses etc.), but
it should hopefully get you headed in the right direction.

Kind regards
Paul Falla