0

I am trying to create a list on excel to track which of my selected sent outlook emails (moved to a particular sub folder, "Test") have been replied. For emails which have not been replied, I would like to send a reminder email after a few days. Would it be possible to create an outlook VBA macro to do this?

Currently, my VBA code is only able to pull selected email details in a tracking file.

I know that to track conversations, the PR_CONVERSATION_INDEX should be used, but am not sure how to incorporate it into my code below.

Sub List_Email_Info()

    Dim xlApp As Excel.Application

    Dim xlWB As Excel.Workbook

    Dim i As Long ' Row tracker

    Dim arrHeader As Variant

   

 

    Dim olNS As NameSpace

    Dim olSentFolder As MAPIFolder

    Dim olItems As Items

    Dim olMailItem As MailItem

    Dim olRecipients As Outlook.Recipients

 

   

 

    arrHeader = Array("Date Created", "Subject", "Recipient's Name")

   

 

    Set xlApp = CreateObject("Excel.Application")

    xlApp.Visible = True

    Set xlWB = xlApp.Workbooks.Add

   

 

    Set olNS = GetNamespace("MAPI")

    Set olSentFolder = olNS.GetDefaultFolder(olFolderSentMail).Folders("test")

    Set olItems = olSentFolder.Items

   

 

    i = 1

   

 

    On Error Resume Next

   

 

    xlWB.Worksheets(1).Range("A1").Resize(1, UBound(arrHeader) + 1).Value = arrHeader

   

 

    For Each olMailItem In olItems

       

        xlWB.Worksheets(1).Cells(i + 1, "A").Value = olItems(i).CreationTime

        xlWB.Worksheets(1).Cells(i + 1, "B").Value = olItems(i).Subject

        xlWB.Worksheets(1).Cells(i + 1, "C").Value = olItems(i).To

       

        i = i + 1

       

    Next olMailItem

   

 

    xlWB.Worksheets(1).Cells.EntireColumn.AutoFit

   

 

    MsgBox "Export complete.", vbInformation

   

 

    Set xlWB = Nothing

    Set xlApp = Nothing

   

 

    Set olItems = Nothing

    Set olSentFolder = Nothing

Would appreciate any help on this!

========================================================================== Current code is adapted from: https://learndataanalysis.org/pull-outlook-emails-detail-into-excel-using-vba/

Eleanor Tay
  • 39
  • 1
  • 5

1 Answers1

1

As this is not a programming service where ready to run code is served, I'd suggest you do like this: When an email is received and moved, you set a calendar appointment in VBA when to send the reminder if no reply has been received. https://learn.microsoft.com/en-us/office/vba/outlook/how-to/items-folders-and-stores/create-an-appointment-as-a-meeting-on-the-calendar

You also set a trigger for it in VBA and let the action be to resend the email: Use calendar appointment in outlook to trigger VBA macro

If a reply is received you delete that particular calendar time: https://learn.microsoft.com/en-us/office/vba/api/outlook.appointmentitem.delete

Otherwise the calendar trigger will send the email reminder.

To do it this way uses built-in resources in Outlook so you don't have to write them yourself.

StureS
  • 227
  • 2
  • 10