email status

rjmdc

Well-known Member
Joined
Apr 29, 2020
Messages
672
Office Version
  1. 365
Platform
  1. Windows
please help me fix my code.i would like to change the requirements
a- if column 8 is target and not blank (no longer has to check if ists a date, it will be a date) then sterminations = target
b= if taget is column 7 and not blank then sterminations = target

i have column 8 ok for now as the target is a date anywayu
when i try to add the clause for column 7 it breaks

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sMail As String, sSubj As String, sBody As String
    Dim sTermination As String
    Dim emailstring As String
    
    If Target.Cells.Count = 1 Then
        If Target.Column = 8 And Target.Value <> "" Then
            sTermination = Target.Value
        
            If IsDate(sTermination) Then
                sMail = "mail@mail.org"
                emailstring = " current status is/or has been terminated on: "
            
           ElseIf Target.Column = 7 And Target.Value <> "" Then
            sTermination = Target.Value
                sMail = "mail@mail.org"
                emailstring = " current status is: "
            
            End If
            
            result = MsgBox("pressing OK will send email to notify", vbOK + vbExclamation, "Termination Status")
            If result = vbOK Then
                sSubj = Cells(Target.Row, "A").Value & " current status/termination"
                sBody = Cells(Target.Row, "A").Value & emailstring & " " & Cells(Target.Row, "F").Value  ' Email Body
                Call SendMail(sMail, sSubj, sBody)
            End If
            
            MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
        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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
See if this runs how you wanted :

VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sMail As String, sSubj As String, sBody As String, result As String
    Dim sTermination As String
    Dim emailstring As String
    
    If Target.Cells.Count = 1 Then
        
        If Target.Column = 8 And Target.Value <> "" Then
            sTermination = Target.Value
        
            'If IsDate(sTermination) Then
                sMail = "mail@mail.org"
                emailstring = " current status is/or has been terminated on: "
            
        ElseIf Target.Column = 7 And Target.Value <> "" Then
            sTermination = Target.Value
                sMail = "mail@mail.org"
                emailstring = " current status is: "
            
        End If
            
            result = MsgBox("pressing OK will send email to notify", vbOK + vbExclamation, "Termination Status")
            If result = vbOK Then
                sSubj = Cells(Target.Row, "A").Value & " current status/termination"
                sBody = Cells(Target.Row, "A").Value & emailstring & " " & Cells(Target.Row, "F").Value  ' Email Body
                Call SendMail(sMail, sSubj, sBody)
            End If
            
            MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
    End If
    
  
End Sub
 
Upvote 0
NOTE: The above post #2 macro will not function as desired. Still working on this.
 
Upvote 0
thanks
it didnt work
i will wait for another solution
 
Upvote 0
Try this :

VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sMail As String, sSubj As String, sBody As String, result As String
    Dim sTermination As String
    Dim emailstring As String
    
    If Target.Cells.Count = 1 Then
    
    If Target.Column < 7 Or Target.Column > 8 Then Exit Sub

        If Target.Column = 7 And Target.Value <> "" Then
            sTermination = Target.Value
        
            If IsDate(sTermination) Then
                sMail = "mail@mail.org"
                emailstring = " current status is/or has been terminated on: "
            End If
        End If
        If Target.Column = 8 And Target.Value <> "" Then
            sTermination = Target.Value
                sMail = "mail@mail.org"
                emailstring = " current status is: "
            
        End If
    End If
            result = MsgBox("pressing OK will send email to notify", vbOK + vbExclamation, "Termination Status")
            If result = vbOK Then
                sSubj = Cells(Target.Row, "A").Value & " current status/termination"
                sBody = Cells(Target.Row, "A").Value & emailstring & " " & Cells(Target.Row, "F").Value  ' Email Body
                Call SendMail(sMail, sSubj, sBody)
            End If
            
            MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
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
 
Upvote 0
hi
i used your premise and made some changes and it works as expected
thanks for steering me in the right direction
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top