Daily Excel sheet report to be emailed

[DuneSage]

Hello All , I have taken interest in excel VBA recently and am trying to learn and apply some optimizations to my daily excel workflow sheets.

I have an excel sheet which is sent daily at a specific time. I would like to have it sent every day except on weekends with the same email recipients and text. The context of the excel doesn't change

I feel there is a macros where to automate this mundane task.

Appreciate the support and thank you.

 

[Monty]

Hello DuneSage

Certainly! You can use VBA to create a macro that automatically sends an email with the Excel sheet as an attachment on weekdays. Here's a basic example to get you started. Please replace placeholders with your actual values:

Sub SendDailyEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strRecipients As String
    Dim strSubject As String
    Dim strBody As String
    Dim strAttachmentPath As String
    
    ' Set email recipients
    strRecipients = "recipient1@example.com; recipient2@example.com"
    
    ' Set email subject and body
    strSubject = "Daily Excel Report"
    strBody = "Dear Team, Please find the daily report attached."
    
    ' Set the file path of your Excel sheet
    strAttachmentPath = "C:\Path\To\Your\File.xlsx"
    
    ' Check if it's a weekday (Monday to Friday)
    If Weekday(Now, vbMonday) >= 1 And Weekday(Now, vbMonday) <= 5 Then
        ' Create Outlook application object
        Set OutApp = CreateObject("Outlook.Application")
        
        ' Create a new email
        Set OutMail = OutApp.CreateItem(0)
        
        ' Set email properties
        With OutMail
            .To = strRecipients
            .Subject = strSubject
            .Body = strBody
            .Attachments.Add strAttachmentPath
            ' Uncomment the line below if you want to display the email before sending
            '.Display
            ' Uncomment the line below to send the email automatically
            '.Send
        End With
        
        ' Release Outlook objects
        Set OutMail = Nothing
        Set OutApp = Nothing
    End If
End Sub

To use this code:

1. Press `Alt` + `F11` to open the VBA editor.
2. Insert a new module: `Insert` > `Module`.
3. Copy and paste the code into the module.
4. Modify the placeholders (recipient emails, subject, body, and file path).
5. Save and close the VBA editor.
6. You can run the macro manually by pressing `F5` or set it to run automatically using Task Scheduler.

Remember to adjust the email content and file path according to your specific needs. Additionally, ensure that your Outlook security settings allow for programmatic access.