| 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 11comments |
|
Page [ 1 2 Next >> ] | |
|
|
Sue Mosher
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). |
|
|
|
Ray Capek
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? |
|
|
|
Sue Mosher
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. |
|
|
|
Ray Capek
28-Nov-2007 16:40
Thanks! I got the "send" part after I posted and it works great. |
|
|
|
Ven
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. |
|
|
|
Sue Mosher
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. |
|
|
|
Ven
25-Feb-2008 16:41
Thanks! Appreciate the help. |
|
|
|
Andy Kakuris
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 |
|
|
|
Sue Mosher
28-Mar-2008 14:51
Andy, there are CC and Bcc properties that work just like the To property shown in the sample. |
|
|
|
Andy Kakuris
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 2 Next >> ] | |
| Post your comment name email  |