Scan Excel column for specific word in VBA

Question

Currently im working on a database Excel spreadsheet and im currently using VBA to implement some automatic functions to the system. Im new to VBA so i need your help :)

My question is this: I have a statues column in which the user needs to select from a drop list 'Complete' or 'In progress'. I need a program which can scan a specific column (example S3) for the word 'Complete'. Once the word is detected, the system will automatically send an email to a specific user tell him that the task has been complete.

Can anyone help me?

Thanks! :)

Update: I have coded the following to search for the word complete and send an email to the user (this is a rough idea)

Sub For_Loop_With_Step()

    Dim lCount As Long, lNum As Long
    Dim MyCount As Long

    MyCount = Application.CountA(Range("S:S"))

    For lCount = 1 To MyCount - 1 Step 1
    If Cells(lCount + 2, 19) = "Complete" Then
    Call Send_Email_Using_VBA
    Else
    MsgBox "Nothing found"
    End If

    Next lCount



    MsgBox "The For loop made " & lNum & " loop(s). lNum is equal to " & lNum

End Sub

.

Sub Send_Email_Using_VBA()

    Dim Email_Subject, Email_Send_From, Email_Send_To, _
    Email_Cc, Email_Bcc, Email_Body As String
    Dim Mail_Object, Mail_Single As Variant
    Email_Subject = "Testing Results"
    Email_Send_From = "fromperson@example.com"
    Email_Send_To = "toperson@example.com"
    'Email_Cc = "someone@example.com"
    'Email_Bcc = "someoneelse@example.com"
    Email_Body = "Congratulations!!!! You have successfully sent an e-mail using VBA !!!!"
    On Error GoTo debugs
    Set Mail_Object = CreateObject("Outlook.Application")
    Set Mail_Single = Mail_Object.CreateItem(0)
    With Mail_Single
    .Subject = Email_Subject
    .To = Email_Send_To
    .cc = Email_Cc
    .BCC = Email_Bcc
    .Body = Email_Body
    .send
    End With
    debugs:
    If Err.Description <> "" Then MsgBox Err.Description
End Sub

enter image description here

Your question is very broad - can you be more specific and explain where you are stuck? — assylias, Jan 15 '13 at 11:03
Im stuck in the VBA program. I got no clue where to start from — Tristan Demanuele, Jan 15 '13 at 11:08
I need to scan a column from an excel spreadsheet for the word 'Complete', once the word is found an email will be sent. — Tristan Demanuele, Jan 15 '13 at 11:12
Does the column only need to contain the word 'Complete' once for the email to be sent, or should the macro check each row and send an email whenever the word complete appears for the first time per row? — MattCrum, Jan 15 '13 at 11:28
Yes the email needs to be sent each time the word complete is found — Tristan Demanuele, Jan 15 '13 at 11:34
@TristanDemanuele assuming you have a column of 10 rows and 5 rows (non consecutive) contains the word `complete`, 1. do you want to send 5 emails or do you want to send 1 email? 2. do you want to stop scanning the first time word is found in the column? — bonCodigo, Jan 15 '13 at 12:09
@bonCodigo 1. I want to send 5 emails 2. No i want the scanning to go through all the row and find all the 'complete' words until all the rows have been scanned. thanks — Tristan Demanuele, Jan 15 '13 at 12:48
If a 6th `complete` is found on a later iteration should 6 emails be sent or only 1? — Brad, Jan 15 '13 at 13:18
After a later iteration only the new found complete tasks need to be sent — Tristan Demanuele, Jan 15 '13 at 13:20
@TristanDemanuele `1` Don't loop, Use `.Find` and `2` Have one more column which will be updated whether an email has been sent or not. This is required for later iterations. `3` May I see a snapshot of your data so that I can test the code before posting? — Siddharth Rout, Jan 15 '13 at 15:33
@TristanDemanuele: Where are you picking up the "To" from? Or are the emails going to the same email address? Also what data are you picking up from other columns? — Siddharth Rout, Jan 15 '13 at 16:05
@SiddharthRout the "To" is going to the same person each time, and im just picking up data from strings. The basic automated email is ready and its working. I used the code above for that — Tristan Demanuele, Jan 15 '13 at 16:14

Siddharth Rout · Accepted Answer · 2013-01-16T09:21:49.220

0

Try this (Tried And Tested)

Screenshot:

enter image description here

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim i As Long, lRow As Long
    Dim ExitLoop As Boolean
    Dim aCell As Range, bCell As Range

    '~~> Set this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find the word in the relevant column. 19 is S Column
        Set aCell = .Columns(19).Find(What:="Complete", LookIn:=xlValues, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            '~~> Update Col T appropriately
            '~~> This is required so that mail doesn't go for the same row again
            '~~> When you run the macro again

            Set bCell = aCell

            If Not .Range("T" & aCell.Row).Value = "Mail Sent" Then
                If SendEmail = True Then
                    .Range("T" & aCell.Row).Value = "Mail Sent"
                Else
                    .Range("T" & aCell.Row).Value = "Error: Mail Not Sent"
                End If
            End If

            Do While ExitLoop = False
               Set aCell = .Columns(19).FindNext(After:=aCell)

               If Not aCell Is Nothing Then
                    If aCell.Address = bCell.Address Then Exit Do

                    If Not .Range("T" & aCell.Row).Value = "Mail Sent" Then
                        If SendEmail = True Then
                            .Range("T" & aCell.Row).Value = "Mail Sent"
                        Else
                            .Range("T" & aCell.Row).Value = "Error: Mail Not Sent"
                        End If
                    End If
               Else
                   ExitLoop = True
               End If
            Loop
        End If
    End With
End Sub

Function SendEmail() As Boolean
    Dim OutApp As Object, OutMail As Object

    On Error GoTo Whoa

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "toperson@example.com"
        .Subject = "Testing Results"
        .Body = "Your Message Goes Here"
        .Display
    End With

    DoEvents

    SendEmail = True

LetsContinue:
    On Error Resume Next
    Set OutMail = Nothing
    Set OutApp = Nothing
    On Error GoTo 0

    Exit Function
Whoa:
    SendEmail = False
    Resume LetsContinue
End Function

edited Jan 16 '13 at 09:21

answered Jan 15 '13 at 16:23

Siddharth Rout

147,039
17
206
250

Thanks alot for the code! very elegant solution. I have ran a dry run of the code and an error 91 is appearing in this line of code after the code was ran for the first time: If Not aCell Is Nothing Then If aCell.Address = bCell.Address Then Exit Do – Tristan Demanuele Jan 16 '13 at 08:02
Did you change anything in the code? May I see a sample file so that I can test the above code? If yes, then you can upload the file in www.wikisend.com and share the link here. – Siddharth Rout Jan 16 '13 at 08:38
No i did not change the above code. I uploaded the file on this link: http://sdrv.ms/VjAiVa – Tristan Demanuele Jan 16 '13 at 08:50
Thanks alot! one last question...is there a way i can run the macro in real time? Example if the excel file is already opened and the user changes the statues, can the macro instantly send the email once the change has been done? Thanks again :) – Tristan Demanuele Jan 16 '13 at 09:27
Yup. See this link on how to use `Worksheet_Change` event. http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640 – Siddharth Rout Jan 16 '13 at 09:30
And in which part of the code above do i need to implement it? Thanks – Tristan Demanuele Jan 16 '13 at 09:51
You need to call `Sample` Also see the comments by @DougGlancy in the comments in that post – Siddharth Rout Jan 16 '13 at 09:58
Sorry but im not grasping the concept yet :/ – Tristan Demanuele Jan 16 '13 at 10:03
You need to spend some time with it to understand it :) After that even if you are unable to get it, post the code that you tried in a new question referring to both posts and me or someone else will jump in to help you :) – Siddharth Rout Jan 16 '13 at 10:06
http://stackoverflow.com/questions/14359439/worksheet-change-concerning-scan-excel-column-for-specific-word-in-vba – Tristan Demanuele Jan 16 '13 at 13:22

Scan Excel column for specific word in VBA

1 Answers1