Subject change on selected emails

[Patrick Moore]

Hi all

Disclaimer - I have no real VBA knowledge!

I have a customer who wants to be able to change the subject line on emails in batches. The below works for all messages in a folder, but they specifically want to be able to select individual mails as an option. Any help will be much appreciated!

Sub AddREMNumberToAllMessagesInFolder()
Dim myolApp As Outlook.Application
Dim aItem As Object

Set myolApp = CreateObject("Outlook.Application")
Set mail = myolApp.ActiveExplorer.CurrentFolder

Dim iItemsUpdated As Integer
Dim strTemp As String
Dim strFilenum As String

strFilenum = InputBox("Enter the REM number to be prefixed to the subject line of ALL the messages in the current folder:")
iItemsUpdated = 0
For Each aItem In mail.Items
    strTemp = "[" & strFilenum & "] " & aItem.Subject
      aItem.Subject = strTemp
      iItemsUpdated = iItemsUpdated + 1
    aItem.Save
Next aItem

MsgBox iItemsUpdated & " of " & mail.Items.Count & " Messages Updated"
Set myolApp = Nothing
End Sub

Many thanks
Patrick

 

[Elrick]

Hi Patrick, same like you I have no VBA knowledge but I've found this, hope it helps.

Sub renameEmails()
On Error Resume Next
Dim MsgColl As Object
Dim msg As Outlook.MailItem
Dim objNS As Outlook.NameSpace
Dim i As Long
Dim subjectname As String

Select Case TypeName(Application.ActiveWindow)
Case "Explorer"
' a collection of selected items
Set MsgColl = ActiveExplorer.Selection
Case "Inspector"
' only one item was selected
Set msg = ActiveInspector.CurrentItem
End Select
On Error GoTo 0

If (MsgColl Is Nothing) And (msg Is Nothing) Then
GoTo ExitProc
End If
subjectname = InputBox("Enter Subject Name", "Subject?")
If Not MsgColl Is Nothing Then
For i = 1 To MsgColl.Count
' set an obj reference to each mail item so we can move it
Set msg = MsgColl.Item(i)
With msg
.Subject = subjectname
.Save
End With
Next i
ElseIf Not msg Is Nothing Then
msg.Subject = subjectname
End If
ExitProc:
Set msg = Nothing
Set MsgColl = Nothing
Set olMyFldr = Nothing
Set objNS = Nothing
End Sub

 

[Patrick Moore]

Elrick - thanks, already found this script but have no idea how to merge the two as they are quite different.

 

[Diane Poremsky]

This line uses all messages in the folder:
For Each aItem In mail.Items

you need to change it to selection. I have sample general purpose code here - change the for each lines (and add the necessary dim & set lines) - http://www.slipstick.com/developer/code-samples/working-items-folder-selected-items/ -

Dim currentExplorer As Explorer
    Dim Selection As Selection
 
    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection
   
For Each aItem In Selection
  ' do whatever

 

[Elrick]

I've change the code, tested, seems to be ok:

Sub AddREMNumber()
Dim myolApp As Outlook.Application
Dim aItem As Object
Dim currentExplorer As Explorer
Dim Selection As Selection

Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
Set myolApp = CreateObject("Outlook.Application")
Set mail = myolApp.ActiveExplorer.CurrentFolder

Dim iItemsUpdated As Integer
Dim strTemp As String
Dim strFilenum As String

strFilenum = InputBox("Enter the REM number to be prefixed to the subject line of ALL the messages in the current folder:")
iItemsUpdated = 0
For Each aItem In Selection
    strTemp = "[" & strFilenum & "] " & aItem.Subject
      aItem.Subject = strTemp
      iItemsUpdated = iItemsUpdated + 1
    aItem.Save
Next aItem

MsgBox iItemsUpdated & " of " & mail.Items.Count & " Messages Updated"
Set myolApp = Nothing
End Sub

 

[Elrick]

Diane, how to modify this code to make it count items and write item number in brackets in subject, for example I have 3 e-mails in folder and I want them this way:

[1] Sample Subject
[2] Sample Subject 2
[3] Sample Subject 3

so each message have own number. I would like to apply this to selected folder.

I've found this code to count items, but I'm not sure how to put all this together:

Sub CountSelectedItems()
    Dim objSelection As Outlook.Selection
    Dim Result As Integer
    Set objSelection = Application.ActiveExplorer.Selection
    Result = MsgBox("Number of selected items: " & _
      objSelection.Count, vbInformation, "Selected Items")
End Sub

 

[Michael Bauer]

See the previous code, which shows how to add something in brackets to the subject. And the iItemsUpdated variable is incremented each loop, I think that's the value you want to use.

 

[Elrick]

See the previous code, which shows how to add something in brackets to the subject. And the iItemsUpdated variable is incremented each loop, I think that's the value you want to use.

Thanks Michael, that works fine, have one more question, what I have to add to make this code clear previous subject every time I run this macro, I don't want things like [0][1][2], is it possible to keep original subject and just replace things in bracket but with new values? Here is the code with your update:

Sub ItemID()
Dim myolApp As Outlook.Application
Dim aItem As Object
Dim currentExplorer As Explorer
Dim Selection As Selection

Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
Set myolApp = CreateObject("Outlook.Application")
Set mail = myolApp.ActiveExplorer.CurrentFolder

Dim iItemsUpdated As Integer
Dim strTemp As String
Dim strFilenum As String

strFilenum = InputBox("Enter the REM number to be prefixed to the subject line of ALL the messages in the current folder:")
iItemsUpdated = 0
For Each aItem In Selection
    strTemp = "[" & iItemsUpdated & "] " & aItem.Subject
      aItem.Subject = strTemp
      iItemsUpdated = iItemsUpdated + 1
    aItem.Save
Next aItem

MsgBox iItemsUpdated & " of " & mail.Items.Count & " Messages Updated"
Set myolApp = Nothing
End Sub

 

[Patrick Moore]

This line uses all messages in the folder:
For Each aItem In mail.Items

you need to change it to selection. I have sample general purpose code here - change the for each lines (and add the necessary dim & set lines) - http://www.slipstick.com/developer/code-samples/working-items-folder-selected-items/ -

Dim currentExplorer As Explorer
    Dim Selection As Selection

    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection
  
For Each aItem In Selection
  ' do whatever

Diane, thanks very much for your assistance - this will do the trick

 

[Diane Poremsky]

Do you mean if you have 15 messages, they will be numbered 1 -15... you want them numbered 1 -5 for subject 1 where there are 5 messages with the same subject, 6-8 for subject 2 (with 3 messages), and so on?

Or that when you tested it on the live mailbox, you goofed and everything is showing just as [0], [1], [2], with the original subject gone? Or some variation of this. (BTDT, once or twice!)

The first one is way above my page grade, unless you only select the messages in one thread and run it. iItemsUpdated should reset to 0 each time you run it. The second one I've done once or twice, and yes, you can fix the subject using VBA.

BTW - it looks like you aren't using this:
strFilenum = InputBox("Enter the REM number to be prefixed to the subject line of ALL the messages in the current folder:")

if so, it can be deleted.

 

[Elrick]

Do you mean if you have 15 messages, they will be numbered 1 -15... you want them numbered 1 -5 for subject 1 where there are 5 messages with the same subject, 6-8 for subject 2 (with 3 messages), and so on?

Or that when you tested it on the live mailbox, you goofed and everything is showing just as [0], [1], [2], with the original subject gone? Or some variation of this. (BTDT, once or twice!)

The first one is way above my page grade, unless you only select the messages in one thread and run it. iItemsUpdated should reset to 0 each time you run it. The second one I've done once or twice, and yes, you can fix the subject using VBA.

BTW - it looks like you aren't using this:
strFilenum = InputBox("Enter the REM number to be prefixed to the subject line of ALL the messages in the current folder:")

if so, it can be deleted.

Let say I have 15 messages, I run my script and each selected message will get number in subject in order so it will be:
[1]Subject [2]Subject [3]Subject ...[15]Subject

I've just received another message so I have 16 e-mails, I'm gonna run my script, at the moment everything shows as:

[1][1]Subject [2][2]Subject [3][3]Subject ... [16]Subject

but I want:

[1]Subject [2]Subject [3]Subject ... [16]Subject

I want to overwrite previous numbers in brackets but still keep original subject. I don't know if this make sense, thanks for info about strFilenum.

 

[Diane Poremsky]

Ok, the macro I used to fix my goof will work for this too - it reads the subject from the header and updates the subject field. Or you could just trim the [nn] part off and add it back :

strtemp = Right(aItem.Subject, Len(aItem.Subject) - InStr(1, aItem.Subject, "[") + 1)
strTemp = "[" & iItemsUpdated & "] " & strTemp

in English )) that says get the character count of the subject, beginning with the first bracket then use only that many right most characters.

The other option would be to use an if and skip the ones that have brackets already then get the value in the bracket. This insures the order doesn't change. The first one, that gets the right most characters is the easiest and you shouldn't have a problem with the order changing, unless a message is deleted, but if you wanted to do it, maybe something like this:

if InStr(aitem.Subject, "[") < 4 then 'allows for RE: FW: or erroneous leading spaces, if messages will never have these use 1
' get #
iItemsUpdated = some combination like we used for strtemp to strip all but the #
else
strTemp = "[" & iItemsUpdated & "] " & aItem.Subject
end if

 

[Elrick]

Thanks Diane, I will try this, I couldn't find this anywhere.

 

[Elrick]

Hi all, I can't jump over this one:

Sub AssignToSelected()

' Assign ID to selected e-mails

Dim oneitem, onecollection As Object
Set onecollection = Application.ActiveExplorer.Selection
iTotalItems = onecollection.count

iItemsUpdated = 1
For i = iTotalItems To 1 Step -1
Set oneitem = onecollection.Item(i)
strTemp = "[" & iItemsUpdated & "] " & oneitem.Subject
oneitem.Subject = strTemp
iItemsUpdated = iItemsUpdated + 1
oneitem.Save
Set oneitem = Nothing
Next

End Sub

I can't process more then 250 e-mails, I was hoping this

 For i = iTotalItems To 1 Step -1

will fix the problem but it won't, any sugesstions?

Thank you

 

[Michael Bauer]

See this one:
http://www.vboffice.net/en/developers/limited-loops