Md. Abu Sina Ibne Albaruni

Dear VSP

Thank you very much for your question. I appreciate your interest in our article. I will show you the step-by-step process to create a macro according to your preferences. Also, you will be able to send automated emails on the due date without running the code.

● First, create the workbook. I am creating a sample workbook that has Message, Date, Status of work, and Email addresses in specific positions that you have mentioned.

● Select your entire dataset.
● Go to the Insert tab >> Table.
● Check the My table has headers option.
● Press OK.

● Sort your data according to your Email address.

● Now go to the Developer tab >> Visual Basic.

● Go to ThisWorkbook and paste the following code into the VBA Macro Editor.
● Save the file as Excel Macro-Enabled Workbook (.xlsm) file.

Private Sub Workbook_Open()

    'variable declaration
    Dim myOutlookApp As Object
    Dim myOutlookMail As Object
    Dim i As Long
    Dim Receiver As String
    Dim EmailBody As String
    Dim WS As Worksheet
    
    'create outlook application object
    Set myOutlookApp = CreateObject("Outlook.Application")
    'set worksheet
    Set WS = ActiveSheet
    
    'loop through rows
    'starting from the second row assuming row 1 is the Header
    For i = 2 To WS.Range("C" & Rows.Count).End(xlUp).Row
        'check if the task is assigned today
        If WS.Range("C" & i) = Date Then
        'check status
        If WS.Range("E" & i) = "In progress" Or WS.Range("E" & i) = "Outstanding" Then
            'check if the email address is same as the previous one
            If WS.Range("G" & i) = Receiver Then
                'merge email bodies for same email addresses
                EmailBody = EmailBody & vbCrLf & WS.Range("A" & i)
            Else
                'if the email address is different, send the previous email
                'create a new email
                If Receiver <> "" Then
                    'create a new mail item
                    Set myOutlookMail = myOutlookApp.CreateItem(0)
                    
                    'compose the email
                    With myOutlookMail
                        'set the receiver email address
                        .To = Receiver
                        'set the subject of the email
                        .Subject = "Task Summary"
                        'set the body of the email
                        .body = EmailBody
                        'send email
                        .Send
                    End With
                    
                    'release the mail object
                    Set myOutlookMail = Nothing
                End If
                
                'set the receiver and body for the new email
                Receiver = WS.Range("G" & i)
                EmailBody = WS.Range("A" & i)
            End If
        End If
        End If
    Next i
    
    'send the last email (if exists)
    If Receiver <> "" Then
        Set myOutlookMail = myOutlookApp.CreateItem(0)
        
        With myOutlookMail
            .To = Receiver
            .Subject = "Task Summary"
            .body = EmailBody
            .Send
        End With

        Set myOutlookMail = Nothing
    End If
    
    'release the Outlook application object
    Set myOutlookApp = Nothing
    
    'display a confirmation message
    MsgBox "The emails have been sent successfully from your outlook"

End Sub

● Now, to automatically send emails, type Run in your Start Menu and open the Run dialog box.
● Paste the following path there and press OK.

%AppData%\Microsoft\Windows\Start Menu\Programs\Startup

● Place your Excel file in the Startup folder.

Now whenever you open your PC, the VBA code of your Excel file will be automatically executed. Emails will be sent to the given email addresses according to the criteria you mentioned.

Notes:
● Stay logged in to your outlook application. We are using the OUTLOOK Microsoft 365 Office application.
● The provided code sends an email for the current date. You can customize it for any date.
● Make sure that your active worksheet contains the sorted table and save the file accordingly.

You can download the sample Excel file from here.
Send Emails.xlsm

If you have any more questions, please let us know in the comment sections.

Regards,
Md. Abu Sina Ibne Albaruni
Team ExcelDemy

Dear NIRMAL

Thank you for taking the time to read this article. I can see that you are facing a problem while executing the code in method 3. I guess It is Run-time error ‘1004’.

Kindly note that you need to modify the file path according to the folder where you are storing your pictures. You also have to specify the type of the image (jpg, jpeg, png, etc.) correctly. So, you should change the LocationPic variable in the code accordingly.

For your convenience, I am attaching a photo indicating the exact portions of the code that you need to re-write.

If you have any more questions, please let us know in the comments.

Regards
Md. Abu Sina Ibne Albaruni
Team ExcelDemy

Dear KAZEM

Thank you for taking the time to read this article. I see that you are facing a problem while executing the code. It is the Run-time error ‘1004’. There are several possible reasons for this error. In your case, I believe you are having this issue because the file path you specified is incorrect or unavailable. Please note that you need to modify the file path according to the destination where you want to save the PDF file. You have to change the file path portion in the Filename variable.

For your convenience, I am attaching a photo indicating the exact portion of the code that you need to re-write.

If you have any more questions, please let us know in the comments.

Regards
Md. Abu Sina Ibne Albaruni
Team ExcelDemy

Hello Z L

Thank you very much for your response. We have modified this article and introduced some new statistical concepts and functions. Would you please go through it again?

If you have any more questions, let us know in the comment section.

Thanks

Md. Abu Sina Ibne Albaruni
Team ExcelDemy

Dear Datla Srinivas,

We have sent you an email. Please find the attached documents in it.

Best wishes
Md. Abu Sina Ibne Albaruni
Team ExcelDemy