Sub to send emails to my clients automatically

[martinargimon]

Hi there,
The below screenshot shows in a nutshell the code. I do get an error '438' , though", which i battle to understand what could be the problem?
I have attached the workbook for your review
kinldy indicate what i am doing wrong ?
Many Thanks . Kind Regards. Martin

 

[Logit]

The following version corrects the errors in your original code. There were a few spelling errors originally. I edited the code to fit your desired outcome .

Sub SendEmail()
    Dim OutlookApp As Object
    Dim MItem As Object
    Dim cell As Range
    Dim email_ As String
    Dim cc_ As String
    Dim subject_ As String
    Dim body_ As String
    Dim R As Range

    'Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")

    'Loop through the rows
    For Each cell In Sheet15.Columns("O").Cells.SpecialCells(xlCellTypeConstants)
        
        Set R = cell.EntireRow

        email_ = cell.Value
        subject_ = "Payment Due Date Reminder for your Lease No. " & R.Cells(1, 1).Value
        body_ = "Dear " & R.Cells(1, 13).Value & " " & R.Cells(1, 14).Value & vbNewLine & vbNewLine _
                & "This is a friendly reminder to pay your monthly rental in time to avoid penalty Interest Charges" & vbNewLine _
                & "Many Thanks" & vbNewLine _
                & "Kind regards" & vbNewLine & vbNewLine _
                & "Fortune Joseph" & vbNewLine & "CMA Realty Properties SA"
        cc_ = "fortune@grupo-cma.com"

        'Create Mail Item and send it
        Set MItem = OutlookApp.CreateItem(0)
        With MItem
            .To = email_
            .cc = cc_
            .Subject = subject_
            .Body = body_
            .Display        'Comment out this line and uncomment the next when ready to auto-email without review.
            '.Send
        End With
    Next

    Set OutlookApp = Nothing
    Set MItem = Nothing
End Sub

 

[martinargimon]

Thanks Logit
I have just tried your code and it works 100% . Many Thanks for fixing my problem.
Kind Regards
Martin

 

[martinargimon]

Hi Logit
One last question please.
The subroutine works perfectly well when i use the 'OLD' Outlook, but it does not when I have my 'NEW' Outlook open.
In fact i have migrated to the NEW Outlook. Do I have to change the code to accommodate this ?
Please let me know
Many Thanks once again. Rgds Martin

 

[Logit]

Microsoft found it in their ultimate wisdom to remove VBA from the new Outlook. So ... you cannot manipulate the new Outlook using
VBA. Sorry ....

 

[martinargimon]

Wow, this is pathetic !. They want to force us to use 'power automate" at any cost.
The fact is that Power Automate makes it very difficult to write 'flows" that can incorporate all of these features. Don't u thinK.?
Power automate is still not widely used.
Based on your experience and skills can someone replicate this subroutine in 'Power Automate" easily ???
Let me know your comments . THX

 

[Logit]

I am not familiar with Power Automate". Sorry

Maybe someone else has input ?

 

[martinargimon]

Sure. No Problem.
Thx a lot for your help.
Kind Regards
Martin