VBA - Open, Refresh, Send Attachment in Email, Save, and Close

B

bolwahnn

New Member
Joined
Apr 29, 2011
Messages
40
Hey Guys,
I'm looking for code that will allow me to set up a Task Scheduler that will open my excel file, wait about 10 seconds for the data to refresh (the data automatically refreshes upon open), send out email as an attachment, and then save and close it self.

I believe the task scheduler will open the file for me when scheduled and the data will refresh on its own.... I'm thinking the code I need is to wait for the refresh to finish and then send the email as an attachment, save and close.....

P.S. The above should only work when the task scheduler opens the file - i.e. when the file is sent out, I dont want the people receiving the file opening it up and then the process happens again

Thanks,
Zeke
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
This should do the trick. It saves the active workbook as a copy, which then saves as an xlsx (to prevent macros from running when the recipient opens the attachment. It then composes the email and adds the temporary xlsx as an attachment. When finished, it deletes the temporary files. All you need to do is create the macro to save the document and close itself.

Code: 
Sub WBtoEmail()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim wb3 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object


    Set wb1 = ActiveWorkbook
    If Val(Application.Version) >= 12 Then
        If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
                   "be no VBA code in the file you send. Save the" & vbNewLine & _
                   "file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
            Exit Sub
        End If
    End If


    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With


    ' Make a copy of the file.
    'Then save the new file as xlsx
    'SaveCopyAs only saves as the origin file type
    'After the copy is made, then we can save it as an xlsx,
    'as to prevent macros from running when the recipient opens the file
    ' If you want to change the file name then change only TempFileName variable.
    TempFilePath = Environ$("temp") & "\"
    TempFileName = wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))


    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
    wb2.SaveAs TempFilePath & TempFileName & ".xlsx", FileFormat:=51
    Set wb3 = Workbooks.Open(TempFilePath & TempFileName & ".xlsx")
    'Cleanup
    wb2.Close SaveChanges:=False
    Kill TempFilePath & TempFileName & FileExtStr


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


    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ""
        .Body = ""
        .Attachments.Add TempFilePath & TempFileName & ".xlsx"
        ' You can add other files by uncommenting the following line.
        '.Attachments.Add ("C:\test.txt")
        ' In place of the following statement, you can use ".Send" to
        ' send the mail.
        .Display
        '.Send
    End With
    On Error GoTo 0
    
    'Cleanup
    'wb3.Close SaveChanges:=False
    Kill TempFilePath & TempFileName & ".xlsx"


    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub
 
Upvote 0
kpasa said:
This should do the trick. It saves the active workbook as a copy, which then saves as an xlsx (to prevent macros from running when the recipient opens the attachment. It then composes the email and adds the temporary xlsx as an attachment. When finished, it deletes the temporary files. All you need to do is create the macro to save the document and close itself.

Code: 
Sub WBtoEmail()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim wb3 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object


    Set wb1 = ActiveWorkbook
    If Val(Application.Version) >= 12 Then
        If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
                   "be no VBA code in the file you send. Save the" & vbNewLine & _
                   "file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
            Exit Sub
        End If
    End If


    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With


    ' Make a copy of the file.
    'Then save the new file as xlsx
    'SaveCopyAs only saves as the origin file type
    'After the copy is made, then we can save it as an xlsx,
    'as to prevent macros from running when the recipient opens the file
    ' If you want to change the file name then change only TempFileName variable.
    TempFilePath = Environ$("temp") & "\"
    TempFileName = wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))


    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
    wb2.SaveAs TempFilePath & TempFileName & ".xlsx", FileFormat:=51
    Set wb3 = Workbooks.Open(TempFilePath & TempFileName & ".xlsx")
    'Cleanup
    wb2.Close SaveChanges:=False
    Kill TempFilePath & TempFileName & FileExtStr


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


    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ""
        .Body = ""
        .Attachments.Add TempFilePath & TempFileName & ".xlsx"
        ' You can add other files by uncommenting the following line.
        '.Attachments.Add ("C:\test.txt")
        ' In place of the following statement, you can use ".Send" to
        ' send the mail.
        .Display
        '.Send
    End With
    On Error GoTo 0
    
    'Cleanup
    'wb3.Close SaveChanges:=False
    Kill TempFilePath & TempFileName & ".xlsx"


    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub
Click to expand...


Thanks for the response, but I cant get this to work.... So here's what I'm doing, what I expect to happen and what is happening.....
What I'm doing: I'm currently going into the workbook (w/no VBA). I'm going into Visual Basic, Doubling clicking on "ThisWorkbook" and copying and pasting the above code. I'm then saving the workbook as a Macro enabled workbook and closing the file.

What I'm expecting to happen: With the above completed - I'm now opening the file (which Windows Task Scheduler will do in the future, but I'm doing manually to test the code) and I'm expecting the Data to refresh (which it does because the connection has been set up to "refresh upon open"),refresh pivot (which I have a code that will refresh when data is updated) and then send out email with attachment and then close the file.

What is happening: When opening the file - Nothing happens..... I went into code to try and run manually and I'm getting a runtime error 1004

Any ideas what I'm doing wrong here?
 
Upvote 0
bolwahnn said:
What is happening: When opening the file - Nothing happens..... I went into code to try and run manually and I'm getting a runtime error 1004

Any ideas what I'm doing wrong here?
Click to expand...

Feel free to DM me. I personally would like to look at the code. In short, nothing happens automatically because you need to put code inside the Workbook_Open() sub (or call it from the _open sub)
 
Upvote 0
kpasa said:
This should do the trick. It saves the active workbook as a copy, which then saves as an xlsx (to prevent macros from running when the recipient opens the attachment. It then composes the email and adds the temporary xlsx as an attachment. When finished, it deletes the temporary files. All you need to do is create the macro to save the document and close itself.

Code: 
Sub WBtoEmail()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim wb3 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object


    Set wb1 = ActiveWorkbook
    If Val(Application.Version) >= 12 Then
        If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
                   "be no VBA code in the file you send. Save the" & vbNewLine & _
                   "file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
            Exit Sub
        End If
    End If


    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With


    ' Make a copy of the file.
    'Then save the new file as xlsx
    'SaveCopyAs only saves as the origin file type
    'After the copy is made, then we can save it as an xlsx,
    'as to prevent macros from running when the recipient opens the file
    ' If you want to change the file name then change only TempFileName variable.
    TempFilePath = Environ$("temp") & "\"
    TempFileName = wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))


    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
    wb2.SaveAs TempFilePath & TempFileName & ".xlsx", FileFormat:=51
    Set wb3 = Workbooks.Open(TempFilePath & TempFileName & ".xlsx")
    'Cleanup
    wb2.Close SaveChanges:=False
    Kill TempFilePath & TempFileName & FileExtStr


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


    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ""
        .Body = ""
        .Attachments.Add TempFilePath & TempFileName & ".xlsx"
        ' You can add other files by uncommenting the following line.
        '.Attachments.Add ("C:\test.txt")
        ' In place of the following statement, you can use ".Send" to
        ' send the mail.
        .Display
        '.Send
    End With
    On Error GoTo 0
   
    'Cleanup
    'wb3.Close SaveChanges:=False
    Kill TempFilePath & TempFileName & ".xlsx"


    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub
Click to expand...
Hi,

Apologies for replying to an old post. Just wanted to say thanks for this. This works great. The only line that gave me a runtime error was
VBA Code: 
wb2.Close SaveChanges:=False
I just commented that out and it works perfectly.
Thanks again
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA: Open and close all source files to refresh Master Workbook data
Replies
1
Views
450
t0ny84
T
J
  • Question
Power Query - Data Refresh Error in Macro
Replies
3
Views
130
bobsan42
bobsan42
melodramatic
  • Question
VBA Send Outlook email, Save email to file folder
Replies
3
Views
450
melodramatic
melodramatic
P
  • Question
Run-time error '424': on Pivot Refresh
Replies
0
Views
190
programsam
P
M
  • Question
Excel VBA Save PDF within workbook folder and send as email attachment
Replies
1
Views
219
rpaulson
rpaulson

Forum statistics

Threads
1,214,818
Messages
6,121,725
Members
449,049
Latest member
MiguekHeka

Share this page

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
Back
Top