Hi wowow,
It looks like you're trying to do the merge from the Excel data directly. You really do need to follow the instructions at:
Mail Merge to E-mail with Attachments
Amongst other things, you will see that the instructions there require a Word document that then gets used as the email merge data source (just as the say you need to set the Outlook Reference). Ordinarily that means you'd need to do an intermediate Directory/Catalogue merge to generate that source document. For your purposes, the Directory/Catalogue merge output document should have a cell that contains just the email addresses. You could shortcut this part by simply copying & pasting columns B, C & D from your worksheet into a new Word document and deleting the header row.
You also need to have a standard mailmerge output document, based on your existing mailmerge main document, that contains just the body of the letter to each set of recipients. The macro cycles though this, Section by Section, copies the Section contents into the email, looks up the corresponding row in the mailing list table (the one generated by the Directory/Catalogue merge), pastes the 'to' etc details into the message, then sends it.
As you can see, there is a fair bit of work yet to be done.
Regarding the:
Quote:
|
the macro generated emails but without any emails in box "To"
|
that's because you don't have the Word maillist document to use as a data source.
As for the:
Quote:
|
"Firstemailaddress; secondemailaddress" in the box "CC"
|
that's because all you've got so far is a hard-coded text string. You need to configure these as variables, then tell the macro where to find them in the maillist document's table.
The following updated version of the code assumes your Directory/Catalogue merge output document will have the recipient in column 1 and the CC data in columns 2 & 3. I've removed the part of the code that relates to attachments as it seems you don't have any.
Code:
Sub EmailMergeWithAttachments()
'To create the email messages in HTML format
Dim Source As Document, MailList As Document, TempDoc As Document
Dim Recipient As Range, CC1 As Range, CC2 As Range
Dim i As Long, j As Long, bStarted As Boolean, objDoc, objSel
Dim oOutlookApp As Outlook.Application, oItem As Outlook.MailItem
Dim MySubject As String, Message As String, Title As String
Set Source = ActiveDocument
' Check if Outlook is running. If it is not, start Outlook
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
If Error = 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
' Open the catalog mailmerge document
Dialogs(wdDialogFileOpen).Show
Set MailList = ActiveDocument
' Show an input box asking the user for the subject to be inserted into
' the Email messages
MySubject = InputBox("Enter the subject to be used for each email message.", "Email Subject Input")
' Iterate through the Sections of the Source document and
' the rows of the catalog mailmerge document,
' extracting the information to be included in each email.
For j = 1 To Source.Sections.Count
Source.Sections(j).Range.Copy
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = MySubject
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
Set Recipient = MailList.Tables(1).Cell(j, 1).Range
Recipient.End = Recipient.End - 1
Set CC1 = MailList.Tables(1).Cell(j, 2).Range
CC1.End = CC1.End - 1
Set CC2 = MailList.Tables(1).Cell(j, 3).Range
CC2.End = CC2.End - 1
.To = Recipient.Text
.CC = CC1.Text & "; " & CC2.Text
.Send
End With
Set oItem = Nothing
Next j
MailList.Close wdDoNotSaveChanges
' Close Outlook if it was started by this macro.
If bStarted Then oOutlookApp.Quit
MsgBox Source.Sections.Count & " messages have been sent."
'Clean up
Set Recipient = Nothing: Set oOutlookApp = Nothing: Set Source = Nothing
Set oItem = Nothing: Set objDoc = Nothing: Set objSel = Nothing
End Sub