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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,907
Here's a bunch of Macros to create emails.


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

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

KriCo

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

Well-known Member
Joined
Feb 6, 2012
Messages
1,907
Understood. Here's the first code off the link. It will attach the active workbook

VBA Code:
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.

VBA Code:
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?


1622148144823.png




VBA Code:
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
 
Last edited:

KriCo

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

ADVERTISEMENT

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

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

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

ADVERTISEMENT

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

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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,538
Messages
5,659,384
Members
418,500
Latest member
Guru Prasad S

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