How to create and email an Excel Copy automatically (daily or real time)

[TheDiego]

Hey guys,

I am new here, and not an excel "expert" whatsoever, so I don't really have much knowledge of the code itself.

I currently have a master file that is saved to our company's One Drive. This file contains 8 sheets in the workbook and at maximum goes from column A to column V.

At the moment at work we are trying to save time, since we have to create a copy and then send this in an email to the relevant people. However, we always send a copy as it is an important document and we can't send the master file. I would like to automate this process so that it goes through it at the end of the day (at around 5 p.m).

This is what I would like to achieve:


1. Set it so that it automatically create a copy of the master file (If possible whenever we make a change, or if it's easier just create it at a specific time).
2. Then make it create and send an email containing the copy of the file to specific contacts.

I realize this might not be possible all in one go. But I would like to know if there is a way you guys could help me automate this.

Thank you in advance!

 

[Logit]

In a routine module :

Option Explicit

Sub Mail_workbook_Outlook()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim sndEmail As String
    Dim toEmail As String
    Dim ccEmail As String
    Dim bccEmail As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
  

    On Error Resume Next
    With OutMail
        .To = "YourBossEmail@yahoo.com"
        .CC = ""
        .BCC = ""
        .Subject = "Daily Report"
        .Body = "Daily Report Attached"
        .Attachments.Add ("C:\Users\jimga\Desktop\WORKS - Email w Attachment Set Time Daily.xlsm") 'attaches this workbook to email
        .Display                           '<-- .Display will show the email first for review
        '.Send                               '<-- .Send will auto send email without review
                                  
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

.


In the ThisWorkbook module :

Option Explicit

Private Sub Workbook_Open()
     Application.OnTime TimeValue("17:00:00"), "Mail_workbook_Outlook"
End Sub

Workbook MUST be open if you intend for it to automatically send the email.

 

[TheDiego]

Thank you Logit!

I appreciate your help.

Just a couple more questions:

1. Is there a way to make it create a copy and send that one? Since I don't want to send the master file. Maybe have a copy that mirrors the master file (meaning it updates with every change). If that's even possible, how would I go about doing that?

2. When it comes to the file location in the code, I have that file in OneDrive Sharepoint, so it has a link when I look for the file location. Is there a workaround for this or can I just put the link as the location?

Thank you in advance/

 

[Logit]

1. Is there a way to make it create a copy and send that one? Since I don't want to send the master file. Maybe have a copy that mirrors the master file (meaning it updates with every change). If that's even possible, how would I go about doing that?

2. When it comes to the file location in the code, I have that file in OneDrive Sharepoint, so it has a link when I look for the file location. Is there a workaround for this or can I just put the link as the location?

#2: Try using the link and see what happens.

#1: You can provide the path to any file you want attached to the email. It doesn't make any difference to the code ... it will work no matter what. Of course, once a file is sent it won't automatically update on the other end. You'll need to send another copy with updates.

 

[TheDiego]

Thank you very much!

I ended up using ActiveWorkbook.FullName to make it work like I wanted it to. Much easier for me that way. It's working exactly how I need it to. Thank you for all your help!

 

[Logit]

You are welcome.