many emails and many call macros

[rjmdc]

hi
i messed up
my code worked when it was only email1 and email2 i guess i didnt add 3 and 4 correctly. was i maent to have a sub that lists all my macros as call email1 call email2
can someone sort it out for me?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count = 1 Then
        If Target.Column = 9 Then
            If Cells(Target.Row, "I").Value <> "" Then
            result = MsgBox("pressing OK will send email to notify", vbOKCancel + vbInformation, "Budget Approved")
           
            If result = vbCancel Then SaveUI = True
                If result = vbOK Then
                    Set OutlookApp = CreateObject("Outlook.Application")
                    Set OlObjects = OutlookApp.GetNamespace("MAPI")
                    Set newmsg = OutlookApp.CreateItem(olMailItem)
                   
                    Call SendEmail1(Target, "mail@mail") 'Email 1
                    Call SendEmail2(Target, "mail@mail") 'Email 2
                    MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
                End If
            End If
        End If
    End If
End Sub

Private Sub SendEmail1(ByVal Target As Range, sRecipient As String)
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OlObjects = OutlookApp.GetNamespace("MAPI")
    Set newmsg = OutlookApp.CreateItem(olMailItem)
   
    With newmsg
        .Recipients.Add (sRecipient)
        .Subject = Cells(Target.Row, "A").Value & " budget was approved" ' Add Subject
        .Body = "Now that budget was approved for " & Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "I").Value & vbCrLf & "" & _
                "Please prepare Budget Description " ' Email Body
        .Display 'Display Email
        .Send 'Send Email
               
    End With
End Sub

Private Sub SendEmail2(ByVal Target As Range, sRecipient As String)
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OlObjects = OutlookApp.GetNamespace("MAPI")
    Set newmsg = OutlookApp.CreateItem(olMailItem)
   
    With newmsg
        .Recipients.Add (sRecipient)
        .Subject = Cells(Target.Row, "A").Value & " budget was approved" ' Add Subject
        .Body = "Now that budget was approved for " & Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "I").Value & vbCrLf & "" & _
                "Please train broker for proper reimbursement billing" ' Email Body
        .Display 'Display Email
        .Send 'Send Email
               
    End With
End Sub
Private Sub AmendedBudget(ByVal Target As Range)

    If Target.Cells.Count = 1 Then
        If Target.Column = 11 Or Target.Column = 12 Then
            If Cells(Target.Row, "K").Value <> "" Or Cells(Target.Row, "L").Value <> "" Then
            result = MsgBox("pressing OK will send email to notify", vbOKCancel + vbInformation, "Amended Budget Approved")
           
            If result = vbCancel Then SaveUI = True
                If result = vbOK Then
                    Set OutlookApp = CreateObject("Outlook.Application")
                    Set OlObjects = OutlookApp.GetNamespace("MAPI")
                    Set newmsg = OutlookApp.CreateItem(olMailItem)
                   
                    Call SendEmail3(Target, "mail@mail") 'Email 3
                    Call SendEmail4(Target, "mail@mail") 'Email 4
                    MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
                End If
            End If
        End If
    End If
End Sub
Private Sub SendEmail3(ByVal Target As Range, sRecipient As String)
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OlObjects = OutlookApp.GetNamespace("MAPI")
    Set newmsg = OutlookApp.CreateItem(olMailItem)
   
    With newmsg
        .Recipients.Add (sRecipient)
        .Subject = Cells(Target.Row, "A").Value & " Amended budget was approved" ' Add Subject
        .Body = "Now that there is an amended budget approval for " & Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "K").Value & vbCrLf & "" & _
                "Please prepare an updated Budget Description " ' Email Body
        .Display 'Display Email
        .Send 'Send Email
               
    End With
End Sub

Private Sub SendEmail4(ByVal Target As Range, sRecipient As String)
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OlObjects = OutlookApp.GetNamespace("MAPI")
    Set newmsg = OutlookApp.CreateItem(olMailItem)
   
    With newmsg
        .Recipients.Add (sRecipient)
        .Subject = Cells(Target.Row, "A").Value & " budget amendment was approved" ' Add Subject
        .Body = "This is a notification that an amended budget was approved for " & Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "K").Value & vbCrLf & "" & _
                "Please update the records according to the information on the SD grid" ' Email Body
        .Display 'Display Email
        .Send 'Send Email
               
    End With
End Sub

 

[rjmdc]

in reply
previous email was for column 12
i am adding another clause
now for column 16
when column 16 gets a check mark i want to send a differnet email

then it will be 7 different emails off of 1 sheet depending on the column

 

[DanteAmor]

If Target.Cells.Count = 1 Then
If Target.Column = 16 And AscW(Target.Value & " ") = 10004 Then
result = MsgBox("pressing OK will send email to notify", vbOK + vbExclamation, "can send Startup Lp draft")

The above is the code you put in post #12

But you don't answer my doubts.
My macro use "Target.CountLarge" and you use "Target.Cells.Count", the question is why do you use "Target.Cells.Count"

My macro uses "ElseIf Target.Column" and you only use "IF Target.Column", the question is why do you only use "IF".

You use AscW(Target.Value & " ") = 10004 , Why do you use that, what does Target.Value & " " mean and what does "10004" mean, where did you get that data from?

 

[rjmdc]

just because i am a vba newbie and that worked in previous single emails on a page
AscW(Target.Value & " ") = 10004 means ✔
it doesnt matter how you help me

your 6 emails was perfect.
i was trying to add a "bandaid" to add a 7th email i was not successful
whichever way if column p is not empty activate i dobnt care

 

[rjmdc]

did i make any sense?
if not let me start again
how do i add an email if a change is made in column P

 

[DanteAmor]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sMail As String, sSubj As String, sBody As String

  If Target.CountLarge > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Not Intersect(Target, Range("I:I, K:L, P:P")) Is Nothing Then
    If Target.Column = 9 Then
      If MsgBox("Pressing OK will send email to notify", vbOKCancel + vbInformation, "Budget Approved") = vbOK Then
        
        'Data for MAIL 1
        sMail = "mail_1@mail"
        sSubj = Cells(Target.Row, "A").Value & " budget was approved"
        sBody = "Now that budget was approved for " & Cells(Target.Row, "A").Value & " on " & _
                Cells(Target.Row, "I").Value & vbCrLf & "" & "Please prepare Budget Description "
        Call SendMail(sMail, sSubj, sBody)
        
        'Data for MAIL 2
        sMail = "mail_2@mail"
        sSubj = Cells(Target.Row, "A").Value & " budget was approved"
        sBody = "Now that budget was approved for " & Cells(Target.Row, "A").Value & " on " & _
                Cells(Target.Row, "I").Value & vbCrLf & "" & "Please train broker for proper reimbursement billing"
        Call SendMail(sMail, sSubj, sBody)
      
        MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
      End If
    ElseIf Target.Column = 11 Then 'column "K"
      If MsgBox("pressing OK will send email to notify", vbOKCancel + vbInformation, "Amended Budget Approved") = vbOK Then
        
        'Data for MAIL 3
        sMail = "mail_3@mail"
        sSubj = Cells(Target.Row, "A").Value & " Amended budget was approved"
        sBody = "Now that there is an amended budget approval for " & _
                Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "K").Value & vbCrLf & "" & _
                "Please prepare an updated Budget Description "
        Call SendMail(sMail, sSubj, sBody)
      
        'Data for MAIL 4
        sMail = "mail_4@mail"
        sSubj = Cells(Target.Row, "A").Value & " budget amendment was approved"
        sBody = "This is a notification that an amended budget was approved for " & _
                Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "K").Value & vbCrLf & "" & _
                "Please update the records according to the information on the SD grid"
        Call SendMail(sMail, sSubj, sBody)

        MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
      End If
    ElseIf Target.Column = 12 Then  'column "L"
      If MsgBox("pressing OK will send email to notify", vbOKCancel + vbInformation, "Amended Budget Approved") = vbOK Then
        
        'Data for MAIL 5
        sMail = "mail_5@mail"
        sSubj = Cells(Target.Row, "A").Value & " Amended budget was approved"
        sBody = "Now that there is an amended budget approval for " & _
                Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "L").Value & vbCrLf & "" & _
                "Please prepare an updated Budget Description "
        Call SendMail(sMail, sSubj, sBody)
      
        'Data for MAIL 6
        sMail = "mail_6@mail"
        sSubj = Cells(Target.Row, "A").Value & " budget amendment was approved"
        sBody = "This is a notification that an amended budget was approved for " & _
                Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "L").Value & vbCrLf & "" & _
                "Please update the records according to the information on the SD grid"
        Call SendMail(sMail, sSubj, sBody)

        MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
      End If
    ElseIf Target.Column = 16 Then  'column "P"
      If MsgBox("pressing OK will send email to notify", vbOKCancel + vbInformation, "Amended Budget Approved") = vbOK Then
        
        'Data for MAIL 7
        sMail = "mail_7@mail"
        sSubj = Cells(Target.Row, "A").Value & " hass DPP services. "
        sBody = "Since the following participant has DPP Services " & vbCrLf & "" & _
                "Please update SD Participants sheet accordingly. "
        Call SendMail(sMail, sSubj, sBody)
      
        MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
        
      End If
    End If
  End If
End Sub

Sub SendMail(sMail, sSubj, sBody)
  Dim OutlookApp As Object
  Set OutlookApp = CreateObject("Outlook.Application").CreateItem(0)
  With OutlookApp
    .To sMail
    .Subject = sSubj
    .Body = sBody
    .Display 'Display Email
    .Send 'Send Email
  End With
End Sub

 

[rjmdc]

sorry
now none of the others work either

 

[DanteAmor]

now none of the others work either

Did you copy the entire macro?
Modify something in the macro?
The macro works for me for columns I, K, L and P

 

[rjmdc]

hi
it now reads: the others work except for column P

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sMail As String, sSubj As String, sBody As String

  If Target.CountLarge > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Not Intersect(Target, Range("I:I, K:L, P:P")) Is Nothing Then
    
    If Target.Column = 9 Then
      If MsgBox("Pressing OK will send email to notify", vbOKCancel + vbInformation, "Budget Approved") = vbOK Then
        
        'Data for MAIL 1
        sMail = "mail@mail.org"
        sSubj = Cells(Target.Row, "A").Value & " budget was approved"
        sBody = "Now that budget was approved for " & Cells(Target.Row, "A").Value & " on " & _
                Cells(Target.Row, "I").Value & vbCrLf & "" & "Please prepare Budget Description "
        Call SendMail(sMail, sSubj, sBody)
        
        'Data for MAIL 2
        sMail =  "mail@mail.org"
        sSubj = Cells(Target.Row, "A").Value & " budget was approved"
        sBody = "Now that budget was approved for " & Cells(Target.Row, "A").Value & " on " & _
                Cells(Target.Row, "I").Value & vbCrLf & "" & "Please train broker for proper reimbursement billing"
        Call SendMail(sMail, sSubj, sBody)
      
        MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
      End If
    ElseIf Target.Column = 11 Then ' Column "K"
      If MsgBox("pressing OK will send email to notify", vbOKCancel + vbInformation, "Amended Budget Approved") = vbOK Then
        
        'Data for MAIL 3
        sMail =  "mail@mail.org"
        sSubj = Cells(Target.Row, "A").Value & " Budget amendment was approved"
        sBody = "Now that there is an amended budget approval for " & Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "K").Value & vbCrLf & "" & _
                "Please prepare an updated Budget Description "
        Call SendMail(sMail, sSubj, sBody)
      
        'Data for MAIL 4
        sMail =  "mail@mail.org"
        sSubj = Cells(Target.Row, "A").Value & " Budget Amendment was approved"
        sBody = "This is a notification that an amended budget was approved for " & Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "K").Value & vbCrLf & "" & _
                "Please update the records accordingly"
        Call SendMail(sMail, sSubj, sBody)

        MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
      End If
    ElseIf Target.Column = 12 Then ' Column "L"
      If MsgBox("pressing OK will send email to notify", vbOKCancel + vbInformation, "Amended Budget Approved") = vbOK Then
        
        'Data for MAIL 5
        sMail = " "mail@mail.org"
        sSubj = Cells(Target.Row, "A").Value & " Amended budget was approved"
        sBody = "Now that there is an Amended Budget approval for " & Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "L").Value & vbCrLf & "" & _
                "Please prepare an updated Budget Description "
        Call SendMail(sMail, sSubj, sBody)
      
        'Data for MAIL 6
        sMail =  "mail@mail.org"
        sSubj = Cells(Target.Row, "A").Value & " Amended Budget was approved"
        sBody = "This is a notification that an Amended Budget was approved for " & Cells(Target.Row, "A").Value & " on " & Cells(Target.Row, "L").Value & vbCrLf & "" & _
                "Please update the records accordingly"
        Call SendMail(sMail, sSubj, sBody)

        MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
        
        ElseIf Target.Column = 16 Then  'column "P"
      If MsgBox("pressing OK will send email to notify", vbOKCancel + vbInformation, "DPP Services") = vbOK Then
        
        'Data for MAIL 7
        sMail = "mail@mail.org"
        sSubj = Cells(Target.Row, "A").Value & " has DPP services. "
        sBody = "Since the following participant has DPP Services " & vbCrLf & "" & _
                "Please update SD Participants sheet accordingly. "
        Call SendMail(sMail, sSubj, sBody)
      
        MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
        
      End If
    End If
  End If
  End If
End Sub

Sub SendMail(sMail, sSubj, sBody)
  Dim OutlookApp As Object
  Set OutlookApp = CreateObject("Outlook.Application").CreateItem(0)
  With OutlookApp
    .to = sMail
    .Subject = sSubj
    .Body = sBody
    .Display 'Display Email
    .Send 'Send Email
  End With
End Sub

 

[rjmdc]

please see what i did wrong

 

[DanteAmor]

You're not copying the full macro
just copy the entire macro.