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.

Help with excel coding for sending email on specific dates

Discussion in 'Business Applications' started by NickS7178, Jul 4, 2017.

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

    NickS7178 Thread Starter

    Joined:
    Jun 26, 2017
    Messages:
    3
    Greetings all,


    I am a novice at best on using excel, but through some searching and experimentation I have managed to get to a roadblock I cannot pass. I am working on setting up a workbook that emails at specific dates for reminders to people in my department. I believed I had it working however I have run into two issues.


    One is a run type error 13 that highlights toDate = Replace(Cells(i, 3), ".", "/") when I run the debug.

    The second is that no matter what I try to do I continue to receive error messages. Can I ask that one of the very talented excel knowledgeable people here look to find where I am making my errors?


    Thank you much in advance with any assistance you can provide which will stop me from pulling out my hair.
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    The ToDate function is failing because your code is looping through row by row and eventually hitting the blank "separator" rows which do not contain any values. Does each group of records separated by the blank rows represent different projects? If so you need to add logic to ignore the header rows and blank separator rows. I'm also confused on why exactly you are using the "replace" and "ToDate" functions. Will the values in column "C" not be in a date format to begin with? Below is an example of how to handle the blank rows and header rows (note the "IF" statement I added to your loop)

    Code:
    For i = 2 To lRow
    
    If Cells(i, 3) <> "Due Date" And Not IsEmpty(Cells(i, 3)) Then
    
    toDate = Replace(Cells(i, 3), ".", "/")
      If Left(Cells(i, 5), 4) <> "Mail" And toDate - Date <= 1 Then
         Set OutApp = CreateObject("Outlook.Application")
         Set OutMail = OutApp.CreateItem(0)
    
            toList = Cells(i, 4)    'gets the recipient from col D
            eSubject = "Loan " & Cells(i, 2) & " Touchpoint is due on " & Cells(i, 3)
            eBody = "Dear " & Cells(i, 1) & vbCrLf & vbCrLf & "Please update your loan and add touchpoint date to PCL."
           
            On Error Resume Next
            With OutMail
            .To = toList
            .CC = ""
            .BCC = ""
            .Subject = eSubject
            .Body = eBody
            .bodyformat = 1
            .Display   ' ********* Creates draft emails. Comment this out when you are ready
            '.Send     '********** UN-comment this when you  are ready to go live
            End With
    
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
     Cells(i, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
    End If
    End If
    Next i
    
    Rollin
     
    Last edited: Jul 8, 2017
    NickS7178 likes this.
  3. NickS7178

    NickS7178 Thread Starter

    Joined:
    Jun 26, 2017
    Messages:
    3
    Thank you so much for the assistance Rollin.

    The goal is to have the red boxes filled in by the supervisor with the following; person's name, then the project name and number, the due date, then the email to send the reminder to, then finally when the email was sent as reminder. The user would input the information inside the red outlined box and then the email would send 24 hours, 72 hours, 7 days, and 14 days from the date at the top. The goal is to have the email send the afternoon before the date so that the employee can work on it the day of.

    I am using the macro found here : https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/

    I searched and tried before asking for help, but I just can't get it to work properly. It is still giving me the error when I run the code you have above. The replace and todate I do not believe need to be there. Dates will be input in the proper format. I just need the macro to recognize the date it is when run so it knows when to send.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    After the error is thrown hit the "debug" button and copy and paste the highlighted yellow line that is giving the error.


    Rollin
     
  5. NickS7178

    NickS7178 Thread Starter

    Joined:
    Jun 26, 2017
    Messages:
    3
    toDate = Replace(Cells(i, 3), ".", "/")

    This is what is highlighted in the debug
     
  6. 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/1192502

  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