Not delivering all mails and losing formatting using mail merge

[mso3]

Hi friends,

Greetings from me!

I want to send wishing mails using mail merge.
The mails are not delivering from top to bottom that is from first entry to last entry.
I’m not getting a name of person having a birthday after a word ‘Dear ,’
I’m losing the formatting of mails as formatted in a main word document.
The picture of cake is aligning left in mails which should be centered as in main word document.
I want a current date (today) under the picture cake leaving one blank line above and under a current date in green color.

Any help will be highly appreciated.

Thanking you in anticipation.

[LJMetzger]

Hi mso3,

See the attached .zip file that contains the following files:
a. LJMOpenWordMailMerge6ForMso3.xls - Excel file that runs Mail Merge
b. Birthday.docx - modified copy of your Word file.
c. SendMails.xls - modified copy of your Excel file.

To run Mail Merge the first time:
a. Extract the 3 files from the .zip file into the same folder.
b. Open file LJMOpenWordMailMerge6ForMso3.xls
c. Left Click the Brown Shape at the Top Right to put the proper files and folders on lines 25 and 27.
d. Left Click the Yellow Shape at the Top Left to run the Mail Merge.


Notes and changes required in your documents:

In word document:
I’m not getting a name of person having a birthday after a word ‘Dear ,’
a. Type in the name of the field (e.g. NAME_OF_PERSON). BLANKS are NOT allowed in field name.
b. Highlight the field name and then press CTRL F9 to create a Merge Field.
When opening a document, Merge Fields may not be visible. To make Merge Fields visible, highlight the entire document and press SHIFT F9.
Merge Fields are displayed in curly brackets preceeded by ref (e.g. {ref NAME_OF_PERSON} ).

I want a current date (today) under the picture cake leaving one blank line above and under a current date in green color.
a. Put in blank lines as required.
b. Put the cursor where you want the date.
c. Insert > Date and Time > Select Format. Check 'Update Automatically' > OK

In Excel File:
a. It is suggested that the first sheet in the file be used for Mail Merge purposes.
b. Header row is line 1. This row contains field names with NO SPACES in a field name.
c. Rows 2 thru n contain data.

Lewis

[mso3]

Hi Lewis Sir,

Good morning!

Thank you for your kind response to solve my problem.
Now it’s very close to my requirement.
I have to send the birthday wishing to friends on their birthday.
So I want to create letters for only those friends whose birthday is on that day that is today each day.
I want to separate each letter to attach it to the mail.
The word file name should be the name of friend.
At present it’s creating the letters for all friends whose birthday is not on that particular day which I don’t want. Everything else is perfect.

Please do the above amendments to complete the program positively.

Extremely sorry for trouble.

Thank you and have a nice day.

[mso3]

Hi Lewis Sir,

Greetings from me!

I found a code to achieve my target on net but didn’t understand it. Will you please look it and suggest me a solution with attachment?

'purpose: save each letter generated after mail merge in a separate file
'         with the file name equal to first line of the letter.
'
'1. Before you run a mail merge make sure that in the main document you will 
'   end your letter with a Section Break (this can be found under 
'   Page Layout/Breaks/Section Break Next Page)
'2. Furthermore the first line of your letter contains the proposed file name
'   and put an enter after it. Make the font of the filename white, to make it 
'   is invisible to the receiver of the letter. You can also include a folder 
'   name if you like.
'3. Run the mail merge as usual. A file which contains all the letters is 
'   generated.
'4. Add this module to the generated mail merge file. Use Alt-F11 to go to the 
'   visual basic user interface, right click in the left pane on the generated
'   file and click on Import File and import this file
'5. save the generate file with all the letters as ‘Word Macro Enabled doc 
'   (*.docm)’.
'6. close the file.
'7. open the file again, click allow content when a warning about macro's is 
'   shown.
'8. execute the macro with the name SaveRecsAsFiles


Sub SaveRecsAsFiles()
    ' Convert all sections to Subdocs
    AllSectionsToSubDoc ActiveDocument
    'Save each Subdoc as a separate file
    SaveAllSubDocs ActiveDocument
End Sub

Private Sub AllSectionsToSubDoc(ByRef doc As Word.Document)
    Dim secCounter As Long
    Dim NrSecs As Long
    NrSecs = doc.Sections.Count
    'Start from the end because creating
    'Subdocs inserts additional sections
    For secCounter = NrSecs - 1 To 1 Step -1
        doc.Subdocuments.AddFromRange _
          doc.Sections(secCounter).Range
    Next secCounter
End Sub

Private Sub SaveAllSubDocs(ByRef doc As Word.Document)
    Dim subdoc As Word.Subdocument
    Dim newdoc As Word.Document
    Dim docCounter As Long
    Dim strContent As String, strFileName As String

    docCounter = 1
    'Must be in MasterView to work with
    'Subdocs as separate files
    doc.ActiveWindow.View = wdMasterView
    For Each subdoc In doc.Subdocuments
        Set newdoc = subdoc.Open
        'retrieve file name from first line of letter.
        strContent = newdoc.Range.Text
        strFileName = Mid(strContent, 1, InStr(strContent, Chr(13)) - 1)
        'Remove NextPage section breaks
        'originating from mailmerge
        RemoveAllSectionBreaks newdoc
        With newdoc
            .SaveAs FileName:=strFileName
            .Close
        End With
        docCounter = docCounter + 1
    Next subdoc
End Sub

Private Sub RemoveAllSectionBreaks(doc As Word.Document)
    With doc.Range.Find
        .ClearFormatting
        .Text = "^b"
        With .Replacement
            .ClearFormatting
            .Text = ""
        End With
        .Execute Replace:=wdReplaceAll
    End With
End Sub

Thanking you,

[LJMetzger]

Hi mso3,

Thank you for posting the sample code. I was able to come up with a simpler solution, that will probably be easier for you to understand.

See the attached .zip file that contains the following files (all files have been modified since the previous update):
a. LJMOpenWordMailMerge7.xls - Excel file that runs Mail Merge
b. Birthday.docx - modified copy of your Word file.
c. SendMails.xls - modified copy of your Excel file.

To run Mail Merge the first time:
a. Extract the 3 files from the .zip file into the same folder.
b. Open file LJMOpenWordMailMerge7.xls
c. Left Click the Brown Shape at the Top Right to put the proper files and folders on lines 25 and 27.
d. Left Click the Yellow Shape at the Top Left to run the Mail Merge.

Please note that I added a few things to the Excel file that runs the Mail Merge:
a. User entry of a folder name where the Output Files will go.
b. User entry of a Mail Merge 'Field Name'. This is the Column in the Excel Data File that is used to create the 'Output File Name'.
c. User entry of 'First' and 'Last' Birthday to include. If 'Today' is wanted, the 'Birthday' cells can be left blank.
d. A command button that will display the 'Output File Folder' in using 'Windows Explorer'.

The following code excerpt is taken (out of context) from the attached file, and demonstrates how the Mail Merge Code works. Several statements not related directly to function have been removed for ease of readability.

  'Create Word application object
  Set WordApp = CreateObject("Word.Application")

  'Create Word Document Object and Open Word file
  'Make the word application visible
  'Set the focus on the Word document
  Set WordDoc = WordApp.Documents.Open(sPathAndWordFile)
  WordApp.Visible = True
  WordApp.Application.Activate
  
  'Set the document type to 'Form Letters'
  'If some other type see the enumerated constant list above
  WordDoc.MailMerge.MainDocumentType = wdFormLetters

  
  'Open the mail merge data file for mail merge
  WordDoc.MailMerge.OpenDataSource _
    Name:=sPathAndExcelMailMergeDataFile, _
    AddToRecentFiles:=False, _
    Revert:=False, _
    Format:=wdOpenFormatAuto, _
    Connection:="Data Source=" & sPathAndExcelMailMergeDataFile & ";Mode=Read", _
    SQLStatement:="SELECT * FROM `" & sExcelMailMergeDataFileSheetName & "$`"
  
  iDocumentCount = WordDoc.MailMerge.DataSource.RecordCount

  'Execute the mail merge
  With WordDoc.MailMerge
  
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    
    'Perform the Mail Merge One Data Record at a time
    For iDataRecord = 1 To iDocumentCount
      .DataSource.ActiveRecord = iDataRecord
      .DataSource.FirstRecord = iDataRecord
      .DataSource.LastRecord = iDataRecord
      .Execute Pause:=False
      
      'Get the Output File Name
      sBaseOutPutFileName = .DataSource.DataFields(sExcelMailMergeFieldNameUsedToCreateOutputDataFile).Value
      sBaseOutPutFileName = Trim(sBaseOutPutFileName)
      
      'Create a dummy file name if the file name was missing
      If Len(sBaseOutPutFileName) = 0 Then
        sBaseOutPutFileName = "Missing File Name Place Holder " & iDataRecord
      End If
      
      'Save the file from the data record just processed (Excel will OVERWRITE existing files)
      WordApp.ActiveDocument.SaveAs Filename:=sPathForOutputFile & sBaseOutPutFileName & ".docx"
      WordApp.ActiveDocument.Close
      
    Next iDataRecord
    
    
  End With
    
  'Close the original Word document that is the 'Mail Merge' Source
  WordDoc.Close SaveChanges:=wdDoNotSaveChanges
  
  'Close Microsoft Word
  'Word asks about what to do with the Mail Merge OUTPUT FILE
  WordApp.Quit
  
  'Clear object pointers
  Set WordApp = Nothing
  Set WordDoc = Nothing

Lewis

[mso3]

Hi Lewis Sir,

Good morning!

Wav! Excellent! Really you are a master of mail merge.

The program is working properly with correct output but now I have inserted columns to make it more perfect to achieve my target and it’s not working.

I have added a unique id for each person to insert his/her photograph as a water mark inn word file.
If the photograph is not available then a default water mark of flowers should be inserted in the word file.
I want the current date (today) in dd mmmm yyyy format.
I have to send the wishing letters in two languages English and devanagari font. So I added name1, name 2 and name3 columns to meet the requirement.
It’s not a problem for me.

The problem now is the program is not working. I tried my level best to find out the problem but in vain.

Therefore, I came here again to take your guidance to solve the problem.

I’m extremely sorry for giving trouble you more. Please apologize me for the same.

Sorry once again and have a nice day.

[LJMetzger]

Your problems seems to be in the Word file. You are looking for a Date Merge Field {MERGEFIELD DATE}. This is probably incorrect, and causes an error because the Excel data file DOES NOT have a field Named 'DATE'. You say you want the current date in a specific format which should probably be: {DATE \@ "d MMMM yyyy"}. Notice that the current date DOES NOT contain the KEYWORD MERGEFIELD.

See the attached file, which implements the changes.

Lewis

[mso3]

Hi Lewis Sir,

Greetings from me!

Thank you. Now the date problem is solved.

Please guide me about the watermark problem.

If it’s not possible then we will close this thread today marking it solved.

Have a nice time.

[mso3]

Hi Lewis Sir,

Good morning!

I want to send wishing mails to all of my contacts. So I want to create data output letters for all the contacts.

Please suggest me an amendment in the code to achieve the target.

Thank you.

[LJMetzger]

Happy to inform you that I have solved the file name problem successfully.

I'm glad it worked out. I've been busy, but when I have time, I will look into the problem.

If I want to create the output data letters for all of my contacts then which amendment is require. I have to send greeting cards on the festival to all of my friends on festival. So in this case the ‘Today’ sheet is not require. I have to send the greeting cards to all contacts on the ‘Access’ sheet.

On the Excel File that runs the MailMerge on 'Sheet1', change the value on line 32 (First Birthday to Include:) to January 1, 2015 and change the value on line 33 B](Last Birthday to Include:[/B]) to December 31, 2015. That will put all entries on the 'Access' Sheet on the 'Today' sheet and do what you want.

[mso3]

Hi Lewis Sir,

Good morning!

Wav! What a simple solution. You have created a excellent program to achieve all things in a single program. I didn't think of it and wasted a lot of time to achieve the target. No matter, now the problem is solved.

I appreciate you for the same.

Have a nice time.

Thank you.