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]

It does run. But the code only completes at the set time

 

[KriCo]

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]

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.

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]

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]

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

. Attachments.add ActiveWorkbook.FullName

To perhaps

. Attachments.add "your file name.pdf"

 

[KriCo]

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]

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.

.Body = "Hi there"

 

[KriCo]

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)

404 File Not Found - Jumpshare

The file you are looking for does not exist.

jmp.sh

Normally the link should work

 

[KriCo]

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]

Easy fix then just change to

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?