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: 9

KriCo

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,907
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

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

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

ADVERTISEMENT

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

Well-known Member
Joined
Feb 6, 2012
Messages
1,907
Maybe best to start a new thread for this issue. From what I can see that line looks to be correct.
 

KriCo

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

ADVERTISEMENT

I started a new thread ...
 

KriCo

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,365
Members
418,499
Latest member
mbcmel

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
Top