+ Reply to Thread
Results 1 to 2 of 2

Send reminder mail from excel using outlook

  1. #1
    Registered User
    Join Date
    10-14-2011
    Location
    germany
    MS-Off Ver
    Excel 2010
    Posts
    1

    Send reminder mail from excel using outlook

    Hi Experts,

    I'm new to vba and i'm trying to send mail from excel through outlook, i know its a very simple logic but i'm not getting any near. I have data about machines to be calibrated which has the due date and person responsible for calibration for each machines in excel. so i need excel to send mail to the responsible person for that machine when the due date is reached. Hope i have cleared myself . This is the macro i'm using presently
    considering column e5 to e10 has the dates to be sent

    Sub Remmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim lastRow As Long
    Dim dateCell As Date
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    On Error GoTo cleanup
    For Each cell In Range(Cells(5, 6), Cells(lastRow, 6))
    dateCell = cell.Value
    If Day(dateCell) = Day(Date) And Month(dateCell) = Month(Date) Then
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
    .To = cell.Offset(0, 7).Value
    .Subject = "Calibration reminder" & cell.Offset(0, 8).Value
    .Body = "Dear " & Cells(cell.Row, "F").Value _
    & vbNewLine & vbNewLine & _
    "this is an automatically generated mail " _
    & vbNewLine & vbNewLine _
    & vbNewLine & vbNewLine & _
    "Cheers," & vbNewLine & _
    "Arun"
    .send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    End If
    Next cell
    cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
    End Sub


    thanks


    Arun
    Attached Files Attached Files

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send reminder mail from excel using outlook

    Hi Arun
    Welcome to the Forum!

    Please add code tags to your code (see Forum Rule #3)...post a new post to your Thread when you've done so...I'll see if I can help.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ Reply to Thread

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