#16
|
|||
|
|||
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 |
#17
|
||||
|
||||
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'.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#18
|
|||
|
|||
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 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! |
#19
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#20
|
|||
|
|||
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. |
#21
|
||||
|
||||
Hi wowow,
There is no vbe Outlook reference in the document you posted.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#22
|
|||
|
|||
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". |
#23
|
||||
|
||||
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:
As for the: Quote:
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] Last edited by macropod; 12-07-2011 at 11:34 PM. Reason: Fixed link |
#24
|
|||
|
|||
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. |
#25
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#26
|
|||
|
|||
Quote:
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 Last edited by macropod; 12-05-2011 at 03:47 AM. Reason: Fixed quote |
#27
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#28
|
|||
|
|||
Quote:
I tested few times, but it still created such blank message. Please you help me fix the last issue. Thanks |
#29
|
||||
|
||||
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?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#30
|
|||
|
|||
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? |
|