Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Nov 2016
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to send customized mail body for reminders

    Dear Friends,

    I have seen post in these forums regarding reminder mails through macro. But, it is not helping my query. Since it is not working.

    I have prepared a macro which sends email to recipients whenever a project is due or will be due. 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.

    What is required, which i am unable to get it in this forum.

    Expected result: i want mail body to be like this whenever macro is run:
    Currently i am unable to put code for below example

    Dear <Name of the person handling project>
    Your Project <Name> is due on < uat due date> and needs attention.
    Kindly ignore if already done and update in sheet.


    Data to be captured by macro:
    Project name you will get in column D i.e. description
    Person handling project in column P
    uat due date mentioned in column I

    Please help.. thanks in advance.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Rash,

    Use the following format for the body. The code assumes row 2 is the row of data. Change the 2 to a variable to loop through different rows.

    Code:
    strbody = "Dear " & Cells(2, "P") & vbNewLine & vbNewLine & _
          "Your Project " & Cells(2, "D") & " is due on " & Cells(2, "I") & _
          " and needs attention." & vbNewLine & _
          "Kindly ignore if already done and updated in sheet."
    HTH,
    Maud

  3. #3
    New Lounger
    Join Date
    Nov 2016
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maudibe,

    Thanks for taking out ur time..

    i am using this code which was missing in my earlier attachment. Can you guide me where exactly that 2 variable shall be inputed in code. Data is starting from Row 6 right...row 2 is an example right?

    Sub SendReminderMail()
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim iCounter As Integer
    Dim MailDest As String

    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)

    With OutLookMailItem
    MailDest = ""
    For iCounter = 1 To WorksheetFunction.CountA(Columns(15))
    If MailDest = "" And Cells(iCounter, 15).Offset(0, -1) = "Send Reminder" Then
    MailDest = Cells(iCounter, 15).Value
    ElseIf MailDest <> "" And Cells(iCounter, 15).Offset(0, -1) = "Send Reminder" Then
    MailDest = MailDest & ";" & Cells(iCounter, 15).Value
    End If
    Next iCounter

    .BCC = MailDest
    .Subject = "UAT Reminder"
    .Body = "Reminder: Your UAT item is pending, Kindly Check Tracker for more details.Please ignore if already done."
    .Send
    End With

    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing
    End Sub

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Try making these changes to your code (in blue). Your code does not accommodate a Primary recipient (.To=), only BCC. Note: This code has not been tested.

    HTH,
    Maud

    Code:
    Sub SendReminderMail()
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim iCounter As Integer
    Dim MailDest As String
    
    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
    
    With OutLookMailItem
    MailDest = ""
    For iCounter = 1 To WorksheetFunction.CountA(Columns(15))
    If MailDest = "" And Cells(iCounter, 15).Offset(0, -1) = "Send Reminder" Then
    MailDest = Cells(iCounter, 15).Value
    ElseIf MailDest <> "" And Cells(iCounter, 15).Offset(0, -1) = "Send Reminder" Then
    MailDest = MailDest & ";" & Cells(iCounter, 15).Value
    End If
    
    .To =
    .BCC = MailDest
    .Subject = "UAT Reminder"
    strbody = "Dear " & Cells(iCounter, "P") & vbNewLine & vbNewLine & _
          "Your Project " & Cells(iCounter, "D") & " is due on " & Cells(iCounter, "I") & _
          " and needs attention." & vbNewLine & _
          "Kindly ignore if already done and updated in sheet."
    .Body = strbody
    '.Send
    .Display 'FOR TESTING
    Next iCounter
    End With
    
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing
    End Sub

  5. #5
    New Lounger
    Join Date
    Nov 2016
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey Maudibe..

    Thanks for your help...it is working...
    Apologies for the late reply.

    Thanks once again. God Bless!

    Regards,
    Gaurang

  6. #6
    New Lounger
    Join Date
    Dec 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    this was very helpful for a similar notification set up I am working on. My issue is that, I need a separate email generated based on if "Send Reminder" is tagged on a specified column.
    thank you in advance!!

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Change the two lines containing
    And Cells(iCounter, 15).Offset(0, -1) = "Send Reminder" Then
    to something like
    And Cells(iCounter, 15).Offset(0, -1) <> "" Then
    This assumes the cell referenced by the offset has a value when you want to send mail, but is empty when you don't. Depending on how you "tag" the column you may need to change this.

    cheers, Paul

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    or, since..
    Cells(iCounter, 15).Offset(0, -1)
    ..is really..
    Cells(iCounter, 14)

    ..why not just use..
    Cells(iCounter, "N")
    ..and then it is easier to follow what column you are referring to.

    zeddy

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Hi winbasic,

    The columns that you use may not align with the OP's therefore the offsets from the reference cell may not be valid. Below is generic code that can easily be customized by replacing with values specific to your project. You can change "Send Reminder" in the code to whatever tag you use on your spreadsheet.

    Code:
    Sub SendReminderMail()
    '-------------------------------------------------
    'declare and set variables
    Dim OutLookApp As Object, OutLookMailItem As Object
    Dim RecipCol As Integer, TagCol As Integer
    Dim EmailCol As Integer, ProjectCol As Integer
    Dim I As Integer, StartRow As Integer, EndRow As Integer
    Dim Recip As String, StrBody As String
    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
    With OutLookMailItem
    '-------------------------------------------------
    'REPLACE THE VALUES SPECIFIC TO YOUR PROJECT
        StartRow = 1 'REPLACE WITH THE FIRST ROW TO CYLE THROUGH
        EndRow = 10  'REPLACE WITH THE LAST ROW TO CYLE THROUGH
        TagCol = 5  'REPLACE WITH THE COLUMN ROW THAT INDICATES IF EMAIL IS TO BE SENT
        EmailCol = 7  'REPLACE WITH THE COLUMN THAT CONTAINS THE EMAIL ADDRESSES
        RecipCol = 1  'REPLACE WITH THE COLUMN THAT HAS THE RECIPIENT NAMES
        DueDateCol = 3  'REPLACE WITH THE COLUMN THAT HAS THE PROJECT DUEDATE
        ProjectCol = 2  'REPLACE WITH THE COLUMN THAT HAS THE PROJECT NAME
    '-------------------------------------------------
    'SEND EMAIL
        For I = StartRow To EndRow
            If Cells(I, TagCol) = "Send Reminder" Then
                .To = Cells(I, EmailCol)
                .BCC = ""
                .Subject = "UAT Reminder"
                StrBody = "Dear " & Cells(I, RecipCol) & vbNewLine & vbNewLine & _
                      "Your Project " & Cells(I, ProjectCol) & " is due on " & Cells(I, DueDateCol) & _
                      " and needs attention." & vbNewLine & _
                      "Kindly ignore if already done and updated in sheet."
                .Body = StrBody
                '.Send
                .Display 'FOR TESTING
            End If
        Next I
    End With
    End Sub

  10. #10
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 986 Times in 916 Posts
    47 ways to skin a cat.

    cheers, Paul

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •