Automatic Daily results and email

[KriCo]

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...

 

[mrshl9898]

Here's a bunch of Macros to create emails.

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

Try recording a macro and select the date, paste the code here if you need help automating that.

In order to do this automatically everyday you need wither the book open or another open with code like the below. It will need to be in the Workbook module. When the book opens it runs and waits until a set time to trigger a macro (your change date and email code).

Private Sub Workbook_Open( )
     Application.OnTime TimeValue("08:00:00"), "MyMacro"
End Sub

 

[KriCo]

Hey,

Thx for the answer.

My main issue is first to create a daily overview on the worksheet like the picture in my post.
So today (at for example 8:00 AM), i want excel to make the overview from yesterday. If its possible, i would like this daily overview to be saved in a new file. This overview needs to be mailed on a daily base to a certain mailinglist.
The mail issue can also be solved using power automate, but like i said the main issue is the daily overview.
I'm a total noob with macros, that's why i'm here. On the net i didn't found anything what could bring me the solution for my problem (maybe there isn't any).
I sure hope that the experts here can help me...

Kris

 

[mrshl9898]

Understood. Here's the first code off the link. It will attach the active workbook

Sub Mail_workbook_Outlook_1()
'Working in Excel 2000-2016
'This example send the last saved version of the Activeworkbook
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
    With OutMail
        .to = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

This will save the workbook as something else.

ActiveWorkbook.SaveAs "C:\WorkbookName.xls"

Together with the On Time most of it is there. And i'm happy to put it together if you're unsure.

What is missing is for you to record a macro doing your daily update and paste it in here. Just hit record, do your thing and hit stop. Have you already enabled the Developer tab?

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

    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

 

[KriCo]

Hey,

Thx for the reply.

For as far i understand the VBA. This is to send the last updated version of my file thru mail.
That's not the problem. I only want to send the results of 1 day. And this on a daily base.

For example :

Tomorrow (29 may 2021) at 8:00AM i want to send the overview of today (28 may 2021), so in principle it is day -1). And this in the layout like i attached in my original post. This is the layout of my worksheet with the pivot table, pivot chart...

I also tried to record a macro, but in this context it isn't possible. If i select a date, it will always select the same date and not like i want (day-1)

i hope that it's possible

 

[mrshl9898]

The challenge is not knowing what your slicer is called, or what format to put the date in, hence wanting to see it recorded. Easiest to see the code and make that small tweek for you.

 

[KriCo]

The challenge is not knowing what your slicer is called, or what format to put the date in, hence wanting to see it recorded. Easiest to see the code and make that small tweek for you.

I can send you a copy of the entire file. But what's the easiest way to do so ? Because this forum won't let you attach a file, only a mini-sheet. And for this mini-sheet i must install a add-in. I don't have permission (company) to install any add-ins or programs...

 

[mrshl9898]

Probably just a file sharing website. Upload the file and post a link

 

[KriCo]

Probably just a file sharing website. Upload the file and post a link

I uploaded the file on jumpshare.
If everything works, you can use this link : Test.xlsx

 

[mrshl9898]

To select yesterday

Sub SelectPreviousDay()

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

End Sub