Change Subject of 500 mails sitting in Outbox with Macro

[Jacques]

My invoicing/billing system creates invoices which are sent to my outbox. All of the invoices have the same Subject except that the invoice number is at the end = "Tax Invoice INXXXXX". I usually disable my internet connection and then move the 500+ invoices to a sub-folder linked to my Outbox/Bulk Mail prior to running the invoices-program. I would like to run a macro to rename or add the words "PROFICIO" to "Tax Invoice INXXXXX" and have tried this with the scripts online. The problem is I cannot run them with rules as there is no sending rule linked to scripts only receiving rules. It should be a very simple Macro telling the program to Amend all files in the "Bulk Mail" folder and to add the words "PROFICIO" to "Tax Invoice INXXXXX". Once this is done I need to send all the files in the "Bulk Mail" folder. When I move them they are open and I have to edit and send them individually.

Your assistance and direction would be highly appreciated. Thanking you in advance.

 

[Diane Poremsky]

Have you tried the macro at Add attachments and set email fields during a mail merge to email ?

Try using

If InStr(LCase(Item.Subject), "Tax Invoice IN") Then

item.subject = "PROFICIO " & item.subject

 

[Jacques]

Thanks Diane - The mails are not added with a mail-merge but via a 3rd party accounting software. I am not very clued up with Outlook VBA and use Excel VBA quit often. Can you perhaps help with the Complete Macro I need to run in Outlook VBA? My main problem is directing the Macro to the specified Folder where the emails are located. Thanks Jacques

 

[Diane Poremsky]

As long as Outlook is sending the messages, it should work regardless of how it hits the outbox - it grabs messages as they hit the outbox.

To change the subjects in a folder, you need to loop through each message - this *should* work on drafts - it's from Add a file number or keyword to the subject line of messages and was designed for received messages. I think adding an aItem.Send after the save would send it for you too. (I didn't test it either using it on drafts or sending drafts, will do that next.)

Sub ChangeSubject()

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

iItemsUpdated = 0

For Each aItem In mail.Items
strTemp = "Keyword" & 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

 

[Diane Poremsky]

Ok... Adding aitem.send works BUT it only sends half, need to do a negative loop to count down and send all.

 

[Diane Poremsky]

ok - this sends most of the messages - for some reason, some fail with an error about inline messages (I'm using 2013) - on error resume next with send what it can and leave the rest.

Sub ChangeSubject()

Dim myolApp As Outlook.Application

Dim aItem As Variant 'As Object

Set myolApp = CreateObject("Outlook.Application")

Set mail = myolApp.ActiveExplorer.CurrentFolder.Items

Dim i As Long

Dim strTemp As String

Dim intCount As Integer

On Error Resume Next

intCount = mail.Count

For i = intCount To 1 Step -1

Set aItem = mail.Item(i)
strTemp = "a " & aItem.Subject
aItem.Subject = strTemp
aItem.Save
aItem.Send

Next i

Set myolApp = Nothing

End Sub

 

[Jacques]

Thanks a million Diane! I did a test on 10 mails and it works great!