Dear Client_Name, ..............................
2. Save email as template, in Save As window, select Outlook Template (*.oft) in Save as type drop down list, because the default saving path could be located in the Outlook system folder, so change the path you want to place the file.
3. Edit recipient list in Excel, column A is recipient's name, and column B is recipient's e-mail address. Edit worksheet name, this step is optional as long as you can identify the purpose of it, because later you will need to type which worksheet name you want to use when you execute the script, also, I want to reuse this Excel to send personalized email, which it means more and more worksheets will be created in the Excel, so normally I will give a meaningful name (e.g. Event_Date) for worksheet to trace back, besides I will create a testing list before send out the real list.
4. Type VB script in the Excel, switch to Developer tab in the top area of Excel, if you don't have the tab, please refer to the following steps:
For Excel 2007, Excel Options → click Show Developer tab in the Ribbon
For Excel 2010, Excel Options → Customize Ribbon → choose Main tabs in Customize the Ribbon drop down list → tick Developer
, click Visual Basic button in Developer tab, expand VBAProject (Excel_File_Name) → expand Microsoft Excel Objects → double-click ThisWorkbook, type script as follows:
Public Sub PersonalizedEmail(), save this script and close coding window.
Dim myOlApp As Outlook.Application
Dim MyItem As Outlook.MailItem
Dim EmailAddr As String
Dim i As Integer
Dim fd As Office.FileDialog
Dim filepath As String
i = 2
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Declare a variable to contain the path of each selected item
'Even though the path is a String, but the variable must be a Variant
'Because For Each...Next loops only works with Variants and Objects.
Dim vrtSelectedItem As Variant
'Use a With...End With block to reference the FileDialog object.
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
'Through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems
'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
'MsgBox "The path is: " & vrtSelectedItem
filepath = vrtSelectedItem
'Set the object variable to Nothing.
Set fd = Nothing
worksheet_name = InputBox(Prompt:="Please input the worksheet name of email list.", Title:="INPUT WORKSHEET NAME", Default:="")
If worksheet_name <> "" Then
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeVisible)
If cell.Value Like "*@*" Then
Set MyItem = oOutlookApp.CreateItemFromTemplate(filepath)
'MsgBox (CStr(i) & " " & cell.Value)
.To = cell.Value
.HTMLBody = Replace(.HTMLBody, "Client_Name", Cells(i, 1))
'.Subject = "Testing of Personalized Email"
i = i + 1
Set MyItem = Nothing
Set myOlApp = Nothing
5. Testing personalized email, create a test recipient list in a new created worksheet, assume worksheet's name is TestingList. Refer to the following steps:
click Macros button in Developer tab
→ click Run button
→ choose Outlook template file
→ type target worksheet name (e.g. TestingList)
→ open Outlook to check testing e-mails in Sent Items folder.