Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Send Email when cell has text in it

    VBA V21.xlsxHI
    I am new to this forums and wondered if any body could help in my problem, I have been trying to set up a automatic email using vba on a excel sheet when a trailer is coming up for service, the email needs to be triggered by the word "Service Due" but the body of the text needs to include the trailer ID Number.
    is this possible? I have tried all sorts of code, then wiped it and start again, could someone please help me with this.
    I have included the work sheet.
    many thanks
    Warden

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Warden,

    I made some modifications to your sheet by adding a few extra columns and adding data validation to several columns. The code behind the workbook employs a class that I wrote for generating email and task reminders. Here's how it works.

    Clicking the "Send Emails" button initiates the code. A search will be performed down column D for "Service Due". When found, if there is a trailer VIN# and no comment in the cell in column G of the same row, an email will be generated for that record. The email will be sent to the respective manager using the manager's email address. The subject line indicates that service is due and the VIN# of the trailer. The body of the email addresses the manager and provides additional information. (see image below)

    Warden2.png

    A comment will be placed in the email address cells (col F) time stamped with the date and time that the email was sent. The comment serves as a quick reference as to when the manager was notified and also as an inhibitor to prevent a duplicate email from being sent. If you wish a repeat email sent then delete the comment for that line prior to running the code.

    warden1.png

    There are no limits to the number of trailers you can add; the code will adjust. The name of the workbook and sheet is insignificant to the code however, the order of the columns are important. Let me know if you need some adjustments made.

    In a standard module:
    Code:
    Public Sub EmailManagers()
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim Email As ClsTaskEmail
    Dim LastRow As Long, I As Long
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    '--------------------------------
    'LOOP THROUGH TRAILERS
    For I = 3 To LastRow
        If Cells(I, "B") <> "" And Cells(I, "D") = "Service Due" And _
           Cells(I, "F").Comment Is Nothing Then
            Set Email = New ClsTaskEmail
    '--------------------------------
    'SETUP EMAIL
            If Email.OutlookCheck = False Then Exit Sub
            Email.EmailTo = Cells(I, "F")
            Email.EmailCopyTo = ""
            Email.EmailBlindCopyTo = ""
            Email.EmailSubject = "Service Due VIN#: " & Cells(I, 2)
            Email.EmailBody = Cells(I, "E") & "," & vbNewLine & vbNewLine & _
                              "Service is due for the following trailer:" & vbNewLine & _
                              "VIN#: " & Cells(I, 2) & vbNewLine & _
                              "Trailer ID: " & Cells(I, 3)
            Email.EmailReadReceipt = False
            Email.EmailFollowUpDueDate = Date + Cells(I, "G")
            Email.EmailAttachmentFile = ""
            Email.EmailCellComment = Cells(I, "F")
            Email.EmailCommentInhibit = True
            Email.EmailCreate
        End If
        Set Email = Nothing
    Next I
    End Sub
    The class must also be imported into the project. The spreadsheet I have provided is already turnkey if you choose to use it.

    HTH,
    Maud
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Dec 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maudibe

    Many thanks for that, I will try it when I am back at work in the New Year and let you know.
    Happy New Year to you.
    Regards
    Warden

  4. #4
    New Lounger
    Join Date
    Dec 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    HI Maudibe

    it work perfectly thank you very much, just one question on the follow up email how dose that work, because when you set the days it dose not repeat email

    thank you
    Warden

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    You are welcome Warden. You can read about follow-ups here:

    https://support.office.com/en-us/art...7-9C57E1F781CC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •