Replace a string in a sent email

Question

The following code creates and sends a simple email. After the email is sent it replaces a string in the email body.

My code works when I use the debugger’s single-step feature to execute the code. It also works when I add a MsgBox instruction with a “click to continue” button after the objMsg.Send instruction. It does not work when I execute the macro without interruption, but tells me that Outlook cannot save an email to the folder when a macro is running.

Sub CreateNewMessage()

     objMsg As MailItem

    Set objMsg = Application.CreateItem(olMailItem)

     With objMsg
      .To = "mblasberg@inoxel.com"
      .subject = "This is the subject"
      .BodyFormat = olFormatHTML
      .Body = "How are you doing?"
    End With
    objMsg.Send

  ' The following code replaces in the email body the string "you" with "they"

  ' Because I could not find how to open the "last sent" email,
  ' I used the "Sent Items" folder email count as as the pointer
  ' to the last email.

    Dim myNameSpace As Outlook.NameSpace
    Dim myFolder As Outlook.Folder
    Dim myItem As Object

    Set myNameSpace = Application.GetNamespace("MAPI")
    Set myFolder = _
        myNameSpace.GetDefaultFolder(olFolderSentMail)

    Dim EmailCount As Integer

    EmailCount = myFolder.Items.Count
    Set myItem = myFolder.Items(EmailCount)
    myItem.HTMLBody = replace(myItem.HTMLBody, "you", "they")
    myItem.Save

End Sub

score 2 · Answer 1 · edited May 23 '17 at 12:33

2

The delaying actions you take are enough for the send to complete. Without the delay the message is not yet in the Sent items folder.

You could instead use ItemAdd with appropriate conditions in the code so it processes applicable messages.

This will process the item most recently added to the Sent Items folder.

Code for ThisOutlookSession

Option Explicit

Private WithEvents sItems As Items 

Private Sub Application_Startup() 
    Dim objNS As NameSpace 
    Set objNS = GetNamespace("MAPI") 
    ' default local Sent Items folder
    Set sItems = objNS.GetDefaultFolder(olFolderSentMail).Items 
End Sub

Private Sub sItems_ItemAdd(ByVal item As Object) 

    If TypeName(item) = "MailItem" Then          
          ' ******************
          ' do something here with item
          ' ******************
           Debug.Print item.subject
    End If

End Sub

Source in case this untested code has typos. How do I trigger a macro to run after a new mail is received in Outlook?

edited May 23 '17 at 12:33

Community

1
1

answered Dec 09 '15 at 23:01

niton

8,771
21
32
52

You should also never assume that MAPIFolder.Items.Item(Items.Count) will give you the last message. The order is undetermined unless you explicitly sort the Items collection. – Dmitry Streblechenko Dec 09 '15 at 23:15
Thank you Dmitry. When I added delays, it did not help. In my application I cannot add a MSgBOX or other manual delays. It has to work automatically. Can you explain me or suggest code how I can detect that the message was saved in the Sent Items Folder. It is not so clear how would ItemAdd do the job. It seems to me that unless the macro is not completed or stopped, the system would never save the sent message in the Sent Items folder. Thank again for your help. – Menachem Dec 10 '15 at 00:13
Note that I nee to search and replac ea string on each message that I send. – Menachem Dec 10 '15 at 00:15
Niton, my previous email was meant primeraly for you. Regarding Dmitry's comment: I noticed and tested that the MAPIFolder.Items.Item(Items.Count) is always based on the save date. The oldest one index is 1. The maximum cound index is allways the last one. – Menachem Dec 10 '15 at 01:01
@Menachem The index was opposite to yours, on my installation. – niton Dec 10 '15 at 02:20
Niton, the use of the ItemAdd event solved my problem. Thanks a lot! Because I have several thousand emails in my Sent Items folder, I cannot sort. Any advice how to find the latest sent email? – Menachem Dec 10 '15 at 03:22
@Menachem first try searching http://stackoverflow.com/help/searching if not successful ask a new question. – niton Dec 10 '15 at 04:07
Dmitry is correct regarding the Item(Items.Count) order. Therefore I sorted the messages on date and made it work. I posted the final code. Thank you all for your help. – Menachem Dec 15 '15 at 19:53

score 0 · Answer 2 · answered Dec 15 '15 at 19:58

Following the comments that I received I am posting the final code that I tested and works. It replaces a string in the last sent message (after being sent and saved).

Sub Replace_LSM_String()

Set Folder = Application.Session.GetDefaultFolder(olFolderSentMail)
Set Items = Folder.Items
Items.sort "[ReceivedTime]", False
Set oldestMessage = Items.GetLast
oldestMessage.HTMLBody = Replace(Items.GetLast.HTMLBody, "old string",_ 
"new   string")
Items.GetLast.Save

End Sub

Replace a string in a sent email

2 Answers2

Linked