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

 

[KriCo]

If i run the macro manually, it is still the same ... It exports only the worksheet where i'm on when i run the macro.
So now i'm gonna test it with the schedule that i set up. I guess that it will be ok when i let it start automatically

Is there a possibility that you can make a exact copy of your excel file ?
So when you enter new data in the original, it automatically change it in the copy ?
I want to do this, just for security. So when something happens, i still have the original one

 

[mrshl9898]

Not really automatically. There are macros that run off events however, like the workbook open one you are using. Examples are workbook close, on pivot refresh, cell change.

 

[KriCo]

I still run into issues ... i'm getting frustrated

The file that is shared with you is my test file. When i insert the macro into the original one, i have to save it as "macro-enabled". So i must create a new file.
And here's my problem now : I have a form for the data input and a flow (power automate) to insert everything into an excel file.
This means that when i save my file into a "macro-enabled", nothing works anymore (form, flow).

For this issue it is necessary that everything stay the same, but i have to link to files. So when something is inserted or updated in the original one, automatically the data is copied to the one with the macro.
I can set up the entire form and flow, but all of my data will be lost.

Is there a solution for this, or do i just need to setup everything up again, but using an excel file with macros enabled ?

 

[KriCo]

I finally found it !

I have a second file running... When the original one gets his new entry, after 1 minute, the second one updates automatically.
Unfortunately, i get several errors while running the macro, so now this is the new issue ...
I already changed several things. I will try to fix the errors.

I will let you know

 

[KriCo]

For as far as my knowledge goes about VBA, i tried everything ...

This is the code that is currently running:

Private Sub Workbook_Open()
Sheets("Dag Overzicht").Select
Application.OnTime TimeValue("08:00:00"), "Overzicht"
End Sub


Sub Overzicht()

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

myDay = Day(Now() - 1)

With ActiveWorkbook.SlicerCaches("Slicer_Dag1")
cnt = .SlicerItems.Count ----> this line currently indicates an error
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)

strbody = "Hej," & vbNewLine & vbNewLine & _
"" & vbNewLine & _
"In bijlage kunnen jullie het dagoverzicht terugvinden van de fouten zonder boeking" & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
"Met vriendelijke groeten," & vbNewLine & _
"With kind regards," & vbNewLine & _
"" & vbNewLine & _

Dim saveLocation As String

Application.DisplayAlerts = False
saveLocation = "U:\Fouten zonder boeking\Dagoverzicht.pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

Application.DisplayAlerts = True

On Error Resume Next
With OutMail
.to = "a.b@c.com"
.CC = ""
.BCC = ""
.Subject = "Dagoverzicht van de fouten zonder boeking"
.Body = strbody
.Attachments.Add "U:\Fouten zonder boeking\Dagoverzicht.pdf"
.Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub



All the names are like they should be. Name of the slicer is "slicer dag1".

I allready searched on the internet what the error could be, but i didn't find it.

In the test file, everything worked just fine, but now it doesn't do it anymore. Everything is the same (names of the sheets), only the name of the macro and slicer are different.
But i changed this in the macro.

Could you please help me with this ?

 

[mrshl9898]

Maybe best to start a new thread for this issue. From what I can see that line looks to be correct.

 

[KriCo]

I started a new thread ...

 

[KriCo]

I started all over again.
So new file (new copy of my original file with new links between them), but same macro...

And strange enough, the error is gone and everything works just fine now.

Thx for your help !

 

[KriCo]

I'm sorry, but i got everything to work
The mail is sent, everything runs automatic, but for some reason it's the entire workbook that is sent. Not only the worksheet with the slicer en not only yesterday. It's still the entire date range.

Could you please help me with this ?

 

[KriCo]

I'm sorry, but i got everything to work
The mail is sent, everything runs automatic, but for some reason it's the entire workbook that is sent. Not only the worksheet with the slicer en not only yesterday. It's still the entire date range.

Could you please help me with this ?

Update:

Now it sends the worksheet with the slicer so that's good, but for some reason it sends the results of the entire month and not just yesterday

Do you have any idea?