Hi,

I have a very convoluted system at my job. I would like to see if I can create a shortcut to the end process. Please bear with me, it's long.

1. I receive a number of applications that the Users have email to me as a PDF form.
2. I take the information from the form and copy paste into an Excel file (Master File) I can't just export the data straight from the form because sometimes the User has flattened it and it's now an image.
3. some piffling stuff like adding enclosed attachments and then flatten the image... no need to go into here
4. the selection committee lets me know which of the applicants have succeeded

Now for the tricksy part:

1. I have the Mail Merge document and I use Mailings to populate the fields.
2. I Finish & Merge and set it for Edit Individual Documents. Now I have a multi-page Word document; each page is a separate letter I send to the recipients.
3. I save the merged document as a .pdf
4. I take the now long pdf into Acrobat Pro, Organize Pages, and extract pages, saving them as separate files.
5. Now I have several pdf files that are titled Letter 1, Letter 2, Letter 3... and so on.
6. I re-title each PDF with the name of the applicant, whose name is in the Master file Excel sheet.

Final stage:

1. I take Outlook offline for this part
2. I have an email Mail Merge I use to fill out the recipient, and any one who needs to be cc'd on the email.
3. I use Mailings to Send Email Message
4. All the emails are now in my Outbox
5. I open each email and attach the pdf files. So the "Joan Smith" .pdf letter is attached to the "Joan Smith" email.
6. Once I do that for each email, I take Outlook back online and the emails go swoosh! and off they go!
7. Whew! Now I just deal with all the Returned emails because the applicant wrote theirs incorrectly

TL:DR

Application form > Excel file > Mail Merge > Save to pdf > separate pdf files > rename pdfs > Outlook offline > email mail merge > attach letter pdf to appropriate email message > Outlook back online

If anyone has any ideas or know a better way to do this I'd appreciate it. I know this process is probably, needlessly over-complicated. It just sort of developed over the years.

Thanks for reading this far.

Use the Merge with Attachments facility of my Merge Tools Add-in that is contained in the MERGE TOOLS ADD-IN.zip file that you can download from:

https://mergetoolsaddin.com/

Extract the files from the archive and read the:

“READ ME – Setting up and using the Merge Tools Add-in.pdf

to see how to install and use the various tools.

Using those tools, it is possible to perform the following types of merge that cannot be done with Mail Merge “out-of-the-box”:

• Merge to e-mail messages either with or without attachments, with the documents created by the merge being sent as either Word or PDF attachments or as the body of the e-mail message .  The email messages can, if necessary, also be sent to CC and BCC addresses and the subject of the message can include data from a field in the data source.
• Merge to individual documents in either Word or PDF format with the filenames being supplied by the data in one of the fields in the data source
• Many to One type merges, which can be used for creating documents such as invoices where there are multiple records in the data source that have common data in one of the fields
• Merging to a document that will include a chart that is unique to each record in the data source
• Merging a document with Content Controls
• Merging a document that contains Legacy FormFields
• Duplex Merges
• Merging to a printer that will collate and staple the output created from each record in the data source.

The requirements for using the system are:

• The mail merge main document must be of the Letters type, though that does not mean that the output cannot be sent as an e-mail message where relevant.
• For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet. For the Chart Merge utility, see the Mail Merging with Charts document that is included in the Merge Tools Add-in Zip file for additional requirements for the data source for use with that utility
• For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.
• For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40.

The MergeTools Add-in will NOT work with the “New Outlook”, which is just a re-badged version of Windows Mail and like its predecessor, it does not support automation and hence will not send the messages created by the MergeTools Add-in.

Be aware, that any messages that you had tried to send with the “New Outlook” will have been placed in the Outbox of the original Outlook and they will be sent as soon as you revert to that version of Outlook.   If you do not want that to happen, you should put Outlook Off-line, or disconnect your computer from the Internet.

For a demonstration of the use of the facility, prepared by a Microsoft employee, see

https://www.youtube.com/watch?v=yj_s3cdfVDY

https://www.youtube.com/watch?v=GGXZ3aQN7oo

Hi Amy Kunce,

Have a good day !

I value your feedback, please mark it as an answer if it answer your question and click Yes to help me improve the support experience.  

It's so important for me to mark it as an answer. It's help me to have a good motivation forward.

I appreciate your patience with me.    
Looking forward to your response and have a great day ahead!!  
  
Best regards.  

Sting-Ng - Microsoft Community Support Specialist

Hi Amy Kunce, 

Thank you for replying, and the information that you have given to me I really appreciate it.    

To give you more specific recommendations, it would be helpful to know: 

• What version of Microsoft Office are you using? (e.g., Office 365, Office 2019, etc.)
• Does your organization use Microsoft 365? (This impacts Power Automate availability).
• Are you comfortable with light scripting or learning a new tool (like Power Automate)?
• Are there any budget constraints for third-party software or tools?

I understand you want to streamline a complex process involving Word mail merge, PDF creation, and email distribution. I'll propose a Python script using libraries like python-docx, pandas, comtypes (for Windows COM automation), and PyPDF2 to automate most of your workflow. This solution assumes you're on Windows (due to Outlook and Word usage) and have Microsoft Office installed. The script will handle data extraction from the Excel Master File, perform the mail merge, split the PDF, rename files, and automate email creation with attachments. 

Here’s a Python script to automate the process: 

1. Prerequisites:  
   1. Install Python 3.x.
   2. Install required libraries: pip install pandas python-docx PyPDF2 comtypes pywin32
   3. Ensure Microsoft Word and Outlook are installed.
   4. Update the file paths (EXCEL_FILE, WORD_TEMPLATE, OUTPUT_DIR) in the script to match your system.
2. Excel File Setup:  
   1. Your Master File Excel sheet should have columns: Name, Email, CC_Email (optional), and Status (with values like "Successful" for approved applicants).
   2. Ensure the data is clean (no missing required fields for successful applicants).
3. Word Template:  
   1. Create a Word mail merge template with fields matching your Excel columns (e.g., <
   2. Save it as a .docx file.
4. Running the Script:  
   1. Save the script as automate_mail_merge.py.
   2. Run it: python automate_mail_merge.py.
   3. The script will:  
      1. Read the Excel file and filter successful applicants.
      2. Perform the mail merge and save as a single Word document.
      3. Convert the merged document to a PDF.
      4. Split the PDF into individual files named after applicants.
      5. Draft emails in Outlook’s Outbox with the correct PDF attached.
   4. Outlook remains offline during email drafting, mimicking your process.

Code Script: 

import pandas as pd 

import comtypes.client 

import os 

from PyPDF2 import PdfReader, PdfWriter 

import win32com.client 

import pythoncom 

import uuid 

from datetime import datetime 

File paths (update these to your actual file paths) 

EXCEL_FILE = "path/to/your/MasterFile.xlsx" 

WORD_TEMPLATE = "path/to/your/MailMergeTemplate.docx" 

OUTPUT_DIR = "path/to/output/folder" 

MERGED_PDF = os.path.join(OUTPUT_DIR, "MergedLetters.pdf") 

Ensure output directory exists 

if not os.path.exists(OUTPUT_DIR): 

    os.makedirs(OUTPUT_DIR) 

Step 1: Read Excel Master File 

def read_master_file(excel_path): 

    df = pd.read_excel(excel_path) 

    # Ensure required columns exist (adjust column names to match your Excel) 

    required_columns = ['Name', 'Email', 'CC_Email', 'Status'] 

    if not all(col in df.columns for col in required_columns): 

        raise ValueError("Excel file missing required columns: Name, Email, CC_Email, Status") 

    # Filter for successful applicants 

    return df[df['Status'] == 'Successful'][['Name', 'Email', 'CC_Email']] 

Step 2: Perform Word Mail Merge 

def perform_mail_merge(template_path, data_source_path, output_doc): 

    pythoncom.CoInitialize() 

    try: 

        word = comtypes.client.CreateObject("Word.Application") 

        word.Visible = False  # Run Word in background 

        doc = word.Documents.Open(template_path) 

        # Set up mail merge 

        doc.MailMerge.MainDocumentType = 0  # wdFormLetters 

        doc.MailMerge.OpenDataSource(Name=data_source_path) 

        doc.MailMerge.Execute() 

        # Save merged document 

        merged_doc = word.ActiveDocument 

        merged_doc.SaveAs(output_doc) 

        merged_doc.Close() 

        doc.Close() 

        word.Quit() 

    finally: 

        pythoncom.CoUninitialize() 

Step 3: Convert Word to PDF 

def convert_to_pdf(word_path, pdf_path): 

    pythoncom.CoInitialize() 

    try: 

        word = comtypes.client.CreateObject("Word.Application") 

        doc = word.Documents.Open(word_path) 

        doc.SaveAs(pdf_path, FileFormat=17)  # 17 = wdFormatPDF 

        doc.Close() 

        word.Quit() 

    finally: 

        pythoncom.CoUninitialize() 

Step 4: Split PDF into individual files 

def split_pdf(pdf_path, output_dir, name_list): 

    pdf = PdfReader(pdf_path) 

    for i, page in enumerate(pdf.pages): 

        writer = PdfWriter() 

        writer.add_page(page) 

        # Use name from Excel for filename, sanitized 

        name = name_list[i].replace(" ", "_").replace("/", "_").replace("\", "_") 

        output_file = os.path.join(output_dir, f"{name}.pdf") 

        with open(output_file, 'wb') as f: 

            writer.write(f) 

        print(f"Created PDF: {output_file}") 

Step 5: Send emails with attachments 

def send_emails(df, pdf_dir): 

    pythoncom.CoInitialize() 

    try: 

        outlook = win32com.client.Dispatch("Outlook.Application") 

        namespace = outlook.GetNamespace("MAPI") 

        for index, row in df.iterrows(): 

            name = row['Name'] 

            email = row['Email'] 

            cc_email = row['CC_Email'] if pd.notna(row['CC_Email']) else "" 

            pdf_name = name.replace(" ", "_").replace("/", "_").replace("\", "_") 

            pdf_path = os.path.join(pdf_dir, f"{pdf_name}.pdf") 

            if not os.path.exists(pdf_path): 

                print(f"PDF not found for {name}, skipping email.") 

                continue 

            # Create email 

            mail = outlook.CreateItem(0)  # 0 = MailItem 

            mail.To = email 

            if cc_email: 

                mail.CC = cc_email 

            mail.Subject = f"Application Outcome for {name}" 

            mail.Body = f"Dear {name},\n\nPlease find attached your outcome letter.\n\nBest regards,\nYour Name" 

            mail.Attachments.Add(pdf_path) 

            mail.Save()  # Save to Outbox (Outlook offline mode) 

            print(f"Email drafted for {name} to {email}") 

    finally: 

        pythoncom.CoUninitialize() 

def main(): 

    # Step 1: Read Excel data 

    df = read_master_file(EXCEL_FILE) 

    # Save temporary Excel for mail merge 

    temp_excel = os.path.join(OUTPUT_DIR, "TempDataSource.xlsx") 

    df.to_excel(temp_excel, index=False) 

    # Step 2: Perform mail merge 

    merged_doc = os.path.join(OUTPUT_DIR, "MergedDocument.docx") 

    perform_mail_merge(WORD_TEMPLATE, temp_excel, merged_doc) 

    # Step 3: Convert to PDF 

    convert_to_pdf(merged_doc, MERGED_PDF) 

    # Step 4: Split PDF 

    split_pdf(MERGED_PDF, OUTPUT_DIR, df['Name'].tolist()) 

    # Step 5: Create emails 

    send_emails(df, OUTPUT_DIR) 

    # Clean up temporary files 

    for file in [temp_excel, merged_doc, MERGED_PDF]: 

        if os.path.exists(file): 

            os.remove(file) 

            print(f"Deleted temporary file: {file}") 

    print("Process completed. Check Outlook Outbox for drafted emails.") 

if __name__ == "__main__": 

    main() 

Final Steps:  

1. Open Outlook, review the drafted emails in the Outbox, and take Outlook online to send them. 
   1. Handle any returned emails manually as you currently do.

Notes 

• Error Handling: The script assumes your Excel file has the required columns and valid data. Add error handling as needed for your specific case (e.g., invalid email formats).
• PDF Extraction: If your input PDF forms are flattened, you’ll still need to manually extract data into the Excel file, as OCR or manual entry is required for images.
• Outlook Offline: The script saves emails to the Outbox, keeping Outlook offline. You can manually review before sending.
• Scalability: For large numbers of applicants, test the script with a small batch first to ensure performance.
• Security: Ensure your system allows COM automation (some IT policies restrict this).

This script significantly reduces manual steps by automating the mail merge, PDF splitting, renaming, and email drafting. If you have specific requirements (e.g., custom email body, additional fields), let me know, and I can adjust the script! 

We truly appreciate your patience as we look into this matter. Thank you for choosing Microsoft and we value your support. 

Best regards.      

Sting-Ng - Microsoft Community Support Specialist.