We were campaigning for GITEX Technology Week when came through the problem of sending Unique Promo Codes to around 200 different Email IDs, saved in an Excel File. Copy pasting each unique code against each email address and then sending it, could have been tiresome method for our marketing lady and thus I decided to help her by introducing some automation.

Just to give you a little synopsis to the situation, I have recently joined this Microsoft products oriented organization after sticking to Linux based one after two years. Had it been a Linux Server, things would have been easier and better optimized, but with only 4 days of Microsoft experience after long time, I expect the code to be little buggy, though I tested it on 3 different machines, all of them running on Windows 8.1, Microsoft Outlook/Excel 2007. Also, I’m not a professional programmer, rather I’ve been in the vicinity of those creepy folks therefore I won’t be responsible if anything goes wrong (though there are least of such chances).The inspiration and the basis was taken from this site however, I found some presentation issues and scattered information across different pages and this I ended up writing this blog to put everything at simple point. Let’s Start…

The Excel File

Our Excel was something like that:

Figure 1 – Excel File Example

Notice the duplicate entries of emails in cells B2 and B4. The recipients shouldn’t receive multiple emails for the same content and therefore, we will be handling this duplication as well. You can use any other table however, to stay consistent to the VB Macro (code that will make it happen), you should use the emails in Column 2, or modify the macro according to your requirement.

Prerequisites

Windows 7 or 8, MS Outlook 2007 (or above), MS Excel 2007 (or above), Good enough Processor & RAM to handle 200 Outlook Drafts (Tested smoothly on Core i5 + 4GB), little programming knowledge & some HTML concepts.

Step – 1: Enable Developer Mode in MS Excel

By default the Developer tab which holds the option for running, creating and editing macros is turned off in the Ribbon.

Excel 2007

1. Open the Excel file you have data in.

2. Click on the Office logo in the left top and choose Excel Options.

3. In the Popular section, enable the option: Show Developer tab in the Ribbon.

4. Press OK to close the open dialog.

Figure 2 – Excel Ribbon Menu

Figure 3 – Excel Options

Step 2: Enable Macros in Excel

Macros are by default turned off in Excel for security reasons. You will need to turn them on in order to use them here in this case.

To turn on the Macros, go to the same Excel Options as indicated in Figure 3, however, now you have to select Trust Center option instead of default Popular option, as shown in Figure 4. Press Trust Center Settings on bottom right corner.

Figure 4 – Trust Settings Center Button

Clicking on this button will open new Macro Options window. Select “Enable All Macros” and “Trust Access to the VBA Project Object Model”, as indicated in Figure 5.

Figure 5 – Enabling Excel Macro

Step – 3: Open Excel Macro Window

This step can be the most confusing one while working on this project. As a matter of fact, this is the core part of all the procedure. In order to create a Standard Macro, you will have to open the Macro Interface. Simply go to the Developer tab that you enabled in Step 2 and click on Visual Basic button, as indicated in Figure 6.

Figure 6 – Opening Visual Basic Macro Editor

Macro interface will open once you press the button. The window will be something like one shown below:

Figure 7 – VBA Macro Interface

Don’t be afraid or overwhelmed with the window as your main concern will be with the highlighted in red section.

Step – 4: Enabling Strict Variable Declaration

In order to avoid unnecessary errors and memory leakage, you will have to enable this option. You can do it by selecting “This Workbook on top right section, and then going to Tools top bar menu and then check the option of Required Variable Declaration, as indicated in Figure 8.

Figure 8 – VB Macro Editor Options

Then:

Figure – 9: Enabling Require Variable Declaration

Select OK and go back to VB Editor.

Step – 5: Inserting the Macro VB Code

Double click on “This Workbook” option to open VB Editor. Go to the end of page (end of anything you see there) and insert this code:

Sub Pass_Variables()

Dim OutApp As Object

Dim OutMail As Object

Dim rng As Range

Dim Ash As Worksheet

Dim Cws As Worksheet

Dim Rcount As Long

Dim Rnum As Long

Dim FilterRange As Range

Dim FieldNum As Integer

On Error GoTo cleanup

Set OutApp = CreateObject(“Outlook.Application”)

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

‘Set filter sheet, you can also use Sheets(“MySheet”)

Set Ash = ActiveSheet

‘Set filter range and filter column (column with e-mail addresses)

Set FilterRange = Ash.Range(“A1:H” & Ash.Rows.Count)

FieldNum = 2 ‘Filter column = B because the filter range start in column A

‘Add a worksheet for the unique list and copy the unique list in A1

Set Cws = Worksheets.Add

FilterRange.Columns(FieldNum).AdvancedFilter _

Action:=xlFilterCopy, _

CopyToRange:=Cws.Range(“A1”), _

CriteriaRange:=””, Unique:=True

‘Count of the unique values + the header cell

Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))

‘If there are unique values start the loop

If Rcount >= 2 Then

For Rnum = 2 To Rcount

‘Filter the FilterRange on the FieldNum column

FilterRange.AutoFilter Field:=FieldNum, _

Criteria1:=Cws.Cells(Rnum, 1).Value

‘If the unique value is a mail addres create a mail

If Cws.Cells(Rnum, 1).Value Like “?*@?*.?*” Then

With Ash.AutoFilter.Range

On Error Resume Next

Set rng = .SpecialCells(xlCellTypeVisible)

On Error GoTo 0

End With

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

Dim j As String

j = RangetoHTML(rng)

Dim k As String

k = “<p>” + “<strong>” + “<strong>” + “Best Regards,” + “</strong>” + “</p>” + “<p>” + “<strong>” + “</strong>” + “</p>” + “<p>” + “<strong>” + “ABC Company” + “</strong>” + “</p>”

.to = Cws.Cells(Rnum, 1).Value

.Subject = “Your Promo Code”

.HTMLBody = j + k

.Display ‘Or use Send

End With

On Error GoTo 0

Set OutMail = Nothing

End If

‘Close AutoFilter

Ash.AutoFilterMode = False

Next Rnum

End If

cleanup:

Set OutApp = Nothing

Application.DisplayAlerts = False

Cws.Delete

Application.DisplayAlerts = True

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

End Sub

Function RangetoHTML(rng As Range)

‘ Changed by Ron de Bruin 28-Oct-2006

‘ Working in Office 2000-2013

Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook

TempFile = Environ$(“temp”) & “/” & Format(Now, “dd-mm-yy h-mm-ss”) & “.htm”

‘Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With

‘Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With

‘Read all data from the htm file into RangetoHTML

Set fso = CreateObject(“Scripting.FileSystemObject”)

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.ReadAll

ts.Close

RangetoHTML = Replace(RangetoHTML, “align=center x:publishsource=”, _

“align=left x:publishsource=”)

‘Close TempWB

TempWB.Close savechanges:=False

‘Delete the htm file we used in this function

Kill TempFile

Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

Step – 6: Running The Macro

You can press F5 or the Play Button on top bar as indicated in Figure 10.

Figure 10 – Running VB Macro

The Macro will open three Outlook Draft emails, as there are total of 3 unique emails addresses. One address is repeated and thus the details attached to this one will be sent in single email, as indicated in Figure 11.

Figure 11 – Opening Multiple Outlook Drafts

And now you can just click on Send Button instead of copy pasting and inserting unique code manually on every window.

Outlook Limitations

Outlook can accommodate no more than 29 Kb of data in each of “To, CC or BCC”. Thus, if you save your contacts with just name, email and promo code of more or less equal length as mine, you will be able to send somewhere around 120-140 contacts that email. You will have to modify your Excel to send the emails to remaining contacts.