+ Reply to Thread
Results 1 to 2 of 2

Macro sending email to outlook when due date approaches

  1. #1
    Registered User
    Join Date
    12-30-2016
    Location
    Toronto
    MS-Off Ver
    10
    Posts
    1

    Question Macro sending email to outlook when due date approaches

    Hello there. I have written a piece of code where excel sheet automatically sends email when a due date approaches. I am basic user of VBA and now I am facing a problem as below.

    If I have 10 rows with 10 items and each row has a due date, then email triggers whenever due date reaches in each row.
    Now in any row, in respective due date column if there is no due date entered, then the code breaks showing run time error 13.

    Can someone help me how to solve this. Code should run for rows which has due dates just ignoring where there is no due date. For reference I am attaching the entire code.

    Thanks in advance

    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(1).Select
    lRow = Cells(Rows.Count, 4).End(xlUp).Row

    For i = 2 To lRow
    toDate = Replace(Cells(i, 3), ".", "/")
    If Left(Cells(i, 5), 4) <> "Mail" And toDate - Date <= 7 Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    toList = Cells(i, 4)
    eSubject = "SAE - " & Cells(i, 2) & "- Inquiry Follow UP Required - Due on " & Cells(i, 3)
    eBody = "Dear PV Team" & vbCrLf & vbCrLf & "This is a remainder email for follow up required on SAE Inquiry raised on " & Cells(i, 6) & " for SAE -" & Cells(i, 2) & vbCrLf & vbCrLf & "Please update the tracker as required." & vbCrLf & vbCrLf & "Thank you"

    On Error Resume Next
    With OutMail
    .To = toList
    .CC = "[email protected]"
    .BCC = ""
    .Subject = eSubject
    .Body = eBody
    .bodyformat = 1
    .Display
    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
    Next i

    ActiveWorkbook.Save

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    End With
    End Sub

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro sending email to outlook when due date approaches

    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

+ 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. Change VBA to allow email code to bring up outlook email before sending it
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2015, 02:33 AM
  2. [SOLVED] Sending PDF with Outlook email
    By zplugger in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-22-2015, 10:40 AM
  3. macro to automatically send email when due date approaches in XL spreadsheet.
    By PTSO in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-04-2014, 06:26 PM
  4. [SOLVED] Getting a outlook warning (2010) while sending an email through excel 2010 macro
    By Kiran Kurapati in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-26-2014, 12:21 PM
  5. Sending Email via Outlook
    By jamsta1972 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-26-2010, 12:46 PM
  6. Sending an email with Outlook Express not Outlook
    By Steven811 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-05-2010, 04:23 AM
  7. Sending Outlook email pop up box
    By jamphan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2007, 03:56 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