Results 1 to 7 of 7

Modity on current code to prompt due date & auto email

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    MS365
    Posts
    287

    Modity on current code to prompt due date & auto email

    Dear All,

    I have below code from one of the pro in this forum.
    I would like to modify this code into others worksheet.
    Could some one explain to me

    1.
    For i = 3 To Sheets("EXPORT").Range("e65536").End(xlUp).row
    What is the "i" stand for?
    Range("e65536") means what?

    2.
    If Cells(i, 28).Value <= Date + 0 And Cells(i, 29) = "" Then
    Date + 0 will prompt you the due date. What about if i wan macro to prompt me 3 days after due date. How should I amend?




    Option Explicit
    'Sub Auto_Open()

    Sub CheckForExpiryDates()
    Dim ce As Range, i As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    For i = 3 To Sheets("EXPORT").Range("e65536").End(xlUp).row
    If Cells(i, 28).Value <= Date + 0 And Cells(i, 29) = "" Then '28 = Due Date , 29 = Payment
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    With Sheets("EXPORT")
    strto = .Cells(i, 44).Value '44 = email address
    strcc = "" '.Cells(i, 44).Value
    strbcc = ""
    strsub = "AutoMail: NON PAYMENT"
    strbody = "Hi" & " " & .Cells(i, 13).Value & vbNewLine & vbNewLine & _
    "The Bill for Invoice no." & " " & .Cells(i, 3).Value & " " & _
    "; Customer :" & " " & .Cells(i, 1).Value & " " & _
    "is due on " & .Cells(i, 28).Value & _
    " " & vbNewLine & vbNewLine & _
    "Please request your customer to expedite payment!!!" & _
    vbCrLf & vbCrLf & _
    "If shipment have been arrange, please inform the PIC to update shipment date." & _
    vbCrLf & vbCrLf & _
    "Thank you."
    '.Cells(i, 16) = Now()
    End With

    With OutMail
    .To = strto
    .CC = strcc
    .BCC = strbcc
    .Subject = strsub
    .Body = strbody
    '.Send
    .display

    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    End If
    Next i
    End Sub
    TIA!!!
    Last edited by cyee; 07-04-2013 at 11:39 PM. Reason: typo error

Thread Information

Users Browsing this Thread

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

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