Runtime Error, VBA EXCEL for email automation

Question

Ive been working on email automation for excel using vba, my code works only for the first email, and got runtime error on the next,

ive tried setting object to nothing but to no avails..

i dont know where the leak in memory is

can somebody point me up

error is at item at object remark. I've provided a screenshot for the error..

Module Name = Automail

Public PublicRow As Integer
Dim mark As New Remarks

Sub Button_Click()

Dim LastR As Long
Dim CRow As Long
Dim sSendTo As String
Dim sSendCC As String
Dim sSendBCC As String
Dim sSubject As String
Dim txt As String
Dim OutApp As Object
Dim OutMail As Object


Set OutApp = CreateObject("Outlook.Application")
Set mark.item = Nothing

OutApp.Session.Logon

LastR = Cells(Rows.Count, 2).End(xlUp).Row

For CRow = 3 To LastR
    If Cells(CRow, 6) <> "Email sent" Then
        If Cells(CRow, 3) <= Date Then

            Set OutMail = OutApp.CreateItem(0)
            Set mark.item = OutMail

            sSendTo = Cells(CRow, 5)
            sSendCC = ""
            sSendBCC = ""
            sSubject = "Project Due Date"
            PublicRow = CRow

            With OutMail
                .To = sSendTo
                If sSendCC > "" Then .CC = sSendCC
                If sSendBCC > "" Then .BCC = sSendBCC
                .Subject = sSubject

                txt = "Dear " & Cells(CRow, 4) & ", "
                txt = txt & vbCrLf & vbCrLf
                txt = txt & "The due date has been reached for the 
 project:"
                txt = txt & vbCrLf & vbCrLf
                txt = txt & "    " & Cells(CRow, 2)
                txt = txt & vbCrLf & vbCrLf
                txt = txt & "Please take the appropriate actions."
                txt = txt & vbCrLf & vbCrLf
                txt = txt & "Regards,"
                txt = txt & vbCrLf
                txt = txt & "Danial"

                .Body = txt
                .Display (True)
            End With

            Set OutMail = Nothing

        End If
    End If
Next CRow

Set mark.item = Nothing
Set OutApp = Nothing

End Sub

Class Name = Remarks

Option Explicit

Public WithEvents item As Outlook.MailItem

Private Sub item_Close(Cancel As Boolean)

Dim boolSent As Boolean

boolSent = item.Sent

If Err.Number = 0 Then
    Cells(PublicRow, 6) = "Email not sent"
    Cells(PublicRow, 7) = "X"
Else
    Cells(PublicRow, 6) = "Email sent"
    Cells(PublicRow, 7) = Now()
End If

End Sub

Error:

Change `Dim mark As New Remarks` to `Dim mark As Remarks` and see what changes. I'm guessing you set `mark` to nothing, but since you use the `New` Keyword it immediately is instanced again. See here: https://stackoverflow.com/a/42656772/10223558 — L8n, Sep 03 '19 at 07:04
You can also leave out the whole CC and BCC stuff if you don't need it, makes the code a bit more readable. Move `mark` inside the Method like outApp, then it will go out of scope at the end of the Method and you won't have to `= Nothing` it. — L8n, Sep 03 '19 at 07:07
Hi L8n, by removing New from the declaration. The macro does not declare the Remarks as an object and return a not exist error. But after the error, and rewriting "New" back on. The code runs fine.. i noticed that the code runs fine when the pop out windows of selecting outlook profile is gone.. So i think i have to do with the popup windows itself that block the email from being detected by my Remarks class — Arepeel, Sep 03 '19 at 08:10

score 0 · Answer 1 · answered Sep 03 '19 at 08:30

0

Cleaned up the code a bit, I can't test it since I do not know what the remarks class is. There are a few other thing that look strange, what kind of module (class/UF/module) is automail? The Button_Click look suspiciously like a UserForm, in this case I recommend reading: https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/

Public PublicRow As Integer

Sub Button_Click()


    Dim LastR As Long
    Dim CRow As Long
    Dim sSendTo As String
    Dim sSubject As String
    Dim bodyTxt As String
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")

    Dim mark As Remarks
    Set mark = New Remarks
    'This is the "usual" way to instantiate an object, see here: https://stackoverflow.com/a/42656772/10223558

    Set mark.item = Nothing ' why set it to nothing here, usually this would happen in the class itself?

    OutApp.Session.Logon

    LastR = Cells(Rows.Count, 2).End(xlUp).Row

    For CRow = 3 To LastR
        If Cells(CRow, 6) <> "Email sent" And Cells(CRow, 3) <= Date Then
            Set OutMail = OutApp.CreateItem(olMailItem) 'use constant name instead of integer, makes it more legible.

            sSendTo = Cells(CRow, 5)
            sSubject = "Project Due Date"
            PublicRow = CRow

            bodyTxt = buildBody(Cells(CRow, 4),  Cells(CRow, 2)

            With OutMail
                .To = sSendTo
                .Subject = sSubject
                .Body = bodyTxt 
                .Display (True)
            End With
            Set mark.item = OutMail
            'shouldn't there be some code to send the mail here?
        End If

    Next CRow
End Sub

Private Function buildBody(ByVal receiverName as String, ByVal projectName as String) as String
    Dim txt As String
    txt = "Dear " & receiverName  & ", "
    Txt = txt & vbCrLf & vbCrLf
    txt = txt & "The due date has been reached for the project:"
    txt = txt & vbCrLf & vbCrLf
    txt = txt & "    " & projectName 
    txt = txt & vbCrLf & vbCrLf
    txt = txt & "Please take the appropriate actions."
    txt = txt & vbCrLf & vbCrLf
    txt = txt & "Regards,"
    txt = txt & vbCrLf
    txt = txt & "Danial"
    buildBody = txt
End Function

answered Sep 03 '19 at 08:30

L8n

728
1
5
15

The sub,module and class name is just class that i rename to.. And i send the mail using .display function that is in my code i set the mark to nothing because i thought the instance from previous app would effect the next instance.. idk much about that, just tried it.. I wil try the code, and will tell you how it goes – Arepeel Sep 03 '19 at 08:44
Update here,upon testing your code.The same error happened. First email works fine but the next returns error. This is how my code works. My excel includes column with different emails, and column with different due date. Upon reaching the due date, clicking a form button would send email to all respective emails warning about the due date. The errors happens when i test the code with 2 rows of due date that have expired. When i click the form button outlooks displayed the email windows with body,email and subject to be sent for the first email, and returns error for the next. – Arepeel Sep 03 '19 at 08:54
The remarks class, as the name goes.. is only to remark each row .. as a condition to check if the email is sent, or indeed just closed after it is displayed – Arepeel Sep 03 '19 at 08:58
Thank You So much btw for cleaning my code :).. I appreciate it :).. Im new to vba so i may make noobs mistake XD – Arepeel Sep 03 '19 at 09:00
Hi L8n, I figure that the problem is outlook is not opened everytime i run the code.. so i added Shell ("OUTLOOK") to my code to run first before creating the emails and it worked!.. but the problem now is Outlooks apps now open and pops out everytime when i want it to run on background.. do you have any idea on how to do this? – Arepeel Sep 03 '19 at 09:11
This is how i add the new line Dim oOutlook As Object On Error Resume Next Set oOutlook = GetObject(, "Outlook.Application") On Error GoTo 0 If oOutlook Is Nothing Then Shell ("OUTLOOK") End If – Arepeel Sep 03 '19 at 09:18
I just noticed, that you are using a late binding: https://stackoverflow.com/questions/47056390/how-to-do-late-binding-in-vba It would probably be preferable to use early binding and setting the proper reference. I'll update the answer later today when i have the time. – L8n Sep 03 '19 at 09:55
tq L8n, any help to improve it will be a pleasure – Arepeel Sep 03 '19 at 10:08

Runtime Error, VBA EXCEL for email automation

1 Answers1

Linked