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
Bookmarks