Mail Merge using Outlook Contacts

[Resource Dragon]

I have a user form and some code in an excel file that creates a word document (known as MyNewWordDoc.doc). The user form asks the user whether they want to use e-mail or snail mail to send the letters the code generates. If they choose snail mail, the user is then required to enter their address.

What I want to do next with it is call up mail-merge, insert a field for the First Name from the Outlook Contacts address book into the document and then let the user choose the contacts to whom the letter is to be sent (or e-mailed) and where the user has chosen e-mail, use the recipients’ e-mail addresses.

I’m just not too sure how to go about this. How do I call up mail merge – or otherwise use Outlook contacts to solve this problem?

Thank you

[westconn1]

the basic code to assign a data source for a mailmerge document
ThisDocument.MailMerge.DataSource =

try recording a macro in word, to generate some sample code for connecting to outlook contacts list
you can then modify the code, to work in excel with your word object
make sure to declare any word constants if they are not valid in excel (if you are using late binding of word)

sorry i can not do for you as i do not have outlook installed

[Resource Dragon]

Thanks, Pete. I'm working on it, I'll let you know how I got & if I get something working nicely, I'll post it.

cheers

The Dragon

[Resource Dragon]

I've got some of the way along with this problem - apologies for the delay in postings but I've had internet dramas. The problem I have now is telling Word where I want it to insert the merge field. This is the code to date:

file_name = "X Mas Letter " & Format(Date, "yyyy") & ".doc"

If Dir(ActiveWorkbook.Path & "\MyNewWordDoc.doc") <> "" Then
        Kill ActiveWorkbook.Path & "\MyNewWordDoc.doc"
        End If
        .SaveAs (ActiveWorkbook.Path & "\" & file_name)


If optPost = True Then
GoTo SnailMailMerge
ElseIf optEmail = True Then
GoTo eMailMerge
End If

SnailMailMerge:
With ActiveDocument

   ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    WordBasic.MailMergeUseOutlookContacts
Selection.Collapse Direction:=wdCollapseEnd
    Selection.Find.ClearFormatting

Selection.Find
    .Forward = True
        .Text = "Dear "
    If .Found = True Then
        Selection.Find.Execute
    Selection.MoveRight Unit:=wdCharacter, Count:=1
    WordBasic.MailMergeUseOutlookContacts
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="First"
    GoTo MergeDone
End If
    
        End With
        
'    End If
'        ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField, Text:="""First"""

'End With

eMailMerge:

MergeDone:

'        .Close ' close the document
 '   End With

So, how do I go about this properly?

Thanks

[westconn1]

you should avoid using the old wordbasic code now, there will be some VBA equivalent

ActiveDocument.MailMerge.UseAddressBook("olk")

maybe put bookmarks in the main document to position the mergefields

[rusel]

record a macro in word.
generate some sample code to connect outlook contacts list.
you can then modify the code, to work in excel with your word object.

[Resource Dragon]

For rusel and westconn1: The
[CODE]WordBasic.MailMergeUseOutlookContacts[CODE]
line came from recording a macro in Word 2007! (I did think it looked a bit odd...)

I have considered using bookmarks. Thinks: If I insert the bookmark during the insertion of the rest of the text, that should be easier.

[Siddharth Rout]

How do I call up mail merge – or otherwise use Outlook contacts to solve this problem?

Thank you

Try this...

'~~> Set a reference to the outlook abject library
Sub GetNames()
    Set OutApp = CreateObject("Outlook.Application")
    Set myNameSpace = OutApp.GetNamespace("MAPI")
    Set myFolder = myNameSpace.GetDefaultFolder(olFolderContacts) 'It errors out
    
    Set myContacts = myFolder.Items

    For Each myContact In myContacts
        '~~> You can choose to output this to a combobox?
        MsgBox myContact.FullName
    Next
End Sub

Sid