Personalized email from recipient list of Excel

1. Draft an email in Outlook, set up the greetings of the email:
    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()
    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
    End If
       
   
    '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.
    With fd

        '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
            Next vrtSelectedItem
        Else
        End If
    End With

    '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
        Sheets(worksheet_name).Select
         
        For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeVisible)
            If cell.Value Like "*@*" Then
                Set MyItem = oOutlookApp.CreateItemFromTemplate(filepath)
                With MyItem
                    'MsgBox (CStr(i) & " " & cell.Value)
                    .To = cell.Value
                    .HTMLBody = Replace(.HTMLBody, "Client_Name", Cells(i, 1))
                    '.Subject = "Testing of Personalized Email"
                    .Send
                    i = i + 1
                End With
            End If
        Next
    End If
   
    Set MyItem = Nothing
    Set myOlApp = Nothing
End Sub

 , save this script and close coding window.

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.