Mail merge to email(outlook) individual letters to multiple recipients with cc

David
David used Ask the Experts™
on
I have excel data that I use to complete a letter via mail merge.  I then need to create an email and attach letter or paste as an image and send to multiple recipients with a cc as well.   As I have 160 letters, Iam not sure if it is possible to do all that automatically?  I can create VBA in excel to open new mail, add to and cc contacts, fill in subject line and enter text in body.  I could attach file but mail merge creates one file.  I tried word mail merge, but a lot of formatting to get the letter looking professional in the body of email and it will only do one email contact, no cc.  Any help would be great, not sure if I'm wasting time on something that's not possible
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Retired
Top Expert 2012
Commented:
Firstly, you can split the output Word document into separate letters using some VBA macro code like this
Sub SplitMergeResult()
     'declare the variables
    Dim sec As Section 
    Dim rng As Range
    Dim strName As String
    Dim DocA As Document
    Dim DocB As Document
   
    Set DocA = ActiveDocument 'use the active document as input
    For Each sec In DocA.Sections 'step through each section 
        Set rng = sec.Range 'get the range of the section
        rng.MoveEnd wdCharacter, -1 'omit section break
        rng.Copy
        strName = Split(sec.Range.Paragraphs(1).Range.Text, vbTab)(0) 'find the new name in the text. It might well be in a different place in your document
        Set DocB = Documents.Add 'create a new blank document
        DocB.Range.Paste 'paste the text from the current section
        DocB.SaveAs strName & ".doc" 'save the new document and 
        DocB.Close 'close it
    Next sec 'now treat the nest section
End Sub 'all done, so end the procedure

Open in new window

Author

Commented:
Thanks didn't think about approaching in 2 steps.  So the above separates the individual letters.  When I then complete the second piece in excel, is there a way to reference a specific file name to be added in each email?
GrahamSkanRetired
Top Expert 2012

Commented:
The macro above extracts the unique data (e.g. recipient) which is used for the file names from a particular position in the letter. (I arbitrarily chose it to be the text before the first tab character.) You could match the file names to the list of recipients.

Author

Commented:
Thanks for the assistance with the code and approach to my problem.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial