email status

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
454
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,208
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
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,208
NOTE: The above post #2 macro will not function as desired. Still working on this.
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
454
Office Version
  1. 365
Platform
  1. Windows
thanks
it didnt work
i will wait for another solution
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,208

ADVERTISEMENT

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
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
454
Office Version
  1. 365
Platform
  1. Windows
hi
i used your premise and made some changes and it works as expected
thanks for steering me in the right direction
 

Forum statistics

Threads
1,144,164
Messages
5,722,856
Members
422,461
Latest member
kelleys315

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
Top