Automatic Daily results and email

KriCo

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hi,

I made a form for entering data.
I used "Power Automate", so all of the entered data is stored in an excel file.

I created some extra worksheets for filtering data, using a pivottable, pivotchart & slicers.

And hereby my problem.

My company wants a report on a daily base. I can do this manually by selecting the date using the slicer on the sheet "Dag Overzicht". I want this to go automatic.
And after that i want to mail the results to a mailing list. Also automatic. For example: everyday at 08:00 AM.

I hope that it is possible...
 

Attachments

  • Capture.JPG
    Capture.JPG
    239.1 KB · Views: 68
And all together.



VBA Code:
Private Sub Workbook_Open( )
     Application.OnTime TimeValue("08:00:00"), "Mail_workbook_Outlook_1"
End Sub


Sub Mail_workbook_Outlook_1()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim myDay As String

myDay = Day(Now() - 1)

With ActiveWorkbook.SlicerCaches("Slicer_Dag")
    cnt = .SlicerItems.Count
    For i = 1 To cnt
        If .SlicerItems(i).Name <> myDay Then
            .SlicerItems(i).Selected = False
        Else
            .SlicerItems(i).Selected = True
        End If
    Next
End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

ActiveWorkbook.SaveAs "C:\WorkbookName.xls"   ''UPDATE THIS

    On Error Resume Next
    With OutMail
        .to = "ron@debruin.nl"  ''AND THIS
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        .Send   'or use .Display     ''CHOOSE DISPLAY OR SEND DEPENDING WHAT YOU WANT TO DO, USE DISPLAY AT LEAST UNTIL YOU'VE TESTED IT.
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
*Update to deal with weekends.

VBA Code:
Sub Mail_workbook_Outlook_1()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim myDay As String
  
If Weekday(Now()) = 2 Then
    myDay = Day(Now() - 3)
Else
    myDay = Day(Now() - 1)
End If

If Weekday(Now()) = 1 Then
    myDay = Day(Now() - 3)
ElseIf Weekday(Now()) = 7 Then
    myDay = Day(Now() - 2)
Else
    myDay = Day(Now() - 1)
End If


With ActiveWorkbook.SlicerCaches("Slicer_Dag")
    cnt = .SlicerItems.Count
    For i = 1 To cnt
        If .SlicerItems(i).Name <> myDay Then
            .SlicerItems(i).Selected = False
        Else
            .SlicerItems(i).Selected = True
        End If
    Next
End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

ActiveWorkbook.SaveAs "C:\WorkbookName.xls"   ''UPDATE THIS

    On Error Resume Next
    With OutMail
        .to = "ron@debruin.nl"  ''AND THIS
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        .Send   'or use .Display     ''CHOOSE DISPLAY OR SEND DEPENDING WHAT YOU WANT TO DO, USE DISPLAY AT LEAST UNTIL YOU'VE TESTED IT.
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
Upvote 0
thank you very much !!! your the best !
It works just fine !

Is it possible to run it automatically (not opening excel at all) and save it in to a new file (and overwrite the same file everyday (for example: "dagoverzicht.pdf")) ?
When that's possible i can run a flow (power automate) to send the mails on a daily base
 
Upvote 0
thank you very much !!! your the best !
It works just fine !

Is it possible to run it automatically (not opening excel at all) and save it in to a new file (and overwrite the same file everyday (for example: "dagoverzicht.pdf")) ?
When that's possible i can run a flow (power automate) to send the mails on a daily base
Sorry,

I typed this message before i saw the second page ?
I'm gonna try this and let you know...

That's crap that excel still has to be open, to let this macro run. Because that's the whole idea behind my problem.
When it runs automatically, nobody has to open, select and send the results... Now, someone has still got to open it

When i tried your solution, i'm gonna test it with windows scheduler. Maybe when i program it with the scheduler, excel opens automatically and also this macro...

Maybe another question :
Does this macro runs automatically when excel opens ?
We also work on saturday and sunday, so the weekends can count as workdays. so i'm gonna use your solution which you posted before dealing with weekends ?

I'm gonna test it and let you know !
But really, thx a lot !!!
 
Upvote 0
Everything works ...

Is it possible to send the mail automatically ?
The file is saved, outlook opens, but you have to send the mail manually.
 
Upvote 0
The workbook open sub will run automatically when the file opens.

As for the sending you can Display or Send or both. I'm assuming it's on Display if that's occurring

Just change that little bit I made the comments on.

VBA Code:
.Send   'or use .Display     ''CHOOSE DISPLAY OR SEND DEPENDING WHAT YOU WANT TO DO, USE DISPLAY AT LEAST UNTIL YOU'VE TESTED IT.
 
Upvote 0
that's right, i changed it into "Display" but forgot to change it back to "Send" ...

i also set up a schedule for tomorrow... So fingerscrossed that everything works
 
Upvote 0
1 final question :

does the macro runs everytime the file opens ? because that's not good. everyone who opens it, will send an email.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top