-1

I designed a macro to send emails with attached files to specific email addresses.

Is it possible to send a reminder email if the recipient never replies to my first email?

Sub MonthlyInterco()
    mth = Format(DateAdd("m", -1, Date), "mmmyy")
    Call SendFiles("C:\Users\haha\Desktop\interco\")
End Sub


Function SendFiles(fldName As String, Optional FileType As String = "*.*") 
       
    Dim fName As String
    Dim sAttName As String
     
    Dim olApp As Outlook.Application
    Dim olMsg As Outlook.MailItem
    Dim olAtt As Outlook.Attachments
      
    Set olApp = Outlook.Application
    Set olMsg = olApp.CreateItem(0) ' email
    Set olAtt = olMsg.Attachments
    
    ' to send all
    fName = Dir(fldName)
     
    'to send only certain extensions
    'fName = Dir(fldName & FileType)
      
    olAtt.Add fldName & "XYZ " & mth & " INTERCO STATEMENT.pdf"
      
    Debug.Print fName
    fName = Dir
    
    ' send message
    With olMsg
        .Subject = mth & " Interco Reconciliation"
        .To = "xxx@hotmail.com"
        .CC = "yyy@hotmail.com"
        .HTMLBody = "Hi all," & "<br /><br /> Attached is " & mth & " interco schedule, kindly reconcile and update us if" & "<br /><br /> 1)      Any discrepancies" & "<br />2)      All amount tie to your interco bal" & "<br /><br /> Thank you." & "<br /><br /> Best Regards," & "<br /> zzz" & "<br /> "
        .Display
    End With
      
End Function
Community
  • 1
  • 1
koky
  • 31
  • 1
  • 2
  • 8
  • I sent you email X a week ago. I received email Y from you 4 days ago. Is email Y a reply to X, a reply to earlier email W or a new email not linked to any of my emails? Usually, a reply contains the header and text of the original email. If you can find that header, you can link the reply to your original. Outlook will link your replies to the original but it cannot link their replies to your original. This is you big problem: linking sent and received emails. – Tony Dallimore Nov 05 '16 at 11:42
  • This answer, http://stackoverflow.com/a/12146315/973283, creates an Excel workbook and outputs details of every email in Inbox to it. The objectives are to show you (1) what an email looks like to a VBA macro and (2) how to work down the Inbox examining each email. Modify that macro as instructed and try it out. Study the output and the macro. What information would be available to your macro? How would you find the header of the original email within a reply? How would you adjust the macro to your needs. For example, your emails will not be in Inbox? Are they still in Sent Items. – Tony Dallimore Nov 05 '16 at 11:44
  • I would start by creating a list for each person to whom you send emails showing the emails you have sent them and the emails they have sent you. This list would be a help in identifying emails for which you had yet to receive a reply and would be a step towards the macro you actually want. – Tony Dallimore Nov 05 '16 at 11:45
  • @TonyDallimore it is email Y reply to email X. But sometimes I sent email X, there are no replies back to my email X from the recipients. My question is how do I send another email to remind them again. I have edited and pasted my code above. Thank you for all the answers! – koky Nov 09 '16 at 06:18
  • Do you leave received emails in Inbox? Do you leave send emails in Sent Items? If so you only have to search those folders. Otherwise you will have to search all the folders in which you store emails. You need to match emails to JohnDoe@AcmeCo against emails from JohnDoe@AcmeCo. Only then can you tell if John has failed to reply to your email. Your code only handles the easy bit: sending another email. BTW, some email package expect proper Html not just a few tags. – Tony Dallimore Nov 09 '16 at 08:56
  • @TonyDallimore I received emails in Inbox and I send emails in Sent Items. Will there be a code that simply knows who did not reply to my initial message. If so, a reminder email will be sent. This is because there are alot of different emails in Inbox and Sent Items. By searching through these folders is manual and take some time. – koky Nov 10 '16 at 00:51

1 Answers1

0

There are two basic approaches you could consider.

The first approach is to use events. I do not necessarily recommend this approach since most people like some experience under their belts before tackling events. You can write a routine and tell Outlook to execute it every time a particular event occurs. For example, there are the send email event and the email received event. You could write a routine that is executed every time you send an email. I would not do too much in that routine since you do not want to slow down your normal work. You could perhaps add the time and receiver email address to the end of a text file. A similar routine could record the time and sender email address when an email is received.

Another routine could be run once a day to process these text files. I would write this routine in Excel VBA so results could be written to worksheets which would help you develop your macros. This macro would have to match emails sent to John Doe against emails received from John Doe and produce a list of emails to which you have not received reply. The receivers of those emails are the people to whom you want to send a chaser email.

If the above approach appeals to you, split it into its components. Do not search for a routine that does everything you want because you will not find it. Do not post a question asking for the routine to be written for you because this site is for programmers to help one another develop; it is not a free coding site.

Look up Outlook events. I do not think the help on Outlook VBA is as good as the help on Excel VBA but it is there. Find examples of the two event routines you require. Look up text files. How do you append to a text file? It is not difficult. How are you going to match entries in the sent and received text files? Perhaps the easiest starting point is to copy the file contents to worksheets which you then sort by email address. Code your macros one step at a time. If you have difficulty with a particular step, come here for help. There is no restriction on the number of questions you can ask providing each shows the code you have tried and explains what is not working as you want/expect.

The second approach is a routine that searches Inbox and Sent Items once a day and extract the information recorded by the event routines. I have already given you a link to a routine that searches Inbox and outputs selected properties of each email to a workbook. This could be the skeleton of the routine you need.

You could search the text bodies of emails you receive for strings of the form: “[CR][LF][CR][LF]On Fri, 4 Nov 2016 at 13:43 zzzz > wrote:[CR][LF][CR][LF]”. Almost all replies to your emails will contain text in this format where zzzz will be your name and xxxx@yyyy will be your email address. This could help matching.

I have tried to give you some ideas. I doubt you can achieve a 100% perfect match between sent and received emails but if you work slowly through the necessary steps you could create a very helpful set of macros.

Tony Dallimore
  • 12,335
  • 7
  • 32
  • 61
  • thanks for taking the time to explain the two approaches in detail! I will explore the link in detail and try working on the 2nd approach! :) – koky Nov 11 '16 at 16:10