Exploding a Single Email
The macro and explanations shown below are pulled directly from my book – "Link Em Up On Outlook."
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
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
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.