Email Code Modification Help

[wom2007]

Hi, the code below emails a workbook to any email address specified. I would like your help in modifying it so that I can send five separate tabs in the same workbook to five different email address. Thanks for your help.

Sub Email()



'Input box to verify password

Dim myPassword As String

myPassword = InputBox(prompt:="Please enter the password to proceed:", _
Title:="Password is required to auto-email this file.")

If myPassword <> "Password" Then
MsgBox prompt:="Click OK to return to Report.", _
Title:="Cancelled -- correct password not entered", _
Buttons:=16

Else
Dim Resp As Integer
Resp = MsgBox(prompt:="Click Yes to review email, No to immediately send, or Cancel.", _
Title:="Email options: Want to review email before sending?", _
Buttons:=3 + 32)

End If

Select Case Resp

'Yes was clicked, user wants to review email first
Case Is = 6
Dim myOutlook As Object
Dim myMailItem As Object

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

With otlNewMail
.To = "JohnDoe@anywhere.com; JaneDoe@anyplace.com"
.CC = “MarySmith@anywho.com”
.Subject = “Email from me”
.Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Ben" & Chr(13) & Chr(13)
.Attachments.Add fName
.Display

End With



Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing


'If no is clicked
Case Is = 7
Dim myOutlok As Object
Dim myMailItm As Object

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

With otlNewMail
.To = "JohnDoe@anywhere.com; JaneDoe@anyplace.com"
.CC = “MarySmith@anywho.com”
.Subject = “Email from me”
.Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Ben" & Chr(13) & Chr(13)
.Attachments.Add fName
.Send

End With

otlApp.Quit

Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing



'If Cancel is clicked
Case Is = 2
MsgBox prompt:="Click OK to return to Report.", _
Title:="EMAIL CANCELLED", _
Buttons:=64

End Select

End Sub

 

[ClulessChris]

Wom,

Please excuse me but I’ve taken the liberty of condensing your posted routine a little:

Sub Email()
    Dim myPassword          As String
    Dim Resp                As Integer
    Dim otlApp              As New Outlook.Application
    Dim myMailItem          As Outlook.MailItem
    
    'Input box to verify password
    myPassword = InputBox(prompt:="Please enter the password to proceed:", _
    Title:="Password is required to auto-email this file.")

    If myPassword <> "Password" Then
        MsgBox prompt:="Click OK to return to Report.", _
        Title:="Cancelled -- correct password not entered", _
        Buttons:=16

    Else
        Resp = MsgBox(prompt:="Click Yes to review email, No to immediately send, or Cancel.", _
        Title:="Email options: Want to review email before sending?", _
        Buttons:=3 + 32)
        If Resp = 2 Then
            MsgBox prompt:="Click OK to return to Report.", _
            Title:="EMAIL CANCELLED", _
            Buttons:=64
            Exit Sub
        End If

    End If

    Set otlNewMail = otlApp.CreateItem(olMailItem)
    fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        
    With otlNewMail
        .To = "JohnDoe@anywhere.com; JaneDoe@anyplace.com"
        .CC = "MarySmith@anywho.com"
        .Subject = "Email from me"
        .Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Ben" & Chr(13) & Chr(13)
        .Attachments.Add fName
        
    End With
    
    Select Case Resp
    Case 6      'Yes was clicked, user wants to review email first
        otlNewMail.Display
        
    Case 7      'If no is clicked
        otlNewMail.Send
 
    End Select
    
    Set otlNewMail = Nothing
    Set otlApp = Nothing
    Set otlAttach = Nothing
    Set otlMess = Nothing
    Set otlNSpace = Nothing


End Sub

To come back to your question, what methods have you considered to achieve this result?

Everybody body is somebodys Nutter.