VBA script making me crazy .... send email based on excel sheet contact list

[nabeelzz]

So we finished our budget for 74 departments. And I m the one who is sending individually to all. I had a vba code to send these to different people. I have been using vba code to create emails for all of the recipients. This time there was a standard word file which I was supposed to send as text of the email. The code is given below.

Sub TempFile_bulkemail()
Dim wd As Object, editor As Object
Dim doc As Object
    
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
For i = 21 To 30
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
    Set wd = CreateObject("Word.Application")
    Set doc = wd.documents.Open("C:\Users\201799\Desktop\Experiment Folder\Budget Templates for 2018-19\Budget e-Mail Message.docx")
    doc.Content.Copy
    doc.Close
    Set wd = Nothing

        With olMail
            .To = Cells(i, 1)
            .CC = Cells(i, 2)
            .Subject = Cells(i, 3)
            .BodyFormat = olFormatHTML
            Set editor = .GetInspector.WordEditor
            editor.Content.Paste
            .Attachments.Add ("C:\Users\201799\Desktop\Experiment Folder\Budget Templates for 2018-19\Budget templates for circulation\PDFs\" & Cells(i, 5))
            .Attachments.Add ("C:\Users\201799\Desktop\Experiment Folder\Budget Templates for 2018-19\Budget templates for circulation\" & Cells(i, 4))
            .Display     
        End With
Next
End Sub

This time the code gave me a tough time. It kept on crashing whenever I tried to create emails. The irony is that I still sent all emails through the code. What I did was that I pressed F8 (executed code line by line) and it worked okay. But if I run the entire code with the execute button it always got stuck.


My question is …. Can someone advise why excel does that. It didn’t execute full code, but if I went with F8, line by line, the code worked fine.

I m not an expert, but my diagnosis is that the Word file was a little heavy (in terms of processing). When I was executing code line by line I could see that the steps where I had to open the word file, copy its contents and then paste them as body of email, that step took a little time. Around 4 to 5 seconds each time.

Even while executing line by line I got error sometime. But then I stopped the code and ran it again and it worked fine. But line by line.
Previously I used to create 74 emails with one click, but this time I had to execute line by line which took me more than an hour just creating emails and sending.
Any comments ???

 

[Domenic]

You only need to create an instance of Outlook and Word once. The same thing with your Word document. You only need to open it once so that you can copy it's contents to the clipboard and make it available for each email you create. Also, you're already using early binding for Outlook, so you should also use early binding for Word, since it should be a bit more efficient.

I haven't tested it, but try the following macro. It uses early binding for both Outlook and Word. So make sure that you set a reference (VBE > Tools > References) to both libraries...

1) Microsoft Outlook Object Library
2) Microsoft Word Object Library

Here's the macro...

Option Explicit

Sub TempFile_bulkemail()


    'Declare variables for Outlook
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    Dim editor As Object
    
    'Declare variables for Word
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    
    'Declare other variables
    Dim i As Long
    
    'Create an instance of Outlook
    Set olApp = New Outlook.Application
    
    'Crerate an instance of Word
    Set wdApp = New Word.Application
    
    'Open Word document
    Set wdDoc = wdApp.Documents.Open("C:\Users\201799\Desktop\Experiment Folder\Budget Templates for 2018-19\Budget e-Mail Message.docx")
    
    'Copy contents of Word document
    wdDoc.Content.Copy
    
    'Create emails
    For i = 21 To 30
        Set olMail = olApp.CreateItem(olMailItem)
        With olMail
            .Display
            .To = Cells(i, 1).Value
            .CC = Cells(i, 2).Value
            .Subject = Cells(i, 3).Value
            .BodyFormat = olFormatHTML
            Set editor = .GetInspector.WordEditor
            editor.Content.Paste
            .Attachments.Add ("C:\Users\201799\Desktop\Experiment Folder\Budget Templates for 2018-19\Budget templates for circulation\PDFs\" & Cells(i, 5).Value)
            .Attachments.Add ("C:\Users\201799\Desktop\Experiment Folder\Budget Templates for 2018-19\Budget templates for circulation\" & Cells(i, 4).Value)
        End With
        DoEvents
    Next
    
    'Close Word document
    wdDoc.Close
    
    'Quit Word application
    wdApp.Quit
    
    'Clear variables from memory
    Set olApp = Nothing
    Set olMail = Nothing
    Set editor = Nothing
    Set wdApp = Nothing
    Set wdDoc = Nothing
    
End Sub

Hope this helps!