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?
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?
VBA Code:
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