CODEWITHSUNDEEP
excelvbaemailoutlook
Reeyal Hayder Imam
Reeyal Hayder Imam

Reputation: 11

Bulk Mail: Excel to Word then to Outlook Body in VBA crashes Outlook at 100+ mailitems

The code works. Outlook crashes for large data.

Turning off .Display may solve the crash problem.
Disabling .Display does not allow Word content to be copied to Outlook body.

Is there any other way, Word content will be copied to email body with .Display turned off? Emails will be generated in the background.

Sample Excel Photo

Sub WordContent_to_EmailBody()
'On Error Resume Next
Dim o As Outlook.Application
Set o = New Outlook.Application
Dim omail As Outlook.MailItem
Dim wd As Word.Application
Dim doc As Word.Document
Set wd = New Word.Application
wd.Visible = True

' *Word Document Template File getting pulled from cell reference* 
Set doc = wd.Documents.Open(Cells(1, 2).Value) 

Dim i As Long

For i = 4 To 7 ' *Large data loop 4 To 1004 then outlook crashes at 100+*

    Set omail = o.CreateItem(olMailItem)

    With wd.Selection.Find
        .Text = "<<Client>>"
        .Replacement.Text = Sheet1.Cells(i, 1).Value
        .Execute Replace:=wdReplaceAll
    End With
    
    With wd.Selection.Find
        .Text = "<<Remarks>>"
        .Replacement.Text = Sheet1.Cells(i, 2).Value
        .Execute Replace:=wdReplaceAll
    End With

    doc.Content.Copy    ' *Full Word Document Content copied for outlook body*

    'Want to turn off `.display` because it crashes outlook for more than 100+
    ' emails with attachment size more than 500kb. On the other hand, without
    ' `.display` Word content copy paste dose not work

    With omail
        .Display
        .To = Cells(i, 3).Value
        .CC = Cells(i, 4).Value
        .Subject = Cells(i, 5).Value
        .Attachments.Add Cells(i, 6).Value
        Set Editor = .GetInspector.WordEditor 
        Editor.Content.Paste
        .Send    ' *Word Content getting Paste to email body and being sent*
    End With
    Documents("Survey_Outlook.docx").Undo 2

Next i

doc.Close saveChanges:=False
MsgBox "Finish - Check the Generated email in Outlook - OUTBOX FOLDER > Offline Work <"
End Sub

Upvotes: 1

Views: 272

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

I'd suggest using the HTMLBody property without involving the Word object model for setting up the message bodies. You can construct the HTML markup based on the Office documents on your own and then just set the single property - HTMLBody. That doesn't require the Display method to be used before submitting items.

Also you may consider using a low-level API on which Outlook is built on - Extended MAPI or just any other wrappers around that API such as Redemption.

Upvotes: 1

Related Questions