More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

share code 30-Sep-2014 10:50

Looking for help with Outlook programming projects — VSTO, add-ins, VBA, custom Outlook forms, etc.? You′ve come to the right place!

NEW! >> Subscribe to this site via RSS. For more RSS options, see the complete list of feeds on our main news page.

Login Password
Remember me
Register | Send my password
Code level: beginner    Code area: Basic Outlook Printer Friendly Version
Title: Cheapo mail merge
Description: This code sample was inspired by KC Lemson's blog entry at http://blogs.technet.com/kclemson/comments/404393.aspx. This version makes 3 improvements. It uses email addresses, not names (which are not a sure thing), to address the messages. It preserves the formatting in HTML messages. And it avoids Outlook security prompts (if you run it in Outlook 2003 VBA) by deriving all objects from the intrinsic Application object.
Date: 06-May-2005  09:47
Code level: beginner
Code area: Basic Outlook
Posted by: Sue Mosher
This message is displayed as VB.NET
 Public Sub KCsCheapoMailMergeII()

    Dim olExp As Outlook.Explorer
    Dim olItemTemplate As Outlook.MailItem
    Dim olItem As Outlook.MailItem
    Dim olRecip As Outlook.Recipient
    Dim strBody As String
   
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            If Application.ActiveExplorer.Selection.Item(1).Class = olMail Then
                Set olItemTemplate = Application.ActiveExplorer.Selection.Item(1)
            End If
        Case "Inspector"
            If Application.ActiveInspector.CurrentItem.Class = olMail Then
                Set olItemTemplate = Application.ActiveInspector.CurrentItem
            End If
    End Select
    
    If olItemTemplate.BodyFormat = olFormatHTML Then
        strBody = olItemTemplate.HTMLBody
    Else
        stroBody = olItemTemplate.Body
    End If
    
    For Each olRecip In olItemTemplate.Recipients
        Set olItem = Application.CreateItem(olMailItem)
        If olItemTemplate.BodyFormat = olFormatHTML Then
            olItem.BodyFormat = olFormatHTML
            olItem.HTMLBody = olItemTemplate.HTMLBody
        Else
            olItem.Body = olItemTemplate.Body
        End If
        olItem.Subject = olItemTemplate.Subject
        olItem.To = olRecip.Address
        olItem.Recipients.ResolveAll
        olItem.Display
    Next

    Set olRecip = Nothing
    Set olItem = Nothing
    Set olItemTemplate = Nothing
    Set Application = Nothing
End Sub
All 10comments
Page [ 1  
  06-May-2005  16:51   
Make that 4 improvements: This version works from either the selected message or an open message (so you don't have to save in Drafts if you don't want to).
  28-Nov-2007  14:40   
This is great - just what I needed, but how can I make it automatically send the emails? Is there a limit on the size of the distribution list?
  28-Nov-2007  14:57   
Ray, to send messages, replace .Display with .Send. If Outlook is set to "automatically send when connected," the messages should go out at soon as they're sent. Otherwise, your Outlook version is relevant.

No distribution list is involved. The code sends a message to each recipient in the original "template" message.
  28-Nov-2007  16:40   
Thanks! I got the "send" part after I posted and it works great.
  25-Feb-2008  16:02   
Hi Sue,

I use Outlook 2000 and am getting 438 error on lines 20 (If olItemTemplate.BodyFormat = olFormatHTML Then), 28 (If olItemTemplate.BodyFormat = olFormatHTML Then) and 29 (olItem.BodyFormat = olFormatHTML). Haven't been able to find anything on OL2000 help to indicate why. Can you help me understand?

Also, what changes would I need to make in order to pick recipients from a distribution list instead of listing all of them as recipients on the "template" message?

Thanks & appreciate the help.
  
  25-Feb-2008  16:28   
Ven, Outlook 2000 does not support the MailItem.BodyFormat property. You should always use and HTML-format message as the template and should simplify the code to skip the check for BodyFormat and just set the HTMLBody property every time.

To pick recipients from a distribution list, you would have to develop your own VBA userform to display the list members. Outlook does not offer any built-in user interface you can borrow for a code project.
  25-Feb-2008  16:41   
Thanks! Appreciate the help.
  28-Mar-2008  14:48   
Is there a way to add cc or bcc? We're sending messages to employees and want to cc their supervisors?

Thanks
  28-Mar-2008  14:51   
Andy, there are CC and Bcc properties that work just like the To property shown in the sample.
  28-Mar-2008  15:16   
Hi Sue,
Thanks! I'm new to programming in Outlook, lots of VBA in Excel and Access. These properties should be:
olItem.CC=olRecip.Address
olItem.Bcc=olRecip.Address
Thanks,
Andy
Page [ 1