Email to recipient with details in Mail Body using macro

[Gaurang Mhatre]

Dear Friends,

I have prepared a macro which sends email to recipients whenever a project is due or will be due and for which i have set criteria of +3 i.e. if project is due on 19-Nov-16 then excel will show alert 3 days before 19 nov.
In macro i have put plain text in body due to which there is no identification to whom it has been sent or to whom it has been addressed.

For Example i want mail body to be like this whenever macro is run:
Also, when UAT date column is blank then mail should not be sent.
Project name you will get in column D i.e. description

Dear <Name>
Your Project <Name> is due and needs attention.
Kindly ignore if already done and update in sheet.

Regards,
Gaurang M


Basically i am sending reminders to people whose project is about to get due i.e. sending reminders 3 days before due date.

Please help me with the code for mail body as i am getting error.

Thanks for help in advance.

 

[PCosta87]

Hi @Gaurang Mhatre,

Please try this code... I commented the ".send" so you can see the actual email. If it is ok, just uncomment and you should be good to go.

Sub Send_Email()

    Dim c As Range
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    
    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
  
    For Each c In Range("N6:N" & Columns("N").Cells(Rows.Count).End(xlUp).Row).Cells
        If c.Value = "Send Reminder" Then
            With OutLookMailItem
                .To = c.Offset(0, 1).Value
                .Subject = "Insert Subject here"
                .HTMLbody = "Dear " & c.Offset(0, 2).Value & "<br>" & "Your Project '" & c.Offset(0, -10).Value & "' is due and needs attention." & "<br>" & "Kindly ignore if already done and update in sheet." & "<br>" & "<br>" & "Regards," & "<br>" & "Gaurang M"
                .Display
'                .Send
            End With
        End If
    Next c

End Sub

Hope this helps

 

[PCosta87]

Hi

Revised (just realized I forgot to change one thing in the above code)

Sub Send_Email()

    Dim c As Range
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
  
    For Each c In Range("N6:N" & Columns("N").Cells(Rows.Count).End(xlUp).Row).Cells
  
    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
        
        If c.Value = "Send Reminder" Then
            With OutLookMailItem
                .To = c.Offset(0, 1).Value
                .Subject = "Insert Subject here"
                .HTMLbody = "Dear " & c.Offset(0, 2).Value & "<br>" & "Your Project '" & c.Offset(0, -10).Value & "' is due and needs attention." & "<br>" & "Kindly ignore if already done and update in sheet." & "<br>" & "<br>" & "Regards," & "<br>" & "Gaurang M"
                .Display
'                .Send
          End With
        End If
    
    Next c

End Sub

 

[Gaurang Mhatre]

Hey Pcosta,
Apologies for the delay in response.

Thanks alot for the help... It is working..

God bless.
Regards,
Gaurang

 

[PCosta87]

Hey Pcosta,
Apologies for the delay in response.

Thanks alot for the help... It is working..

God bless.
Regards,
Gaurang

You are welcome