I have a work that contains one data query. The work book contains an auto_open macro. When i open the workbook I get the following message generated by the macro. My intention with this workbook is that the workbook is run by the scheduler. It emails a copy of the workbook that is data only and then closes. It does work if the second line commented out.
"This action will cancel a pending Refresh Data command. Continue?
The message is generated by the second line of code which saves the workbook. It appears to be a timing issue. Listed below is the auto_open macro:
Sub auto_open()
ActiveWorkbook.Connections("OI_Stock_Status_Report").Refresh
ActiveWorkbook.Save
Sheets("Sheet2").Select
Cells.Select
Selection.ClearContents
Application.Goto Reference:="Copy_Source"
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Sheet2").Select
Range("A7").Select
Sheets("Sheet1").Select
Range("A7").Select
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "mmddyy")
Application.ScreenUpdating = False
Sheets("Sheet2").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Stock_Status_010_" & strdate & ".xlsx"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.Subject = "Stock Status 010 New Format " & Format(Date, "MM/dd/YY")
.Body = "Attached is the New Stock status report for BI."
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
ActiveWorkbook.Save
ActiveWorkbook.Application.Quit
End Sub
Bookmarks