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
-
Open the Excel file you have data in.
-
Click on the Office logo in the left top and choose Excel Options.
-
In the Popular section, enable the option: Show Developer tab in the Ribbon.
-
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.