Exploding a Single Email

The purpose of this macro is to "explode" a single email into many individually addressed emails. If you would like to send the same email to thirty (or more) people and avoid the use of a generic address and not allow the recipient to see the other addresses to which you sent this email, this macro accomplishes that. For a better explanation of the macro code see the comments following the macro itself.

Public Sub explode-mail()
' assuming code is running in outlook 2000/2002s
' vba environment
Dim Bccs() As String
Dim nBccs As Integer
Dim i As Integer
Dim colSelection As Outlook.Selection
Dim oMessage As Outlook.MailItem
Dim colObj As Object
Set colSelection = Application.ActiveExplorer.Selection
If colSelection.Count = 0 Then
Set colSelection = Nothing
Exit Sub
End If
For Each colObj In colSelection
Bccs = Split(colObj.BCC, ";", -1)
nBccs = UBound(Bccs)
For i = 0 To nBccs
Set oMessage = colObj.Copy
oMessage.BCC = ""
oMessage.To = Bccs(i)
oMessage.HTMLBody = colObj.HTMLBody
' comment this line out after testing is complete
oMessage.Display True
' UNcomment this line after testing is complete
'oMessage.Send
Next
Next
Set colSelection = Nothing
End Sub

In this macro, the colSelection variable stores the entire message. If there is no message when the macro runs, it exits per the Exit Sub line.

The first For Each … Next loop breaks the addresses in the BCC field apart.

The second For Each … Next loop creates a new e-mail with the same body and puts the address into the TO field. There are actually two lines that work with the e-mail in this loop. The first is

oMessage.Display True

This line displays the message and allows you to click Send before sending it. With this line in place, you will actually have to send fifty e-mails if you have that many addresses in the BCC field. The other line that handles the e-mail is

'oMessage.Send

Notice the apostrophe (') in front of this line. That means it is currently commented out and will not execute. The comments at the end of these two lines give the reasoning behind this. Test it first with Display implemented to see how it works. After that you can comment the Display line and remove the comment character on the Send line.

The message as it is designed will be sent to each address in the BCC field. A new e-mail will be created for each one and that address will be placed in the TO field of the new message.

Keep Outlook security in mind when running this or any macro. It may pop up with each e-mail and it may disable running macros altogether. To get around an inability to run macros, take the following steps within Outlook.

• Click on Tools.
• Hover over Macro.
• Click on Security.
• Select Medium or Low.

Do not forget to set the security back when you are finished with it. Leaving the security setting at a lower setting could expose your computer to the many viruses and Trojans that are out on the World Wide Web.

As stated above, this macro, as it is currently written, will place the addresses in the BCC field as. You can change the addresses to be the TO field by changing the line

Bccs = Split (colObj. BCC , ";", -1) to  
Bccs = Split (colObj.TO, ";", -1)

An acknowledgement is given in my book and here to Stephane Friggeri for providing this macro along with permission to use it.