Bulk Emails from Excel using VBA

[Amir Wisal]

Hi Everyone,

Hope you're all doing well.

I have updated/written the following VBA code to send bulk emails from excel which are usually approx. 150-250 in number. The following code displays the email fine however the issue is;

1) It only generates approx. 80-90 emails and then it crashes. when I debug the error, the .Display argument is highlighted. Seems like a memory issue.

Desired Results:
1) I want it to display all the emails at once, if possible without crashing.
2) When I change the .Display part of the code to .Send, it ignores my built-in signature. (When I bring the .Dispaly argument after the HTML signature part, the code runs but does nothing)

Can you please go through the code and correct what am I doing wrong? Thanks


Sub Send_Emails()
Sheet6.Activate
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Emails")
Dim i As Integer
Dim last_col As Integer

Dim ol As Outlook.Application
Dim olmail As Outlook.MailItem
Dim sig As String

Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))

For i = 9 To last_row
Set ol = New Outlook.Application
Set olmail = ol.CreateItem(olMailItem)

With olmail
.To = sh.Range("B" & i).Value
.CC = sh.Range("A" & i).Value
.Subject = Range("e8").Value
.Display
.HTMLBody = "Dear all, " & "<br><br>" & Range("e9").Value _
& "<br><br>" & Range("e10").Value _
& "<br><br>" & Range("e11").Value _
& "<br><br>" & Range("e12").Value _
& "<br><br>" & Range("e13").Value _
& "<br><br>" & Range("e14").Value _
& "<br><br>" & Range("e15").Value _
& "<br><br>" & Range("e16").Value _
& "<br><br>" & Range("e17").Value _
& "<br><br>" & Range("e18").Value _
& .HTMLBody

End With

sh.Range("c" & i).Value = "Displayed"

Next i

Set ol = Nothing
Set olmail = Nothing
Application.ScreenUpdating = True

End Sub

 

[Amir Wisal]

Error Screenshot attached.