+ Reply to Thread
Results 1 to 3 of 3

Auto Send Email based on Cell Content

Hybrid View

  1. #1
    Registered User
    Join Date
    08-19-2012
    Location
    London, England
    MS-Off Ver
    Excel 2016 in Win 10
    Posts
    97

    Auto Send Email based on Cell Content

    I am trying to get an email to automatically be produced and sent if Cell in Column U contains the words "Send Reminder"

    I have attached the spreadsheet and the code I have been working on [from Ron Bruin site]for the email which I cannot get working

    Also not convinced it would send automatically when the cell changes to contain required phrase

    I'd be really grateful for some help with explaining what I have done wrong


    Sub email()
    Dim lRow As Integer
    Dim i As Integer
    Dim toDate As Date
    Dim toList As String
    Dim eSubject As String
    Dim eBody As String
     
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    Sheets("Sheet1").Select
    lRow = Cells(Rows.Count, 12).End(xlUp).row '12 = email address from Column L
    For i = 2 To lRow
      If Left(Cells(i, 21), 12) = "Send Reminder" Then '21 = From Column U, 12 = email address from Column L
         Set OutApp = CreateObject("Outlook.Application")
         Set OutMail = OutApp.CreateItem(0)
            toList = Cells(i, 12)    'gets the recipient email address from Column L
            eSubject = "Mower Service is due on" & Cells(i, 16) ' Next Service Date from Column P
            eBody = "Dear " & Cells(i, 3) & " " & Cells(i, 5) & vbCrLf & vbCrLf & "Your " & Cells(i, 17) & "is due for its next service." & vbCrLf & vbCrLf & "Please call us on XXXXXXXXXXX or email us to arrange for your machines next service." & vbCrLf & vbCrLf & "The Cost of the service will be " & Cells(i, 19)
           '3 = Title from Column C, 5 = Surname from Column E, 17 = Mower Type from Column Q, 19 = Cost from Column S
            On Error Resume Next
            With OutMail
            .To = toList
            .CC = ""
            .BCC = ""
            .Subject = eSubject
            .Body = eBody
            .BodyFormat = 1
            .Display   ' ********* Creates draft emails, to be removed when final testing complete
            '.Send     '********** To be set when final testing complete
            End With
         On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    Cells(i, 21) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column U"
    End If
    Next i
    ActiveWorkbook.Save
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
    End Sub
    Spreadsheet is here: Contacts List.xlsm

    Thank you so much for helping

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Auto Send Email based on Cell Content

    Hello tigerdel,


    See this link: http://www.excelforum.com/excel-prog...excel-vba.html

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Auto Send Email based on Cell Content

    There are 13 character in the string "Send Reminder", that may be why the e-mail is not kicking in. Your code is asking 12 `Left(Cells(i, 21), 12)`, change the 12 to 13

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 23
    Last Post: 08-04-2015, 03:47 PM
  2. Replies: 2
    Last Post: 12-19-2014, 11:28 AM
  3. Workflow with signatures (if cell=x then send email, if signed then send email)
    By Kate2811 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-25-2014, 05:37 AM
  4. Macro to send email with signature depending on cell content
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-12-2013, 11:39 AM
  5. auto send email with cell background colour
    By vipulhumein in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-09-2013, 03:25 PM
  6. Auto send email from hyperlink in cell e12
    By Robbosan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2013, 10:49 AM
  7. Auto send email with the cell value that triggered the email in the first place.
    By coobey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-02-2013, 05:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1