Send email (with VBA) and update "status"

[Mehmud Khan]

Hello,

Good day!

Need helps in below;

I want to send multiple emails (via VBA) and simultaneously I want to update the status of email.
>In case, if email drafted but not sent, then status should be "Not Sent"
>In case, If email sent then status should be "Sent"


Please help, humble request from members and ninjas.




Thanks and Regards
Mehmud

 

[Mehmud Khan]

@vletm
@Hui
@Luke M
Please help me Ninjas....
Most of my query ares resolved by you...
please help in this also...
please please please

 

[Luke M]

Edit: Typo

Sub SendEmails()
    Dim lastRow As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim i As Long

    Set OutApp = CreateObject("Outlook.Application")
  

    'How much data is there?
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
    'Prevent screen flicker
    Application.ScreenUpdating = False
  
    For i = 2 To lastRow
        If UCase(Cells(i, "E").Value) <> "SENT" Then
            Set OutMail = OutApp.CreateItem(0)
          
            With OutMail
                .to = Cells(i, "A").Value
                .cc = Cells(i, "B").Value
                .Subject = Cells(i, "C").Value
                .body = Cells(i, "D").Value
              
                Cells(i, "E").Value = "Sent"
              
                .send   'or use .Display
            End With
          
            Set OutMail = Nothing
        End If
    Next i
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

 

[Mehmud Khan]

Hi Luke,

Thanks for your reply,

Getting below error;

 

[Marc L]

Hi, just try without the leading dot …​

​

 

[Luke M]

Sorry about that. Marc is correct, remove the leading period.

 

[Mehmud Khan]

Dear Luke and Marc,


Thanks for your supports and replies.

Your solution works.

All good, only 1 issue.

Let me explain you step by step;

step1.
I cleared the column "E" first.

Step2
I go to VBA then run the code

step 3
VBA trying to send the first email and Outlook was giving pop alert for 5 second

HERE, I Need help

1. At step 3, if I click on Allow then outlook will send the email and status on column E will be updated "Sent"

2. If I click on Deny then system gave me some error (refer below screenshot) but still email status is updated as "Sent"



WHAT I NEED HELP FOR;

if I deny then system should update in column as "Not Sent"
In simple words, If email actually sent then only status should update else not.



I hope, I am able to explain.


Thanks and Regards,
-Khan