1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Yes for Email No for no Email Condition in an if Statement of macro

Discussion in 'Business Applications' started by toofani, May 11, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    Dear Guyz

    I have a macro helped and developed by a respected admin of this group

    it has following statement

    If Cells(lRow, "N").Value >= 5 And Len(Trim(Cells(lRow, "P").Value)) > 0 Then ' check if the months passed are >= 5 (same as the red conditional formatting)
    If Len(Trim(Cells(lRow, "O").Value)) = 0 Or (IsDate(Cells(lRow, "O").Value) = True And Month(Cells(lRow, "O").Value) <> Month(Date)) Then
    toList = Cells(lRow, "P") 'gets the recipient's email address Column O

    I want to add another line from another macro

    If cell.Value Like "?*@?*.?*" And _
    LCase(Cells(cell.Row, "C").Value) = "yes" Then

    which makes sure that only @ emails are there and also ensures to which person it has to email and which it has avoid.

    Can anybody help me to sort out this especially @Keebellah
     
  2. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    guys?
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Can you post your full code?
     
  4. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    ' Last Update : 23-10-2014 by Hans Hallebeek (HC&TS)

    Public Sub CheckAndSendMail()
    Dim lRow As Long
    Dim lstRow As Long
    Dim toDate As Date
    Dim toList As String
    Dim ccList As String
    Dim bccList As String
    Dim eSubject As String
    Dim EBody As String
    Dim ws As Worksheet
    Dim cell As Range

    On Error Resume Next
    Set ws = Sheets("Prog")
    If ws Is Nothing Then
    MsgBox "Expected worksheet 'Prog' not found!" & vbNewLine & vbNewLine & _
    "Please check if the sheet exists or has been renamed!" & vbNewLine & vbNewLine & _
    "Procedure aborted!", vbExclamation, "INVALID WORKSHEET"
    GoTo endMacro
    End If
    Err.Clear
    On Error GoTo 0
    ws.Activate

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    End With


    lstRow = WorksheetFunction.Max(3, ws.Cells(Rows.Count, "M").End(xlUp).Row)

    For lRow = 3 To lstRow
    ' IF ...... Then 'an extra check if the student has finished everything
    ccList = vbNullString ' initialize the cclist
    If Cells(lRow, "N").Value >= 5 And Len(Trim(Cells(lRow, "P").Value)) > 0 Then ' check if the months passed are >= 5 (same as the red conditional formatting)
    If Len(Trim(Cells(lRow, "O").Value)) = 0 Or (IsDate(Cells(lRow, "O").Value) = True And Month(Cells(lRow, "O").Value) <> Month(Date)) Then
    toList = Cells(lRow, "P") 'gets the recipient's email address Column O
    For Each cell In ws.Range(Cells(lRow, "Q"), Cells(lRow, "R")) ' fills the CClist with the email addresse, if present
    If Len(Trim(cell.Value)) > 0 Then
    If Len(Trim(ccList)) > 0 Then ccList = ccList & ";"
    ccList = ccList & cell.Value
    End If
    Next cell
    If Len(Trim(Cells(lRow, "U").Value)) > 0 Then ' this is column T for the CClist
    If Len(Trim(ccList)) > 0 Then ccList = ccList & ";"
    ccList = ccList & Cells(lRow, "U").Value
    End If

    eSubject = "GEC Meeting to Review Research Progress "
    EBody = "Respected GEC Members" & "," & vbCrLf & vbCrLf & Cells(lRow, "U") & " of PhD scholar Mr. " & Cells(lRow, "B") & " is due" & vbCrLf & vbCrLf & "Kindly convene GEC meeting to review the research progress. " & vbCrLf & vbCrLf & "Please let us know the GEC meeting date to enable us for necessary planning and coordination." & vbCrLf & vbCrLf & "With Profound Regards"

    MailData msgSubject:=eSubject, msgBody:=EBody, Sendto:=toList, CCto:=ccList

    Cells(lRow, "S") = "Mail Sent on " & Date + Time 'Marks the row as "email sent in Column S
    Cells(lRow, "O").Value = Date
    End If
    End If
    ' End If
    Next lRow

    endMacro:
    ActiveWorkbook.Save
    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    End With
    Err.Clear
    On Error GoTo 0
    End Sub

    Function MailData(msgSubject As String, msgBody As String, Sendto As String, _
    Optional CCto As String, Optional BCCto As String, Optional fAttach As String)

    Dim app As Object, Itm As Variant
    Set app = CreateObject("Outlook.Application")
    Set Itm = app.CreateItem(0)
    With Itm
    .Subject = msgSubject
    .To = Sendto
    If Len(Trim(CCto)) > 0 Then .Cc = CCto
    If Len(Trim(BCCto)) > 0 Then .Bcc = BCCto
    .Body = msgBody & Chr(13) & Chr(13)
    .BodyFormat = 1 '1=Plain text, 2=HTML 3=RichText -- ISSUE: this does not keep HTML formatting -- converts all text
    On Error Resume Next
    If Len(Trim(fAttach)) > 0 Then .Attachments.Add (fAttach) ' Must be complete path and filename of an attachment
    Err.Clear
    On Error GoTo 0
    .Save ' This property is used when you want to saves mail to the Concept folder
    .Display ' This property is used when you want to display before sending
    '.Send ' This property is used if you want to send without verification
    End With
    Set app = Nothing
    Set Itm = Nothing
    End Function

    Public Sub AutoCheckAndSend()
    CheckAndSendMail
    ActiveWorkbook.Save
    If countOpenWorkbooks > 0 Then
    ActiveWorkbook.Close False
    Exit Sub
    End If
    Application.Quit
    End Sub

    Public Function countOpenWorkbooks() As Integer
    Dim wb As Workbooks
    Dim x As Integer
    Dim wbC As Integer
    wbC = Workbooks.Count
    x = 0
    For x = 1 To Workbooks.Count
    If Workbooks(x).Name = ActiveWorkbook.Name Then
    wbC = wbC - 1
    ElseIf InStr(1, LCase(Workbooks(x).Name), "personal") > 0 Then
    wbC = wbC - 1
    End If
    Next x
    countOpenWorkbooks = wbC
    End Function
     
  5. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    I want to add condition of yes and no for sending emails in another column that is V
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I suggest you attach an Excel file (macro enabled) with some dummy data, will make it all easier:)
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1148044

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice