Mail Merge with correct format

gmayor · #2 03-10-2015, 03:56 AM

If by 'send it in the same format as it appears in Word', you mean the message body should have the same format, then you are destined to be disappointed. Word document format and html e-mail format are entirely different from one another. You can get it close if you start by formatting the document in Word's web view, but an exact facsimile is unrealistic.

If format is important then mail it as an attachment in PDF format. http://www.gmayor.com/ManyToOne.htm in one to one mode will achieve either a formatted merge to e-mail body or as an attachment (with or without a personalised covering message). The only proviso is that the merge data must be an Excel worksheet (with the e-mail addresses in the records). The BCC is also possible.

Having said that, it is possible to improve on your macro, but there are some anomalies e.g. you have both the Source Document and the Maillist Document set as the activedocument, which seems odd, given that Source is presumably the product of a mail merge to a new document. You will have to explain that one.

Rather than just use the .Body of the message, you need to use the Outlook Inspector to enable you to edit the message body e.g. something along the lines of

Code:

For j = 1 To Source.Sections.Count - 1
        Set oItem = oOutlookApp.CreateItem(olMailItem)
        With oItem
            .BodyFormat = 2
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range
            'If you don't want the default signature, remove the next line
            oRng.Collapse 1
            .Display 'this must not be removed.
            .Subject = mysubject
            oRng.FormattedText = Source.Sections(j).Range.FormattedText
            Set Datarange = Maillist.Tables(1).Cell(j, 1).Range
            Datarange.End = Datarange.End - 1
            .to = Datarange
            .BCC = "amentinho@example.com"
            For i = 2 To Maillist.Tables(1).Columns.Count
                Set Datarange = Maillist.Tables(1).Cell(j, i).Range
                Datarange.End = Datarange.End - 1
                .Attachments.Add Trim(Datarange.Text), olByValue, 1
            Next i
            .Send
        End With
        Set oItem = Nothing
    Next j

is probably not far off (olInsp, wdDoc and oRng are each declared as Object) but without the document to test against it is only a guide.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge Format random 0s	lmoir87	Mail Merge	2	07-31-2014 03:45 AM
Change Mail Merge Date Format from US to UK	Evanaught	Mail Merge	1	09-29-2013 08:02 PM
Issue with date format in mail merge document	walshjod	Mail Merge	4	11-28-2012 04:46 AM
Mail merge will not format date field generated by Excel IF statement	borntorun75	Mail Merge	3	12-16-2011 06:28 AM
Correct Format needed	TonyB	Mail Merge	3	05-03-2011 01:10 AM