Mailmerge

wowow · #16 12-01-2011, 07:35 PM

Thank macropod,

By the way, could you please let me know if my replacement of the codes that I mentioned in the last post is right? Do I have to continue changing some things in the code?

Thanks

macropod · #17 12-03-2011, 02:45 AM

Hi wowow,

Your code looks correct in terms of the instructions given in the link, but I suspect the variable named 'Counter' should be replaced by 'j'. The variable named 'Counter' isn't dimmed anywhere and it looks as if Doug Robbins might have swapped 'Counter' for 'j'.

wowow · #18 12-03-2011, 09:43 AM

Hi macropod,

Below is all code I made

Code:

Sub emailmergewithattachments()
'To create the email messages in HTML format
Dim source As Document, Maillist As Document, TempDoc As Document
Dim datarange As Range
Dim i As Long, j As Long
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim 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 Err 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
' Open the catalog mailmerge document
With Dialogs(wdDialogFileOpen)
.Show
End With
Set Maillist = ActiveDocument
' Show an input box asking the user for the subject to be inserted into the 
email messages
message = "Enter the subject to be used for each email message."
' Set prompt.
Title = " Email Subject Input"
' Set title.
' Display message, title
mysubject = InputBox(message, Title)
' 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 datarange = Maillist.Tables(1).Cell(j, 1).Range
datarange.End = datarange.End - 1
.To = datarange
.cc = ; "
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
Maillist.Close wdDoNotSaveChanges
' Close Outlook if it was started by this macro.
If bStarted Then
oOutlookApp.Quit
End If
MsgBox source.Sections.Count & " messages have been sent."
'Clean up
Set oOutlookApp = Nothing
End Sub

Could you please help me check if this code is completed? Because I got the error message when I ran this VBA. I attached here the image of error for your information.

By the way, I wonder if the emails in CC/BCC should be put in another single column in Excel database or in the same column contains emails in "To" and is separated by space comma/semi-colon?

Thank you so much!

macropod · #19 12-03-2011, 02:47 PM

Hi wowow,

First off, you need to set a refernce to Outlook in the vbe. To do this, open the vbe and go to Tools|References, scroll down till you find the Microsoft Outlook # Object Library and check it.

Then, there are some errors in the code, mainly in the form of wrapped comment lines that either shouldn't be or should begin with a tick mark. Finally, your .CC line is wrong - you've omitted the expressions you had before. I've fixed these issues in the following code:

Code:

Sub EmailMergeWithAttachments()
'To create the email messages in HTML format
Dim source As Document, Maillist As Document, TempDoc As Document
Dim datarange As Range, i As Long, j As Long, bStarted As Boolean
Dim oOutlookApp As Outlook.Application, oItem As Outlook.MailItem
Dim mysubject As String, message As String, Title As String
Dim objDoc, objSel

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
message = "Enter the subject to be used for each email message."
' Set prompt.
Title = " Email Subject Input"
' Set title.
' Display message, title
mysubject = InputBox(message, Title)
' 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 datarange = Maillist.Tables(1).Cell(j, 1).Range
    datarange.End = datarange.End - 1
    .To = datarange
    .cc = "Firstemailaddress; secondemailaddress"
    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
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 oOutlookApp = Nothing
End Sub

Note: You should alsways use 'Option Explicit' in a code module, so that any undeclared and/or mis-named variables etc will the captured before they cause problems.

wowow · #20 12-03-2011, 07:50 PM

Hi macropod,

I did as you guided me to set a reference to Outlook in the VBE by checking Microsoft Outlook 12 Object Library in VBE=>Tools=>References.

Then I copied your code, pasted in VBE of the letter that will generate the mails. However, it still does not work and alert the same error.

I attached here my mail merge form, data file and the error mesage to have your help in fixing it.

Thank you so much.

macropod · #21 12-04-2011, 07:43 PM

Hi wowow,

There is no vbe Outlook reference in the document you posted.

wowow · #22 12-04-2011, 08:44 PM

Hi macropod,

Thank you for your kind supports. I forgot to enable Outlook reference. I just complete and it works now.

However, I am still not be able to send emails because I don't know what happens and how to fix it. When I opened letter and ran macro (Developer => Macros => Run emailmergewithattachments), the macro generated emails but without any emails in box "To", while there is a line "Firstemailaddress; secondemailaddress" in the box "CC". I attached again the files and error alert for your information. I don't know if it needs to use the function of merging mail, e.g Mailings => Finish & Merge => Send E-mail Messages.

Could you please help me to complete the VBA that will automatically take relevant emails from columns "PIC Email", "CC1", "CC2" in the Data file and put in the box "To" and "CC".

macropod · #23 12-04-2011, 10:01 PM

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

wowow · #24 12-05-2011, 02:53 AM

Hi macropod,

I did all things you guided me. It works! That's great!

There only one problem is it only send 01 message! I don't know why. I tested several times, but the problem still happened, only one message was sent! I attached the picture of message alerted by Word for your help.

Thanks.

macropod · #25 12-05-2011, 03:14 AM

Hi wowow,

Did you execute the merge of your Quotation document? The image you posted suggests you're still using the mailmerge main document. If you want to run the macro from the mailmerge main document instead of from the mailmerge output document, you need to:
• add the following lines to the macro after the 'Dim MySubject' line:
' Run the letter mailmerge
ActiveDocument.MailMerge.Execute
• add the following line to the macro after the 'Next j' line:
Source.Close wdDoNotSaveChanges
• save the changes

You can then run the macro.

wowow · #26 12-05-2011, 03:36 AM

Quote:

Originally Posted by macropod

Hi wowow,

Did you execute the merge of your Quotation document?

Hi macropod,

Do you mean I have to merged the letter to individual documents by following Mailings => Finish & Merge => Edit Individual Documents ... and then run the macro?

Thanks

macropod · #27 12-05-2011, 03:48 AM

Hi wowow,

Yes! If you don't, there's no proper 'source' document for the code to process against the mailings list. If you make the minor code mods I suggested, the macro will take care of it for you.

wowow · #28 12-05-2011, 06:23 AM

Quote:

Originally Posted by macropod

Hi wowow,

If you make the minor code mods I suggested, the macro will take care of it for you.

Yes macropod, the macro works. However, there is one minor problem, the macro creates one blank mail at the beginning. The second and the following messages are ok.

I tested few times, but it still created such blank message.

Please you help me fix the last issue.

Thanks

macropod · #29 12-05-2011, 02:48 PM

Hi wowow,

I don't know why that would happen (remember: the underlying code isn't mine - I'm just helping to adapt it for you) and there's nothing in the code that I can see to cause it. Are you sure the email addresses match the letter contents?

wowow · #30 12-05-2011, 03:55 PM

Hi macropod,

I know it's so kind of you to help me do a lot with this. I only can say thank you so much.

I checked mail merge letter, data in excel file, emails list and everything is ok. I tested few times, but it still generates and send one blank mail at the beginning.

I don't know why too because I have nothing knowledge at VBA.

Could you tested it and help me fix it?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode