Solved

Outlook and Word Macro to insert contact information into a word document or template

Posted on 2006-07-10
29
920 Views
Last Modified: 2011-11-02
I have a bunch of word documents that are templates that I use on a day to day basis in order to send letters to clients. I would like to be able to add a context menu inside of outlook that when I am on a contact, I can click open an existing word document, and have all of that person's contact information automatically be added to that word document in a certain place. Essientially, I am describing a mail merge, but in this case I am only talking about adding that one contact to the word document. I know there are ways to do this from word, but it takes a long time to go through the wizard. You seem to know your way around outlook pretty well, is there a way to accomplish what I am describing. And keep in mind that I have numerous templates I would like to apply this to.
 
0
Comment
Question by:ohioequities
  • 15
  • 14
29 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 17087183
Greetings, ohioequities.

I want to make sure I understand what you're looking for.  You start by selecting a contact in Outlook.  You then open a context menu and select a Word document (a template).  The Word document opens and is automatically filled in with certain information from the contact.  Do I have it right?  If so, does this have to work from a context menu or would a toolbar button be okay?  Second, how many different Word templates are we talking about?  Third, what contact fields need to be merged in?  Fourth, does this need to be extensible (i.e. do you need the ability to add fields as the need arrises)?  Fifith, are you expecting to use the mailmerge fields you typically set up in a Word document, or can we use a different means of inserting the replaceable text?

I can definitely do this, it's a question of how to approach it.  Your answers will help me figure that out.  One point, I'm not certain about adding a context menu.  If it's not possible is that a show stopper?

Cheers!
0
 

Author Comment

by:ohioequities
ID: 17089390
Okay, here is some clarification. You pretty much have the right idea.

First, I dont' need it in a context menu, a toobar menu is perfectly fine, infact, that's probably more ideal. So that sounds good.

Second, I would like the ability to be able to add more fields as the need arises. To start, I am expecting most of the normal fields you get during a mailmerge.

Third, it doesn't really matter to me the process by which the fields get inserted, as long as I know how to set up the template.

Lastly, I imagine I will have about 4 or 5 different word templates, and 1 or 2 email (outlook templates). The email templates will be similar to the word documents with the only addition being the person's email address being filled in the to line, and possibly a subject being filled in.

Let me know if you need any more information.

Thanks again,

Matt
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17108890
Hi, Matt.

Ok, here it is.  You already know how to add the code into Outlook.  To use this you'll need to set up a series of toolbar buttons, one for each template you want to use.  Each of those buttons will need to call a macro that looks like this:

    Sub MergeToTemplate1()
        MergeFromContact "C:\SomeFolder\Template1.doc"
    End Sub

In each template you can have as many replaceable fields as you want.  The replaceable text has to be in the form <<FIELDNAME>>.  The code replaces the text with the data from the selected contact via a search and replace.  I've set that process to be case sensitive, so the replaceable text has to be an exact match.  I put in replacement operations for what I figure are the most common fields.  You can add more as you want.  Each replacement operation is controlled by one of these lines:

    SearchAndReplace "<<TEXTTOREPLACE>>", .ContactField

You can also throw in replacements that aren't based on a contact field.  For example, if you wanted to include a notation at the bottom of a document indicating when it was created you could add a replacement operation that looked like this:

    SearchAndReplace "<<CREATED>>", "Created: " & Now()

Now is a VB function that returns the current date and time.  Notice that the replacement operations for addresses is slightly different.  that's because the address as it comes from Outlook has each line terminated by a carriage-return/line-feed pair.  In Word that leaves a symbol in front of the subsequent lines of the address.  I used the Replace function to swap that CrLf pair for a carriage-return by itself.


'CODE BEGINS HERE
Dim wrdSelection As Word.Selection

Sub MergeFromContact(strTemplate As String)
    Dim objTemp As Object, _
        olkContact As Outlook.ContactItem, _
        strMacroName As String, _
        wrdApp As Word.Application, _
        wrdDoc As Word.Document, _
        wrdRange As Word.Range
   
    'General preparation
    strMacroName = "Merge From Contact"
   
    'Check to see that we have a contact selected
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            Set objTemp = Application.ActiveExplorer.Selection(1)
        Case "Inspector"
            Set objTemp = Application.ActiveInspector.CurrentItem
        Case Else
            MsgBox "You must have a contact open or selected to use this macro.", vbCritical + vbOKOnly, strMacroName
            bolError = True
    End Select
    If objTemp.Class = olContact Then
        Set olkContact = objTemp
    Else
        MsgBox "A contact was not selected.  Operation aborted.", vbInformation + vbOKOnly, strMacroName
        bolError = True
    End If
    If Not bolError Then
   
        'Prep the Word document
        Set wrdApp = CreateObject("Word.Application")
        Set wrdDoc = wrdApp.Documents.Open(strTemplate)
        Set wrdRange = wrdApp.ActiveDocument.Range(0, wrdDoc.Characters.Count)
        wrdRange.Select
        Set wrdSelection = wrdApp.Selection
   
        'Find and replace the replaceable text
        With olkContact
            SearchAndReplace "<<BUSINESSADDRESS>>", Replace(.BusinessAddress, vbCrLf, vbCr)
            SearchAndReplace "<<BUSINESSFAXNUMBER>>", .BusinessFaxNumber
            SearchAndReplace "<<BUSINESSTELEPHONENUMBER>>", .BusinessTelephoneNumber
            SearchAndReplace "<<COMPANYNAME>>", .CompanyName
            SearchAndReplace "<<FIRSTNAME>>", .FirstName
            SearchAndReplace "<<FULLNAME>>", .FullName
            SearchAndReplace "<<HOMEADDRESS>>", Replace(.HomeAddress, vbCrLf, vbCr)
            SearchAndReplace "<<HOMEFAXNUMBER>>", .HomeFaxNumber
            SearchAndReplace "<<HOMETELEPHONENUMBER>>", .HomeTelephoneNumber
            SearchAndReplace "<<JOBTITLE>>", .JobTitle
            SearchAndReplace "<<LASTNAME>>", .LastName
            SearchAndReplace "<<MAILINGADDRESS>>", Replace(.MailingAddress, vbCrLf, vbCr)
            SearchAndReplace "<<MOBILETELEPHONENUMBER>>", .MobileTelephoneNumber
            SearchAndReplace "<<PRIMARYTELEPHONENUMBER>>", .PrimaryTelephoneNumber
            SearchAndReplace "<<SPOUSE>>", .Spouse
            SearchAndReplace "<<TITLE>>", .Title
        End With
   
        'Display the document
        wrdApp.Visible = True
       
    End If
   
    'Clean up
    Set olkContact = Nothing
    Set objTemp = Nothing
    Set wrdSelection = Nothing
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

Sub SearchAndReplace(strFind As String, strReplace As String)
    With wrdSelection.Find
        .Text = strFind
        .Replacement.Text = strReplace
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = True
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute Replace:=wdReplaceAll
    End With
End Sub
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17171782
Any progress, Matt?
0
 

Author Comment

by:ohioequities
ID: 17172335
Sorry, I've been extremely busy and haven't had a chance to try this out. Hopefully I will get to this later in the week.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17172654
That's okay.  I was just checking.
0
 

Author Comment

by:ohioequities
ID: 17244369
Okay, two questions. One, where do I put the code

Dim wrdSelection As Word.Selection

Sub MergeFromContact(strTemplate As String)

and

Sub SearchAndReplace(strFind As String, strReplace As String)

Do i put those in their own modules or do i put it in with the Sub MergeToTemplate1()
        MergeFromContact "C:\SomeFolder\Template1.doc"
    End Sub

Also, how should i go about making the word document?

Thanks.
 
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17247001
All of this code can go into its own module.  Create the Word doc as you would any other Word doc.  Insert the replaceable text wherever you want to.  You can also apply formatting (font, font size, bold, italics, etc.) to the replaceable text.  The replaced text will retain that formatting.
0
 

Author Comment

by:ohioequities
ID: 17247317
Okay, first of all, it doesn't like this line:

Dim wrdSelection As Word.Selection, it says that it can't go outside the sub.

Secondly it doesn't like this line:

strMacroName As String, _
        wrdApp As Word.Application

Do I have to make any changes to this script at all, like where the _ is, or should it be good to go?

Lastly, does this line of code go at the top of the module:

Sub MergeToTemplate1()
        MergeFromContact "C:\SomeFolder\Template1.doc"
    End Sub.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17248477
> Okay, first of all, it doesn't like this line:
Yes, it does have to go outside the sub, exactly as I posted it.  

> Secondly it doesn't like this line:
Change these three lines

        wrdApp As Word.Application, _
        wrdDoc As Word.Document, _
        wrdRange As Word.Range

to

        wrdApp As Object, _
        wrdDoc As Object, _
        wrdRange As Object

Everything else should be fine.

> Lastly, does this line of code go at the top of the module:
It doesn't matter where in the module it goes.  There's no required order that Subs and Functions have to appear in.  The only requirement is that thie line

    Dim wrdSelection As Word.Selection

be at the top and outside of Subs and Functions.
0
 

Author Comment

by:ohioequities
ID: 17249439
Okay, the error I'm am getting is this:

Compile error:

User defined type not defined

and it's highlight that top line  wrdSelection As Word.Selection
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17253605
Sorry, I missed that one.  Change this

    wrdSelection As Word.Selection

to

    wrdSelection As Object
0
 

Author Comment

by:ohioequities
ID: 17263316
Okay, now that I have changed that I get no errors and it opens up the word document. However it makes no changes in the word document.

For example, I have this in the word document:

Dear <<FIRSTNAME>>,

<<FULLNAME>>

<<BUSINESSADDRESS>>


this is just to test it and it doesn't fill anything in, it just leaves the <<FIRSTNAME>>, <<FULLNAME>>, <<BUSINESSADDRESS>>, ETC...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17267720
I've tested the macro here reapeatedly, including a couple of runs just now, and it never fails to work.  I assume there weren't any error messages.  If not, then we need to step through the process and see if we can figure out what's going on.  Select a contact, then press ALT+F11 to open the VB Editor.  Place the insertion point inside this block of code

    Sub MergeToTemplate1()
        MergeFromContact "C:\SomeFolder\Template1.doc"
    End Sub

Now, press F8.  This will turn the first line of code yellow.  Pressing F8 again will execute that line and move the highlight to the next line.  Keep pressing F8, some lines will take longer to execute than others.  What I need to know is whether all of the code executes.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:ohioequities
ID: 17271003
Okay, I did that. It appears as if all the lines executed just fine. It even opens up the word document at the end, but it doesn't replace any of the fields. What do you think I can try next? I will paste all of my code in here so that you can read it exactly the way i have it now:

Dim wrdSelection As Object
   
    Sub MergeToTemplate1()
        MergeFromContact "C:\Documents and Settings\mgregory\My Documents\Word Templates\Template1.doc"
    End Sub


Sub MergeFromContact(strTemplate As String)
    Dim objTemp As Object, _
        olkContact As Outlook.ContactItem, _
        strMacroName As String, _
        wrdApp As Object, _
        wrdDoc As Object, _
        wrdRange As Object
   
    'General preparation
    strMacroName = "Merge From Contact"
   
    'Check to see that we have a contact selected
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            Set objTemp = Application.ActiveExplorer.Selection(1)
        Case "Inspector"
            Set objTemp = Application.ActiveInspector.CurrentItem
        Case Else
            MsgBox "You must have a contact open or selected to use this macro.", vbCritical + vbOKOnly, strMacroName
            bolError = True
    End Select
    If objTemp.Class = olContact Then
        Set olkContact = objTemp
    Else
        MsgBox "A contact was not selected.  Operation aborted.", vbInformation + vbOKOnly, strMacroName
        bolError = True
    End If
    If Not bolError Then
   
        'Prep the Word document
        Set wrdApp = CreateObject("Word.Application")
        Set wrdDoc = wrdApp.Documents.Open(strTemplate)
        Set wrdRange = wrdApp.ActiveDocument.Range(0, wrdDoc.Characters.Count)
        wrdRange.Select
        Set wrdSelection = wrdApp.Selection
   
        'Find and replace the replaceable text
        With olkContact
            SearchAndReplace "<<BUSINESSADDRESS>>", Replace(.BusinessAddress, vbCrLf, vbCr)
            SearchAndReplace "<<BUSINESSFAXNUMBER>>", .BusinessFaxNumber
            SearchAndReplace "<<BUSINESSTELEPHONENUMBER>>", .BusinessTelephoneNumber
            SearchAndReplace "<<COMPANYNAME>>", .CompanyName
            SearchAndReplace "<<FIRSTNAME>>", .FirstName
            SearchAndReplace "<<FULLNAME>>", .FullName
            SearchAndReplace "<<HOMEADDRESS>>", Replace(.HomeAddress, vbCrLf, vbCr)
            SearchAndReplace "<<HOMEFAXNUMBER>>", .HomeFaxNumber
            SearchAndReplace "<<HOMETELEPHONENUMBER>>", .HomeTelephoneNumber
            SearchAndReplace "<<JOBTITLE>>", .JobTitle
            SearchAndReplace "<<LASTNAME>>", .LastName
            SearchAndReplace "<<MAILINGADDRESS>>", Replace(.MailingAddress, vbCrLf, vbCr)
            SearchAndReplace "<<MOBILETELEPHONENUMBER>>", .MobileTelephoneNumber
            SearchAndReplace "<<PRIMARYTELEPHONENUMBER>>", .PrimaryTelephoneNumber
            SearchAndReplace "<<SPOUSE>>", .Spouse
            SearchAndReplace "<<TITLE>>", .Title
        End With
   
        'Display the document
        wrdApp.Visible = True
       
    End If
   
    'Clean up
    Set olkContact = Nothing
    Set objTemp = Nothing
    Set wrdSelection = Nothing
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

Sub SearchAndReplace(strFind As String, strReplace As String)
    With wrdSelection.Find
        .Text = strFind
        .Replacement.Text = strReplace
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = True
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute Replace:=wdReplaceAll
    End With
End Sub

0
 
LVL 76

Expert Comment

by:David Lee
ID: 17275956
Hmmm.  Try adding these lines of code (below) at the top of the module immediately above

    Dim wrdSelection As Object



CONST wdFindContinue = 1
CONST wdReplaceAll = 2
0
 

Author Comment

by:ohioequities
ID: 17276299
Okay, great. That works perfectly. Thank you very much. Last question.

If I want to add an additional outlook field to my script and template, how will I know what the command is that I need to add. Is there a complete list of these anywhere?
 SearchAndReplace "<<BUSINESSADDRESS>>", Replace(.BusinessAddress, vbCrLf, vbCr)
            SearchAndReplace "<<BUSINESSFAXNUMBER>>", .BusinessFaxNumber
            SearchAndReplace "<<BUSINESSTELEPHONENUMBER>>", .BusinessTelephoneNumber
            SearchAndReplace "<<COMPANYNAME>>", .CompanyName
            SearchAndReplace "<<FIRSTNAME>>", .FirstName
            SearchAndReplace "<<FULLNAME>>", .FullName
            SearchAndReplace "<<HOMEADDRESS>>", Replace(.HomeAddress, vbCrLf, vbCr)
            SearchAndReplace "<<HOMEFAXNUMBER>>", .HomeFaxNumber
            SearchAndReplace "<<HOMETELEPHONENUMBER>>", .HomeTelephoneNumber
            SearchAndReplace "<<JOBTITLE>>", .JobTitle
            SearchAndReplace "<<LASTNAME>>", .LastName
            SearchAndReplace "<<MAILINGADDRESS>>", Replace(.MailingAddress, vbCrLf, vbCr)
            SearchAndReplace "<<MOBILETELEPHONENUMBER>>", .MobileTelephoneNumber
            SearchAndReplace "<<PRIMARYTELEPHONENUMBER>>", .PrimaryTelephoneNumber
            SearchAndReplace "<<SPOUSE>>", .Spouse
            SearchAndReplace "<<TITLE>>", .Title

Thanks for all your help.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 17277476
> Is there a complete list of these anywhere?
Yes, in several places.  First, and quickest, launch the VB Editor in Outlook (ALT+F11), then Click Help->Microsoft Visual Basic Help.  When the Help pane appears on the right expand Microsoft Outlook Visual Basic Reference->Objects->M and click on MailItem Object.  When the help window appears, click Properties at the top to see a list of the fields that make up a mail item object.  Second, you can find the same information you see in Outlook help at Microsoft's MSDN web site.  Here's a link to the same information on the MailItem object there: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaol11/html/olobjMailItem_HV05247822.asp?frame=true

You're welcome.  Sorry it took so long to get this working.
0
 

Author Comment

by:ohioequities
ID: 17278632
Okay, I see what your talking about, but I might not have asked the right question. Say I need to insert another field in the script, such as searchandreplace for <<EMAILADDRESS>> or <<WEBADDRESS>> or something like that. How can I find out what the .outlookfield is? I tried looking in the help but i couldn't find it.

I will award your well deserved points now. Thanks!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17283689
Everything between << and >> is a name I made up.  You can call those things anything you want.  You can even make them nonsensical if you want.  The key is what comes after that.  For example in this line

    SearchAndReplace "<<COMPANYNAME>>", .CompanyName

<<COMPANYNAME>> is a text string I decided to use.  I could just as easily have chosen <<BUSINESS>>, <<CMPNY>>, or even something pointless like <<DOG>>.  The Outlook value is the part of the line that comes after the comma.  In this case ".CompanyName".  CompanyName is the name of an Outlook property.  Of course that's the same name that appears inside the angle brackets, but it didn't have to be.  I simply mirrored the name for simplicities sake.  The format of that command line is

    SearchAndReplace "Text to Find", "Replacement Text"

In this case we're using the contents of an Outlook contact property as the replacement text.  We don't have to though.  You can use anything you want.  For example, the following command

    SearchAndReplace "<<TODAY>>", Date

This would replace all instances of the text string <<TODAY>> with the current date.    There's also no requirement that the replaceable text in the form "<<TEXT>>".  I chose that format because it's unlikely that such a text string would normally occur in a document.  This avoids unwanted replacements.  To illustrate what I mean, assume that instead of

    SearchAndRepalce "<<TITLE>>", .Title

I used

    SearchAndReplace "title", .Title

If the title in the contact was "Vice President, Marketing", the a sentence like this

    "The title to you car is in the mail."

would be transformed to

    "The Vice President, Marketing to your car is in the mail."

If the replaceable text isn't unique, then you're asking for problems.  
0
 

Author Comment

by:ohioequities
ID: 17284652
Yeah, I understand that.  That makes sense. What my question is, is where is there a list of outlook's fields. The examples you used were .CompanyName, and .Title, and throughout your code you wrote about 15 more. I'm assuming there is about 100 more possible fields that I could use, one being the email address field. Do you know where I could find a list of all the rest of outlook's contact fields, or did you just know them. Thanks.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17286534
Are you saying that when you follow the steps in my post from 08/09/2006 05:26AM EDT, that you don't see the property names?  What do you see when you get to this step, "When the help window appears, click Properties at the top to see a list of the fields that make up a mail item object."?
0
 

Author Comment

by:ohioequities
ID: 17288449
Correct, when I follow those steps I can't find the list of fields.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17292373
What happens when you click Properties on that page?
0
 

Author Comment

by:ohioequities
ID: 17295585
Okay, when I click on properties, i get a drop down of a ton of items starting with actions property. i don't know which one to click on.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17296368
Those are the field names.  Each one of them is a property of the contact item.  You could pick almost any of them (not actions though as those are actions to take, not fields).  I can't tell you which ones to pick, because I don't know which ones you want or need.  If yoou click on one of them you'll get another page that describes the item.  When you find one you want, write its name down and create a replace statement using it.
0
 

Author Comment

by:ohioequities
ID: 17296606
I was trying to find the field for emailaddress and i can't find that one?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17296658
I just noticed that the link I sent, assuming that's how you're getting to properties, is to the MailItem (i.e. an email message) object and not a ContactItem object which is what you want.  Sorry about that.  Here's the correct link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaol11/html/olobjContactItem_HV05247818.asp?frame=true
0
 

Author Comment

by:ohioequities
ID: 17296683
Got it. Thank you very much!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now