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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

KriCo

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
I tested it and came up with the following problems:

- Because it's the same file each day, you have to answer the question if the file may be overwriten.
- When you open that file, everything is visible (so not only "yesterday")

Is it possible to bypass the question and have the file automatically be overwritten?
Is it possible to save only the part with the slicer ? And instead of an excel file maybe in a pdf
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,907
Application.DisplayAlerts = False turns the box off
Application.DisplayAlerts = True turns them on.

Just add the lines before and after the save like the below.

Replace the workbook.save with the following and update the location.

VBA Code:
Dim saveLocation As String

Application.DisplayAlerts = False
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"

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

Application.DisplayAlerts = True
 

KriCo

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

probably it's my mistake (because i'm a total noob in this), but the saved file (pdf ) wont attach in the mail.

Sorry that i'm still asking so much
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,907

ADVERTISEMENT

Not a worry, sorry but I haven't been near a Pc so it's drips and drabs of information.

Totally forgot about the attachment.

Change
VBA Code:
. Attachments.add ActiveWorkbook.FullName

To perhaps
VBA Code:
. Attachments.add "your file name.pdf"
 

KriCo

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
I tried it and it works. The pdf file is attached in the mail. And no question to overwrite...

But unfortunately he same problem came up again. The entire workbook is exported as pdf. So not only the worksheet with the pivot table, pivot chart and slicer and not only "yesterday".

How can you make an entire mail setup ?
For example, this is the standard mail setup what will be send to the managers :

Hej,

In bijlage kunnen jullie het dagoverzicht terugvinden van de fouten zonder boeking.

Met vriendelijke groeten,
With kind regards,

Kris Coppin



I tried to put it like this, but it wont work with the spaces...
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,907

ADVERTISEMENT

Strange. Can you share the file again please. I'll need to look at it from a Pc tomorrow. It should be exporting the sheet, not the book.

As for the email body.

Change Hi there to your message. Keeping the quotation marks at the start and end of the whole message.

VBA Code:
.Body = "Hi there"
 

KriCo

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
the mail setup is working now. I added "strbody". Now that is totally like i wanted.
So the only thing that's left is that the entire workbook is exported to a pdf-file en not the worksheet with the slicer.

This is the link to the file again (everything is in the macro is with the file location at my company)




Normally the link should work
 

KriCo

New Member
Joined
May 26, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
I think that i found something.

When i have the worksheet open with all the data and i run the macro manually, it shows the entire data entries and not only "yesterday".
When i have the worksheet with the slicer open "Dag Overzicht" and i run the macro manually, it does what i want.

Excel should open with the selected date "yesterday" and directly has to open the worksheet with the slicer "Dag Overzicht"

Like is said, "i think i found something".
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,907
Easy fix then just change to

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

*Should aim to not select a sheet in VBA, but for simplicitys sake this will do the job.

Does this fix the PDF issue also?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,355
Members
418,498
Latest member
nattynat

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