Solved Outlook VBA to Sort Inbox by Date and find Email by a Partial string

[kengooch]

I have a report that I send out each day about Staffing and Metrics. The email staff names and Metrics categories rarely change only the actual numbers.

What I need to do is find the previous report in the Sent Mail folder by finding it by the Last Date Sent where the Subject has the Partial String "Staffing and Metrics - ".
Once I locate that email I think I can use the objInsp.CommandBars.ExecuteMso to resend the message.

I would also append the "Staffing and Metrics - " partial string to the Date for today and rewrite it into the Subject line. I have code that will do that.

I am clueless about how to use VBA to tell outlook to give me that most recent email by date with the partial string "Staffing and Metrics".
If someone knows how to do this, I would REALLY appreciate your help!!!

 

[Gasman]

Wouldn't it just be easier to create a new message?

 

[kengooch]

Not really, to have all of the names and everything in place is very helpful, plus some of the statistics given I have to calculated the differences between yesterday and today and in some cases provide a percentage. This is done everyday, even if the macro only saved me a 3 or 4 minutes, over a year, that's several days worth of work. I started doing the block and copy thing, and then discovered the "resend" which keeps all of the people it has to be sent too, the subject line etc. I just have to go in and change the date and then all the numbers. It's just hard to find anyone that knows much about outlook VBA. I have several macros and have pieced some things together, but was hoping someone with more skill thann me would know what to do.

 

[Gasman]

Well if you do not get much luck here, this is where I have received Outlook help in the past from a user called GMayor

Outlook Forum

Microsoft Outlook discussions.

www.msofficeforums.com

He also hangs out at http://www.vbaexpress.com/forum/forumdisplay.php?18-Outlook-Help

 

[sxschech]

I have some code that may work. If I understand your question, you are looking for an email that was already sent and match/select it based on the subject string. The message will contain the previous message text and new message text. If so, then I will edit the code to remove some personal information and provide it. Will most likely need further editing to fit your situation such as which mailbox to look in.

 

[kengooch]

I have some code that may work. If I understand your question, you are looking for an email that was already sent and match/select it based on the subject string. The message will contain the previous message text and new message text. If so, then I will edit the code to remove some personal information and provide it. Will most likely need further editing to fit your situation such as which mailbox to look in.

Thank you so much! This sounds really good. I am sure i can edit the code as needed. Look forward to what you will send!

 

[kengooch]

I have some code that may work. If I understand your question, you are looking for an email that was already sent and match/select it based on the subject string. The message will contain the previous message text and new message text. If so, then I will edit the code to remove some personal information and provide it. Will most likely need further editing to fit your situation such as which mailbox to look in.

Were you able to clean up that code you mentioned so you can send it?

 

[sxschech]

Sorry, late to work due to car encased in Ice. Here is the code with some slight edits. It called a few other functions which you may not need, but included in case of use. If I missed any other helper functions, let me know. The part that searches for previous messages is under SentFldr=True. In my case was looking for subject containing a number sequence, so if you are just going on a generic subject, then you can simplify that search string. Some of the code originally came from vbaexpress as mentioned by gasman.

Code has several optional inputs so it can be used for various email situations, such as having attachments or not, message or not, cc or not etc.

Here is how the function is called:

Call Outlook_ReplyAllTEST(Forms!frmSignedDocs.txtNewFileName, "Reply All", , , , "N:\Data Folders\Letters\Signed\" & Me.txtNewFileName & ".pdf", , True, , True)

Looks like I need to attach the code as when I pasted, it said

Please enter a message with no more than 10000 characters.

Hope it will work for your situation.

 

[kengooch]

Sorry, late to work due to car encased in Ice. Here is the code with some slight edits. It called a few other functions which you may not need, but included in case of use. If I missed any other helper functions, let me know. The part that searches for previous messages is under SentFldr=True. In my case was looking for subject containing a number sequence, so if you are just going on a generic subject, then you can simplify that search string. Some of the code originally came from vbaexpress as mentioned by gasman.

Code has several optional inputs so it can be used for various email situations, such as having attachments or not, message or not, cc or not etc.

Here is how the function is called:

Call Outlook_ReplyAllTEST(Forms!frmSignedDocs.txtNewFileName, "Reply All", , , , "N:\Data Folders\Letters\Signed\" & Me.txtNewFileName & ".pdf", , True, , True)

Looks like I need to attach the code as when I pasted, it said



Hope it will work for your situation.

Thanks so much. Do you know if there is any place that provides a list of the Field names as they need to be called in vba and/or the alternate numeric codes, as you used with the GetDefaultFolder(5). For example folders("Inbox") vss folders("SentItems") etc. I can't seem to find the lists of these name designations.

 

[kengooch]

So here is what I came up with today while waiting. It also walks through the existing emails to allow the user to pick the specific one they want.

Sub sortItems()
'Assign Variables
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.NameSpace
    Dim olFldr As Outlook.Folder
    Dim objInsp As Outlook.Inspector
    Dim olItem As Object
    Dim olFldrItems As Items
    Dim mItem As MailItem
'Set Objects
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set olFldr = olNs.GetDefaultFolder(olFolderSentMail)
'Sort by Sent Date
    Set olFldrItems = olFldr.Items
    olFldrItems.Sort "[ReceivedTime]", True
'Find the Right Email
    For Each olItem In olFldrItems
        If TypeName(olItem) = "MailItem" Then
            Set mItem = olItem
            vChk = MsgBox(Format(mItem.ReceivedTime, "mm/dd/yyyy") & " " & mItem.Subject & vbCrLf + vbCrLf & "Resend this email?", vbQuestion + vbYesNoCancel, "Locate Email to Resend")
            If vChk = vbCancel Then GoTo jEnd
            If vChk = vbNo Then GoTo jNext
            If vChk = vbYes Then mItem.Display
'MsgBox "Resend the Email"
                Set objInsp = mItem.GetInspector
                objInsp.CommandBars.ExecuteMso ("ResendThisMessage")
               mItem.Close olDiscard
                Set mItem = Application.ActiveInspector.CurrentItem
                mItem.Subject = "CITC Staffing/Metrics - " & Date
             vRDate = Format(InputBox("Enter the Date to change.", "Date to Change", Date - 1), "m/d/yyyy")
             If vRDate = vbCancel Then GoTo jEnd
             vNwDate = Format(Date, "m/d/yyyy")
'Replace the Date
            vBody = mItem.HTMLBody
            vBody = Replace(vBody, vRDate, vNwDate)
            mItem.HTMLBody = vBody
            GoTo jEnd
        End If
jNext:
    Next
jEnd:
End Sub

 

[sxschech]

Thanks so much. Do you know if there is any place that provides a list of the Field names as they need to be called in vba and/or the alternate numeric codes, as you used with the GetDefaultFolder(5). For example folders("Inbox") vss folders("SentItems") etc. I can't seem to find the lists of these name designations.

This?

Microsoft Outlook Constants

learn.microsoft.com

OlDefaultFolders


Or maybe you could use some variation to loop through the folders?

Sub Which_Account_Number()
'List the account number for each email address useful
'to find out which email is the shared account so that
'can send out emails under shared rather than personal.
'https://stackoverflow.com/questions/18652964/sending-from-a-shared-mailbox-without-using-sentonbehalfof
'20190103
'Don't forget to set a reference to Outlook in the VBA editor
Dim olApp As Object
Dim olAcct As Object
Dim i As Long

 'Set OlApp = CreateObject("Outlook.Application")    'Outlook Not Running
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")      'Outlook Running
    If Err.Number <> 0 Then
        Err.Clear
        Set olApp = CreateObject("Outlook.Application")
    End If
    Set olAcct = olApp.session.Accounts("dcc@yourcompany.com")
    For i = 1 To olApp.session.Accounts.Count
        MsgBox olApp.session.Accounts.Item(i) & " : This is account number " & i
    Next i
'------------------------------------------------------------------
'After running this sub, The shared account will likely be 2.
'To use in code add something like this:
'Note : Set may be needed before .SendUsingAccount in some versions of Outlook
'http://www.rondebruin.nl/win/s1/outlook/account.htm
    'With OutMail
    '    .SendUsingAccount = OutApp.Session.Accounts.Item(2)
    'End With
'------------------------------------------------------------------
End Sub