[COLOR=green][FONT=Times New Roman]'-------------------------------------------[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'References:[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Visual Basic For Applications[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Access 9.0 Object Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'OLE Automation[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft ActiveX Data Objects 2.1 Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Scripting Runtime[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft DAO 3.6 Object Library[/FONT][/COLOR]
[FONT=Times New Roman][COLOR=#008000]'Microsoft Excel 11.0 Object Library[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=#008000]'Microsoft Outlook 14.0 Object Library[/COLOR][/FONT]
[COLOR=green][FONT=Times New Roman]'-------------------------------------------[/FONT][/COLOR]
[FONT=Times New Roman]DoCmd.Echo [COLOR=blue]False[/COLOR], "Running Program" [COLOR=green]'Indicates in the progress bar the program is running[/COLOR][/FONT]
[FONT=Times New Roman]DoCmd.Hourglass [COLOR=blue]True[/COLOR] [COLOR=green]'Turn on the Hourglass[/COLOR][/FONT]
[FONT=Times New Roman]DoCmd.SetWarnings [COLOR=blue]False[/COLOR] [COLOR=green]'Turn off warnings[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] ExcelFile [COLOR=blue]As[/COLOR] String [COLOR=green]'Declare the ExcelFile variable of string type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] ExcelWorksheet [COLOR=blue]As[/COLOR] String [COLOR=green]'Declare the ExcelWorksheet variable of string type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] FEDB [COLOR=blue]As [/COLOR]String [COLOR=green]'Declare the FEDB variable of string type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] QueryName [COLOR=blue]As [/COLOR]String [COLOR=green]'Declare the QueryName variable of string type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] objDB [COLOR=blue]As[/COLOR] Database [COLOR=green]'Declare the objDB variable of Database type[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] MyDate [COLOR=green]'Declare the MyDate variable[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] MyWeekDay [COLOR=green]'Declare the MyWeekDay variable[/COLOR][/FONT]
[FONT=Times New Roman]MyDate = Date [COLOR=green]'Assign the current date to MyDate variable[/COLOR][/FONT]
[FONT=Times New Roman]MyWeekDay = Weekday(MyDate) [COLOR=green]'MyWeekDay represents the current day in the week[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]If[/COLOR] MyWeekDay = 6 [COLOR=blue]Then[/COLOR] [COLOR=green]'If today is Friday [day 6] then[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=green]'Assign the location to export the Biasi excel file to and give the current date[/COLOR][/FONT]
[FONT=Times New Roman] ExcelFile = "G:\Biasi\Biasi sent to Pauline F\WC" & "_" & Format(Date - 4, "ddmmyy") & ".xls" [/FONT]
[FONT=Times New Roman] ExcelWorksheet = "WC " & Format(Date - 4, "ddmmyy") [COLOR=green]'Assign Biasi as the name of the worksheet in the excel file[/COLOR][/FONT]
[FONT=Times New Roman] FEDB = "G:\eFlowStatsFrontEnd.mdb" [COLOR=green]'Assign the name and path of the database to export the table from[/COLOR][/FONT]
[FONT=Times New Roman] QueryName = "qryExportToExceltblBiasi" [COLOR=green]'Assign the name of the table to be exported to the Excel file[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Set[/COLOR] objDB = OpenDatabase(FEDB) [COLOR=green]'Set the objDB to open the eFlowStatsFrontEnd database[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]If[/COLOR] Dir(ExcelFile) <> "" [COLOR=blue]Then[/COLOR] Kill ExcelFile [COLOR=green]'If the Excel file already exists, you can delete it here[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=green]'Excute the creation of the Excel file[/COLOR][/FONT]
[FONT=Times New Roman] objDB.Execute "Select*Into[Excel 8.0;Database=" & ExcelFile & "].[" & ExcelWorksheet & "] From " & "[" & QueryName & "]"[/FONT]
[FONT=Times New Roman] objDB.Close [COLOR=green]'Close the eFlowStatsFrontEnd database[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Set[/COLOR] objDB = [COLOR=blue]Nothing[/COLOR] [COLOR=green]'Set the objDB to nothing[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=green]'Append the contents of the tblBiasiProcessHistory table to the tblBiasiHistoryLongTerm table[/COLOR][/FONT]
[FONT=Times New Roman] DoCmd.OpenQuery "qryApptblBiasiProcessHistoryToLongTerm", acNormal, acEdit[/FONT]
[FONT=Times New Roman] DoCmd.OpenQuery "qryDeltblBiasiProcessHistory", acNormal, acEdit [COLOR=green]'Delete the contents of the tblBiasiProcessHistory table[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Dim[/COLOR] ObjExcel [COLOR=blue]As[/COLOR] Object [COLOR=green]'Declare the Excel Object[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Set[/COLOR] ObjExcel = CreateObject("Excel.Application") [COLOR=green]'Create an instance of Excel[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.Visible = [COLOR=blue]True[/COLOR] [COLOR=green]'Make Excel visible[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=green]'Open the Excel workbook Biasi with the current week commencing date[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.Workbooks.Open "G:\Biasi\Biasi sent to Pauline F\WC" & "_" & Format(Date - 4, "ddmmyy") & ".xls"[/FONT]
[FONT=Times New Roman] [COLOR=blue]Set[/COLOR] objsheet = ObjExcel.ActiveWorkbook.Worksheets(1) [COLOR=green]'Set the objsheet to active worksheet in the active workbook[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]With[/COLOR] objsheet [COLOR=green]'With the active worksheet[/COLOR][/FONT]
[FONT=Times New Roman] .Rows("1:1").Font.Bold = [COLOR=blue]True[/COLOR] [COLOR=green]'Set the first row font to bold[/COLOR][/FONT]
[FONT=Times New Roman] .Rows("1:1").Font.Underline = xlUnderlineStyleSingle [COLOR=green]'Underline the text in each cell of the first row[/COLOR][/FONT]
[FONT=Times New Roman] .Rows("1:1").Select [COLOR=green]'Insert a new row[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.Selection.Insert Shift:=xlDown[/FONT]
[FONT=Times New Roman] ObjExcel.Range("A1").Select [COLOR=green]'Select Cell A1[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.ActiveCell.FormulaR1C1 = "BIASI" [COLOR=green]'Assign the text "BIASI" to this cell[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.Range("B1").Select [COLOR=green]'Select Cell B1[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.ActiveCell.FormulaR1C1 = "Week Commencing:" [COLOR=green]'Assign the text "Week Commencing:" to this cell[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.Range("D1").Select [COLOR=green]'Select Cell D1[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.ActiveCell.FormulaR1C1 = MyDate – 4 [COLOR=green]'Assign the current date minus 4 days to give the beginning of the week[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]With[/COLOR] ObjExcel.Selection[/FONT]
[FONT=Times New Roman] .HorizontalAlignment = xlLeft [COLOR=green]'Left align the text[/COLOR][/FONT]
[FONT=Times New Roman] .VerticalAlignment = xlBottom [COLOR=green]'Bottom align the text[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]End With[/COLOR][/FONT]
[FONT=Times New Roman] .Columns("A:Z").Select [COLOR=green]'Select Columns A to Z[/COLOR][/FONT]
[FONT=Times New Roman] .Columns("A:Z").EntireColumn.AutoFit [COLOR=green]'Autofit the columns to their contents[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]End With[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.ActiveWorkbook.Close [COLOR=blue]True[/COLOR] [COLOR=green]'Close the Active Workbook[/COLOR][/FONT]
[FONT=Times New Roman] ObjExcel.Quit [COLOR=green]'Close the Excel Application[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Dim[/COLOR] olApp [COLOR=blue]As[/COLOR] Outlook.Application [COLOR=green]'Declare the outlook application variable[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Dim[/COLOR] olMail [COLOR=blue]As[/COLOR] MailItem [COLOR=green]'Declare the Mail Item variable[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Set[/COLOR] olApp = New Outlook.Application [COLOR=green]'Set the olApp to a new outlook application[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Set[/COLOR] olMail = olApp.CreateItem(olMailItem) [COLOR=green]'Set the olMail to create an outlook mail item[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=green]'Create and send the email with an excel attachment[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]With[/COLOR] olMail[/FONT]
[FONT=Times New Roman] .To = "Pauline.Fiddimore@domesticandgeneral.com" [/FONT]
[FONT=Times New Roman] .CC = "maria.leonard@domesticandgeneral.com"[/FONT]
[FONT=Times New Roman] .BCC = "elaine.boulton@domesticandgeneral.com"[/FONT]
[FONT=Times New Roman] .Subject = "Biais Weekly Excel Spreadsheet"[/FONT]
[FONT=Times New Roman] .Body = "Please find attached the weekly Excel Spreadsheet."[/FONT]
[FONT=Times New Roman] .Attachments.Add "G:\Biasi\Biasi sent to Pauline F\WC" & "_" & Format(Date - 4, "ddmmyy") & ".xls"[/FONT]
[FONT=Times New Roman] .Send[/FONT]
[FONT=Times New Roman] [COLOR=blue]End With[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Set[/COLOR] olMail = [COLOR=blue]Nothing[/COLOR] [COLOR=green]'Set the olMail to nothing[/COLOR][/FONT]
[FONT=Times New Roman] [COLOR=blue]Set[/COLOR] olApp = [COLOR=blue]Nothing[/COLOR] [COLOR=green]'Set the olApp to nothing[/COLOR][/FONT]