Simplified Outlook Mail Merge


 
Last week while reading Quora, I came across an interesting Outlook question from Siddharth Sreenivas. His question was, “Are there any free applications that let you mail merge with unique attachments on each mail?”. There are a number of third party mail merge tools, some of which (e.g. MAPILab’s Mail Merge Toolkit) do add this capability. But, none of the ones I know of are free. That’s not to say that they’re expensive, a single user license of the MAPILab product is just $24, but I don’t know of any that are free. My job doesn’t call for me to use Outlook’s mail merge very often, I probably haven’t done one in 5-6 years, but I remember that the process seemed a little complicated and thinking that there ought to be a simpler approach. Another disadvantage of Outlook’s built-in mail merge is that you can’t save and reuse it. That would be really handy if you need to do the same merge periodically. Writing a mail merge script sounded like an interesting challenge, so I responded to Siddharth and offered to help him out.

The solution I’ve put together solves the problems I mentioned above. It’s simple, flexible, and enables you to reuse a merge. Since it’s a scripted solution, it’s also extensible. The solution is made up of four components.

  1. A message template. The template is nothing more than a message created and saved to your Drafts folder. The message will have a subject, a body, and, optionally, attachments that will go to everyone you send the message to. Don’t worry, I’ve also devised a way to add unique attachments on a per recipient basis.
  2. An Excel spreadsheet. The spreadsheet will contain the list of recipients, one recipient per line, the message is to go to. The only required field for each recipient is an address. Other fields (e.g. the attachment file names) are optional. You can have as many additional fields as desired and each field can be used in the mail merge.
  3. A merge definition. Rather than employing a wizard, we’ll enter the merge details into an Outlook post. The definition will include details like the path to the spreadsheet containing the merge data, and descriptions of the text substitutions it will use.
  4. The script. The script reads the merge definition, then processes the merge accordingly.

Instructions.

Follow these instructions to add the code to Outlook.

The code comes in two parts. This is part 1.

  1. Start Outlook
  2. Press ALT + F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. Right-click on Class Modules, select InsertClass Module
  5. In the Properties panel click on Name and enter clsMailMerge
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Edit the code as needed. I included a comment where changes are needed or allowed.
  8. Click the diskette icon on the toolbar to save the changes
Const CLASS_NAME = "Mail Merge (v1)"

Private olkTmpl As Outlook.MailItem, _
        dicRepl As Object, _
        strBook As String, _
        strSheet As String, _
        strAddress As String, _
        strFolder As String, _
        strAttachment As String, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        lngStart As Long, _
        lngCount As Long

Private Sub Class_Initialize()
    Set dicRepl = CreateObject("Scripting.Dictionary")
    lngStart = 2
End Sub

Private Sub Class_Terminate()
    Set dicRepl = Nothing
End Sub

Public Sub Merge(Item As Outlook.PostItem)
    GetParameters Item
    ConnectToExcel
    ExecuteMerge
    DisconnectFromExcel
    MsgBox "Merge operation complete.  I processed and sent a total of " & lngCount & " messages.", vbInformation + vbOKOnly, CLASS_NAME End Sub

Private Sub GetParameters(olkMer As Outlook.PostItem)
    Dim arrLines As Variant, _
        varLine As Variant, _
        arrParam As Variant, _
        arrRepl As Variant
    arrLines = Split(olkMer.Body, vbCrLf)
    For Each varLine In arrLines
        If (Left(varLine, 1) = "#") Or (varLine = "") Then
            'Ignore the line
        Else
            arrParam = Split(varLine, "=")
            Select Case LCase(arrParam(0))
                Case "template"
                    Set olkTmpl = Session.GetDefaultFolder(olFolderDrafts).Items.Find("[Subject] = '" & arrParam(1) & "'")
                Case "filename"
                    strBook = arrParam(1)
                Case "sheet"
                    strSheet = arrParam(1)
                Case "startingrow"
                    lngStart = arrParam(1)
                Case "addresses"
                    strAddress = arrParam(1)
                Case "attachmentfolder"
                    strFolder = arrParam(1)
                    If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
                Case "attachments"
                    strAttachment = arrParam(1)
                Case "replace"
                    arrRepl = Split(arrParam(1), ",")
                    dicRepl.Add arrRepl(0), arrRepl(1)
            End Select
        End If
    Next
    If lngStart = 0 Then lngStart = 2
End Sub

Private Sub ConnectToExcel()
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Open(strBook)
    Set excWks = excWkb.Worksheets(strSheet) End Sub

Private Sub ExecuteMerge()
    Dim olkMsg As Outlook.MailItem, _
        objReg As Object, _
        objHits As Object, _
        lngRow As Long, _
        strReplacement As String, _
        arrAttachments As Variant, _
        varAttachment As Variant, _
        varHit As Variant
    Set objReg = CreateObject("VBScript.RegExp")
    For lngRow = lngStart To excWks.UsedRange.Rows.Count
        Set olkMsg = olkTmpl.Copy
        With objReg
            .Global = True
            .IgnoreCase = False
            .Pattern = "\|(.*?)\|"
            Set objHits = .Execute(olkTmpl.HTMLBody)
        End With
        With olkMsg
            .Recipients.Add excWks.Cells(lngRow, strAddress)
            For Each varHit In objHits
                Select Case varHit
                    Case "|DATE|"
                        strReplacement = Date
                    Case "|TIME|"
                        strReplacement = Time
                    Case Else
                        strReplacement = excWks.Cells(lngRow, dicRepl.Item(varHit.Value))
                End Select
                .HTMLBody = Replace(.HTMLBody, varHit.Value, strReplacement)
            Next
            arrAttachments = Split(excWks.Cells(lngRow, strAttachment), ",")
            For Each varAttachment In arrAttachments
                .Attachments.Add IIf(strFolder = "", varAttachment, strFolder & varAttachment)
            Next
        End With
        olkMsg.Send
        lngCount = lngCount + 1
    Next
    Set olkMsg = Nothing
    Set objReg = Nothing
    Set objHits = Nothing
End Sub

Private Sub DisconnectFromExcel()
    Set excWks = Nothing
    excWkb.Close False
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Part 2.

  1. Start Outlook
  2. Press ALT + F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. If not already expanded, expand Modules
  5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting InsertModule.
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Click the diskette icon on the toolbar to save the changes
  8. Close the VB Editor
Sub RunMailMerge()
    Dim objMM As New clsMailMerge
    objMM.Merge Application.ActiveExplorer.Selection(1)
    Set objMM = Nothing
End Sub

Sub NewMailMerge()
    Dim olkPost As Outlook.PostItem
    Set olkPost = Application.CreateItem(olPostItem)
    With olkPost
        .BodyFormat = olFormatPlain
        .Body = Replace(GetText(), "<br>", vbCrLf)
        .Display
    End With
End Sub

Function GetText() As String
    GetText = "# Blank lines and any line beginning with a pound-sign are ignored<br>"
    GetText = GetText & "<br>"
    GetText = GetText & "# Remove unnecessary parameters.<br>"
    GetText = GetText & "<br>"
    GetText = GetText & "# The subject line of the message being used as the merge template.  Required parameter.<br>"
    GetText = GetText & "Template=Merge Template #1<br>"
    GetText = GetText & "<br>"
    GetText = GetText & "# The full path to the Excel workbook containing the merge information.  Required parameter.<br>"
    GetText = GetText & "Filename=c:\Users\David\Documents\MailMerge.xlsx<br>"
    GetText = GetText & "<br>"
    GetText = GetText & "# The name of the sheet within the workbook containing the merge information.  Required parameter.<br>"
    GetText = GetText & "Sheet=Sheet1<br>"
    GetText = GetText & "<br>"
    GetText = GetText & "# The starting data row on the spreadsheet.  Optional parameter.<br>"
    GetText = GetText & "# If the parameter is left off, then the process assumes the data begins on row 2.<br>"
    GetText = GetText & "StartingRow=2<br>"
    GetText = GetText & "<br>"
    GetText = GetText & "# The letter of the spreadsheet column containing the addresses.  Required parameter.<br>"
    GetText = GetText & "Addresses=a<br>"
    GetText = GetText & "<br>"
    GetText = GetText & "# If the attachments are all in the same folder, then this parameter specifies the path to that folder.  Optional parameter.<br>"
    GetText = GetText & "AttachmentFolder=c:\Users\David\Documents\TestArea<br>"
    GetText = GetText & "<br>"
    GetText = GetText & "# The letter of the spreadsheet column containing the a list of attachments.  The list is comma delimited.  Optional parameter.<br>"
    GetText = GetText & "# If the Attachment folder parameter is filled in, then each entry will be a simple filename.  The code will prepend the AttachmentFolder path to each filename.<br>"
    GetText = GetText & "# If the AttachmentFolder parameter is not used, then each filename must include the full path.<br>"
    GetText = GetText & "Attachments=d<br>"
    GetText = GetText & "<br>"
    GetText = GetText & "# Each Replace parameter defines two values: text to find, and text to replace the found text with.  Optional parameter.<br>"
    GetText = GetText & "# The text to find must be unique and must be enclosed in vertical bars (i.e. the | character).  The format of the parameter is<br>"
    GetText = GetText & "#     |TEXT TO FIND|,letter of the spreadsheet column containing the replacement text<br>"
    GetText = GetText & "# Note that there is no space after the comma.<br>"
    GetText = GetText & "Replace=|FIRST|,B<br>"
    GetText = GetText & "Replace=|LAST|,C<br>"
End Function

Using the Solution.

Merging is a four-step process: prepare the spreadsheet, create a template, create a merge definition, run the merge.

Preparing the Spreadsheet

The spreadsheet contains the data the script will merge into the template. The only required field (column) is the address. All others are purely optional. The merge process will only pay attention to columns that you define in the merge definition. In other words, you can have fields in the spreadsheet that are not used in the merge. Fields can be in any order. The spreadsheet must be laid out so there is one recipient per row.

Attachment handling. Attachments will be entered as a comma-separated list of file names. If all the attachment files are in the same folder, then you can use the AttachmentFolder parameter in the merge definition and enter the file names in the spreadsheet as names alone. For example, “File1.doc,File2.doc”. If the attachment files are not all in the same folder, then each file name must include the path to the file. Example, “C:\Projects\File1.doc”,”C:\Projects\Project Alpha\File2.doc” (without the quotes).

Creating a Template

The first step in creating a template is to create a new message. Next, add a subject line. Now, enter the body of the message. Enter merge fields (i.e. the text you want to replace with data from the spreadsheet) by enclosing some unique bit of text between vertical bars (i.e. the | character). I also strongly recommend using all caps for the merge field names. It makes them easy to pick out should you need to edit the message. Say for example that you want each message to greet the recipient by name. In that case the body might start like this

Good morning, |FIRSTNAME|.

I’m writing you today to …

Perhaps in addition to that you need to mention their account number. If so, then the body might look like this

Good morning, |FIRSTNAME|.

I’m writing you today about account #|ACCOUNTNUMBER|.

Just remember that the merge field names must be unique and enclosed in vertical bars. Once you’ve got the message typed out save it to your Drafts folder.

Creating a Merge Definition

Once the template is ready it’s time to create the merge definition. Run the macro NewMailMerge to do that. It will create a new post with a skeleton merge definition. All you have to do is fill in the blanks. The file includes a brief explanation of each parameter. Here’s a more detailed description of each parameter.

  • Template. Required parameter. The name (i.e. subject line) of the merge template. The merge template is the message you created in your Drafts folder that contains the message you want to merge the data into and send. Example: You created a message with a subject of “Account Update”. This parameter would then be “Account Update” (without the quotes).
  • Filename. Required parameter. The full path to the Excel spreadsheet containing the data you want to merge into the messages. Example: The spreadsheet is named “Account Update.xlsx” and is in your “My Documents” folder. This parameter would then be “C:\Users\\Documents\Account Update.xlsx” (without the quotes), where “” is your username.
  • Sheet. Required parameter. The name of the sheet in the Excel spreadsheet that contains your merge data. Example: The data is on “Sheet1”. This parameter would be “Sheet1” (without the quotes).
  • StartingRow. Optional parameter. The row number of the first line of data on the sheet your data is on. Example: The first two lines of the sheet are a header with the data beginning on the third line. The parameter would then be “3” (without the quotes). If the parameter is omitted, then the default value is 2 (assumes that the sheet’s first line is a header).
  • Addresses. Required parameter. The letter corresponding to the column in the spreadsheet that contains the addresses the messages will be sent to. Example: The addresses are in the second column. The parameter would then be “B” (without the quotes).
  • AttachmentFolder. Optional parameter. The path to a folder that contains all the attachments to be used by this merge. If this parameter is present, then the script will look in this folder for all attachments. If this parameter is not filled in, then the script assumes that the names of the attachments specified in the Attachments parameter are complete path names. Example: You are sending account details to a list of customers. All the detail files are stored in the folder “C:\Details”. This parameter would then be “C:\Details” (without the quotes). If the attachments are not all in the same folder, then remove this parameter by deleting or commenting out the line it’s on.
  • Attachments. Optional parameter. The letter corresponding to the column in the spreadsheet that contains a comma-separated list of files to attach for each recipient. Example: The list of attachments for each recipient is in the third column of the spreadsheet. This parameter would then be “C” (without the quotes).
  • Replace. Optional parameter. This is the only parameter that can be repeated (i.e. there can be multiple Replace parameters in a given merge definition). Each Replace parameter defines two values: the text to find, and text to replace it with. The text to find will always be a merge field (i.e. a unique name enclosed in vertical bars). The replacement text will always be a letter corresponding to a column in the spreadsheet that contains the data to merge (i.e. insert into) the message. Example: Your spreadsheet has the recipient’s first name in the second column of the spreadsheet and you’ve entered a merge field of “|FIRSTNAME|” in your template. This parameter would then be “Replace=|FIRSTNAME|,B” (without the quotes).

I’ve included two additional merge fields you can use in your templates. These are “|DATE|” and “|TIME|” (without the quotes) that insert the current date and time respectively. Do not add Replace parameters for them as they are both predefined.

Running the Merge

Once you’ve created the template and merge definition you’re ready to run the merge. To do that

  1. Select (i.e. click on) the merge definition.
  2. Run the RunMailMerge macro.

For each entry (i.e. row) in the spreadsheet the script will

  • Create a new message base on the template specified in the Template parameter
  • Address the message to the address in the column specified by the Addresses parameter
  • If the Attachments parameter is specified, attach the files contained in the spreadsheet column listed in the Attachments parameter.
  • Perform a search and replace for each Replace parameter. The script will find the text specified in the first part of the parameter and replace it with the text in the spreadsheet column denoted in the second part of the parameter
  • Send the message

Examples

You work for a financial institution and need to send a list of customers a monthly email with their account balance. The message will include their account number, first and last names, balance, and today’s date. Here’s what the spreadsheet looks like.

Your first step is to create a message template. Here’s what you’ve come up with.

Next, you create a merge definition. Based on the spreadsheet and the template, here’s what your definition looks like.

Finally, you run the RunMailMerge script which produces and sends two messages. Here’s what they would look like.


Advertisements

76 comments on “Simplified Outlook Mail Merge

  1. I am trying to recreate a macro that a colleague before me created and I came across this blog. The Bonnie that you worked with is the same one that created my macro and I was hoping you could help me out like you helped her. I tried to recreate her steps, but I am getting the run-time 1004 application-defined or object-defined error. Debugging highlights this: objMM.Merge Application.ActiveExplorer.Selection(1)

    • Hi, Matthew.

      The error suggests a problem with the merge definition. If you’ll send me the definition, I’ll take a look at it and see if I can see anything wrong.

  2. Hi David,

    I get an error on MsgBox “Merge operation complete. I processed and sent a total of ” & lngCount & ” messages.”, vbInformation + vbOKOnly, CLASS_NAME End Sub

    can you help?

    • Kelly,

      The “End Sub” should be on the next line, not the same line as the rest of the code on that line.

    • David,
      I get another error –“Run-time error ’13’: type mismatch and it highlights on objMM.Merge Application.ActiveExplorer.Selection(1)

    • Hi, Kelly.

      The error suggests that you didn’t have a merge definition selected when you ran the merge.

    • David,

      I think so. I tried running the macro with closing the merge definition and have it open. I follow your steps like you have it above: I set up the excel file, email template, and merge definition.

      Thanks.

    • Hi, Kelly.

      It doesn’t matter whether the merge definition is open or closed, it must be selected in Outlook. In this context “selected” means that it must the highlighted item in the folder it’s in. If you had the definition selected and still got this error, then can you share the merge definition with me? If you can, then I’ll email you and you can reply back and attach the definition.

  3. David,

    I received an error in Public Sub Merge (Item As Outlook.PostItem)
    MsgBox “Merge operation complete. I processed and sent a total of ” & lngCount & ” messages.”, vbInformation + vbOKOnly, CLASS_NAME End Sub.

    Can you help?

  4. Hi David. Thank you so much for sharing your solution. I’m encountering Run-time error 91: Object variable or With block variable not set. Any guidance is greatly appreciated.

  5. Hi David
    Is it possible to add code that will open a specific Excel sheet (GraphRef) based on a reference in the Excel spreadsheet containing the merge data, highlight and copy a certain predefined area, close GraphRef, and then paste the copied content as a Bitmap into the message body at a location defined by the Merge Definition?

    I would like to include a graphic summary of the “to be attached” reports in the actual body of the email message?

    Regards
    Graeme

    • Hi, Graeme.

      Possibly. I’ll have to figure out how to insert a graphic at a given location.

    • Hi David

      If I have a “LocationID” such as “BitmapLoc” imbedded in message body at the location when the Bitmap should be pasted, is it possible to position and paste the copied content as a Bitmap there.

      Regards
      Graeme

    • Hi, Graeme.

      I have a working solution for copying an image from Excel and pasting it into a message in place of some static text. I’ll need to see about integrating it into the mail merge code.

    • Hi David

      Thanks for the update. Looking forward to receiving your integrated solution.

      Regards
      Graeme

    • Hi David
      Have you managed to integrate your working solution, for copying an image from Excel into a message, into the mail merge code?
      Regards
      Graeme

    • Hi, Graeme.

      It turns out that there is a limit on the number of open calendars. Please see this page for details. If the solution described on that page doesn’t solve the problem, then I’ll see about writing a script to open a set of folders.

      “Have you managed to integrate your working solution, for copying an image from Excel into a message, into the mail merge code?”

      No, still haven’t gotten to that yet.

  6. Hi David,

    Thank you for the code. Much appreciated!! I am facing the same problem as others are. When I execute the code, its showing a debug error at objMM.Merge Application.ActiveExplorer.Selection(1). I am using outlook 2013.

    • Hi, Yogesh.

      Most of the problems are the result of having spaces before/after the equals sign in the merge definition. Please check to see if this is true in your merge definition. If it is, then please remove those spaces.

  7. David,

    Before I start, many thanks for sharing this code. I am trying to get it working with Outlook 2013 and have hit a few snags. First off, there’s an issue with the code picking up extra spaces either side of the equals in the module string definitions – you either have to enforce a strict ‘no spaces’ policy eg: GetText = GetText & “Sheet = Sheet1” must become GetText = GetText & “Sheet=Sheet1”, or modify the case statement with a few Trims:

    Select Case Trim(LCase(arrParam(0)))
                    Case "template"
                        Set olkTmpl = Session.GetDefaultFolder(olFolderDrafts).Items.Find("[Subject] = '" &amp; arrParam(1) &amp; "'")
                    Case "filename"
                        strBook = Trim(arrParam(1))
                    Case "sheet"
                        strSheet = Trim(arrParam(1))
                    Case "startingrow"
                        lngStart = Trim(arrParam(1))
                    Case "addresses"
                        strAddress = Trim(arrParam(1))
                    Case "attachmentfolder"
                        strFolder = Trim(arrParam(1))
                        If Right(strFolder, 1)  "\" Then strFolder = strFolder &amp; "\"
                    Case "attachments"
                        strAttachment = Trim(arrParam(1))
                    Case "replace"
                        arrRepl = Split(arrParam(1), ",")
                        dicRepl.Add arrRepl(0), arrRepl(1)
                End Select
    

    That fixes the subscript out of range error when the code tries to select a workbook called “workbookname”

    Having got past that issue, I am hitting “Runtime error ‘-2xxxx (800004005) This method cannot be used with an inline response mail form” at Set olkMsg = olkTmpl.Copy (or thereabouts).

    Any ideas?

    • Gah – my post got nobbled due to use of angled brackets – second from last paragraph should read…

      That fixes the subscript out of range error when the code tries to select a workbook called “-space-workbookname”

    • Hi, Nigel.

      You’re right, I need to add those trims into the code.

      I’ve never seen that error before. The inline response is a new object Microsoft added in 2013. If you respond to a message from the reading pane, then the inline response contains that reply. The code does not reference the inline response object anywhere, so I’m at a bit of a loss to understand how it can be an issue. Did you perhaps edit the message template in the reading pane?

  8. Hi David,

    This seems like an awesome macro.I seem to get the same error as Bonnie.

    I get an error “Subscript out of range”
    Debug highlighting this line -> objMM.Merge Application.ActiveExplorer.Selection(1)

    After I posted the Merge definitions to my inbox. I click on it from my inbox (not opening it) and run the mail merge and I get the error above. Any tips or suggestions?

    I am using Outlook/Excel 2013 on Windows 7.

    Thank you,
    Peter

    • Hi, Peter.

      My first guess is that something is not right in the merge definition. If you’ll send it to me, then I’ll take a look at it.

  9. When I open the merge definition post, hit Alt F11, Run Macro, choose RunMerge, I get a runtime error 13 “mismatch”. the debugger leads me to this line: objMM.Merge Application.ActiveExplorer.Selection(1). Any thoughts?

    • ok, I’m sorry, this must be over my head, I am not sure where to edit the script to select. I guess I need a Macro 101 class first 🙂 I will go back to Word Mail Merge, disconnect from network, move from outbox to drafts, then do whatever manual adjustments I need to from there. Thanks for your help. (BTW, also noticed instructions say to run RunMailMerge macro, but only ones that are available to select are RunMerge and NewMailMerge. NewMailMerge is the definition creating macro, so I selected RunMerge.)

    • Bonnie,

      I’ll be glad to work with you on this if you still want to have a go at it. It just takes getting used to. Thanks for pointing out the disconnect between the instructions and the actual macro name. I’ll go fix that now.

    • Well, I would certainly appreciate the help as the limitations of word mailmerge will still require some manual adjustments that should be able to be covered in a macro. Can you instruct how to change code? Would it be helpful to send word template and excel sheet? What is most efficient way to proceed?

    • Bonnie,

      I’ll change the code. Let’s start by determining where you stand.

      1. Do you have a spreadsheet with all the info required for the merge?
      2. Have you created the merge template message?
      3. Have you created the merge definition?
    • Bonnie,

      Can you share the merge template and definition with me? If you can, then I’ll email you and you can reply back with those items attached.

  10. Thanks for the prompt response. I am trying to send a personalized email with unique attachments to report owners – often 2 people. Then I need to CC 1 group email box address which is static and 3 levels of managers above the report owners, which are dynamic. I have all the emails in the excel sheet, so just need a way to include more than one cell to the To and CC fields (or I could concatenate the emails, but that doesn’t seem to work). I would also like to send the merged emails to a draft directory, instead of outbox, so they could be reviewed prior to sending.

    • Bonnie,

      So you don’t plan on using the recipient’s name in the message. You just want to send a given file to each set of recipients. Is that correct?

  11. This macro appears to be a beautiful thing and I am trying to fit it to my needs. I’m a beginner at macros, so having this much done for me is HUGEly helpful! One questions, how can I adapt the code to allow for multiple recipients in both To and CC address fields?

    • Hi, Bonnie.

      I’m not sure I understand. The point of a mail merge is to send a personalized message to each person. How would that work if there are multiple people on the “To” line of a single message?

    • Hi, Jayesh.

      Did you create a merge definition, save it, and then have it selected when you ran the merge?

  12. Hi David,
    I have gone over the process a number of times but still get a – objMM.Merge Application.ActiveExplorer.Selection(1) – Runtime error 13 type mismatch. Do you have any pointers? Is there a format I should follow in the spread sheet?

    • Hi, Todd.

      Based on the error message it sounds as if you don’t have the right item selected in Outlook when you run the merge. The item you need to have selected is an Outlook post containing your merge definition. I doubt that this has anything to do with the spreadsheet, but, to answer your question, the format of the sheet is up to you. As I note in the instructions, then only requirement is to have a column containing the addresses of the recipients. All other columns are optional. The columns can be in any order since the macro finds them by name or by explicit position. Of course, the field names you use in the merge template must match the column names used in the spreadsheet.

    • Hi David,
      The Template name Template = MonthlyAccountBalance is the same name as I have saved the email draft as the subject. I have tried selecting or opening the email draft and then running merge definition then add a subject name and run the RunMailMerge macro but still get the error. I commented out all the optional bits but still got the same error so don’t know where it is going wrong.

    • Hi, Todd.

      You need to select the post containing the merge definition, then run the macro.

    • Run-time error ’13’: Type mismatch
      When I press “Debug,” it highlights objMM.Merge Application.ActiveExplorer.Selection(1)

    • my apologies . . . i fixed that error. the original error is “Compile error: Sub or Function not defined”. Click OK and it highlights, “Private Sub Class_Initialize()”

    • laticca,

      Are you familiar with the debugger? If so, please step through the code and let me know if it goes into Class_Initialize and, if so, what line it stops on.

    • When I step into with the debugger, a pop-up window says, “Compile error: Sub or Function not defined.) then, it highlights the line of the Mail Merge code, “Private Sub Class_Initialize().” the line below that says,
      Set dicRepl = CreateObject(“Scripting.Dictionary”)
          lngStart = 2
      End Sub

  13. I copied the new code and created the merge definition again. This time when I ran the mail merge I got a compile error – syntax error. It identified line 30 column 145 as a problem – “End sub” was at the end of the line when it should be on the next line. The same was true for line 71 (line 72 after you fix the first End sub). After doing that and saving the code again and restarting Outlook, I then got the same error as before – Run-time error ’9′: Subscript out of range that when debugged highlighted the same code from line 3 of Module1: objMM.Merge Application.ActiveExplorer.Selection(1). It seems like we’re getting closer though. I wish I knew VBA better, I could look more closely at this code myself if I did. I appreciate all of your help!

    • baverypsd,

      Can you send me a copy of the merge definition? If you can, then I’ll send you an email and you can reply to it with the definition attached.

  14. I re-downloaded the code (I thought the web-encoded characters might be the issue). I was now able to make the mail merge definition, but where I tried to run the mail merge it gave the message “Run-time error ‘9’: Subscript out of range”. When I clicked on debug it highlighted the code from line 3 of Module1: objMM.Merge Application.ActiveExplorer.Selection(1). When I tried to run the merge I did have the post generated by the definition selected.

    • baverypsd,

      Unfortunately the debugger always highlights the line that called a procedure in a class module instead of highlighting the actual line in the class that caused the problem. I looked over the code and I think I see the problem. I made a change to correct it. Please download the code again and replace what you have with the new version.

    • I see the problem. Sometimes when I paste an article into the WordPress editor it converts certain symbols to their web equivalents. I’ve corrected those symbols back to what they should be. Please download the code again and give it a try.

  15. I tried following your directions but when I first ran the macro it threw an error saying that there was an expected expression. Can you help me with this? Feel free to contact me directly via e-mail if you like. I am trying to send a mail merge with unique attachments for each recipient, and like you I’m not interested in paying money for third-party software.

    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s