Macro Help

shaunkaz

Board Regular
Joined
Jan 30, 2008
Messages
199
Hi I have 2 macro which work individualy but do not work as one, it just stops at the sub end before sending out an email can someone please help

Sub SAP_Imports_and_Reports()
'
' SAP_Imports_and_Reports Macro
' Macro recorded 21/06/2010 by SHAUN banks
'
'
'MESSAGE AT BEGINNING
var1 = MsgBox("Only authorised personel to import downloads from SAP. As well as importing the downloads this will also produce the manufacturing lates report. Do you still wish to continue ???", vbYesNo + vbCritical, "Schneider-Electric Manufacturing Order Book")

If var1 = vbNo Then

stopnow = True

Else
' OPEN UP DOWNLOADS

ChDir _
"J:\Manufacuring Daily Order Book 2008\Manufacturing Order Book Downloads"
Workbooks.Open Filename:= _
"J:\Manufacuring Daily Order Book 2008\Manufacturing Order Book Downloads\Open Order Report.xls"
Workbooks.Open Filename:= _
"J:\Manufacuring Daily Order Book 2008\Manufacturing Order Book Downloads\COIS Download.xls"
Workbooks.Open Filename:= _
"J:\Manufacuring Daily Order Book 2008\Manufacturing Order Book Downloads\Import FH30.xls"
Workbooks.Open Filename:= _
"J:\Manufacuring Daily Order Book 2008\Manufacturing Order Book Downloads\Open Deliveries Report.xls"

'Copy and paste downloads into core stock

'FH30

Windows("Manufacturing daily order book 2010.xls").Activate
Windows("Import FH30.xls").Activate
Cells.Select
Selection.Copy
Windows("Manufacturing daily order book 2010.xls").Activate
Sheets("Import FH30").Select
Cells.Select
ActiveSheet.Paste

'Coois

Windows("COIS Download.xls").Activate
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Manufacturing daily order book 2010.xls").Activate
Sheets("Import COOIS").Select
Cells.Select
ActiveSheet.Paste

'FH30 O.Orders

Sheets("FH 30 O.Orders").Select
Range("A2:S2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Windows("Open Order Report.xls").Activate
Range("A2:S2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Manufacturing daily order book 2010.xls").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'FH30 O.Delivery

Sheets("FH 30 O.Delivery").Select
Range("A2:P2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Windows("Open Deliveries Report.xls").Activate
Range("A2:P2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Manufacturing daily order book 2010.xls").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlLast

'Update SAP report

Sheets("SAP").Select
Range("AI3:AK9007").Select
Selection.Copy
Range("A3").Select
Do While ActiveCell.Formula <> ""
ActiveCell.Offset(0, 1).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Update Graphs

Sheets("graph data").Select
Range("a2:q3").Select
Selection.Copy
Range("A7").Select
Selection.End(xlDown).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Update History

Sheets("O.Orders History").Select
Range("b2").Select
Do While ActiveCell.Formula <> ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, -1).Select
ActiveCell.Formula = Date

Sheets("FH 30 O.Orders").Select
Cells.Select
Selection.AutoFilter
Range("O3").Select
Selection.AutoFilter Field:=21, Criteria1:="Late"
Selection.AutoFilter Field:=22, Criteria1:="Manufacturing"
Range("A2:Ac2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("O.Orders History").Select
Range("b2").Select
Do While ActiveCell.Formula <> ""
ActiveCell.Offset(1, 0).Select
Loop


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

' Close all SAP files

Windows("Open Order Report.xls").Activate
ActiveWindow.Close
Windows("COIS Download.xls").Activate
ActiveWindow.Close
Windows("Import FH30.xls").Activate
ActiveWorkbook.Close
Windows("Open Deliveries Report.xls").Activate
ActiveWindow.Close

'Back to Manufacturing Daily Order Book

Windows("Manufacturing daily order book 2010.xls").Activate

'Tidy up Pages

Range("A1").Select
ActiveSheet.Next.Select
Range("A1").Select
ActiveSheet.Next.Select
Range("D2").Select
ActiveSheet.Next.Select
Cells.Select
Selection.AutoFilter
Range("G2").Select
ActiveSheet.Next.Select
Range("A1").Select
ActiveSheet.Next.Select
Range("D2").Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A6").Select
ActiveSheet.Next.Select
Range("I6").Select
ActiveSheet.Next.Select
Range("J8").Select
ActiveSheet.Next.Select
Range("J7").Select
ActiveSheet.Next.Select
Range("F6").Select
ActiveSheet.Next.Select
Range("J8").Select
ActiveSheet.Next.Select
Range("A1").Select
ActiveSheet.Next.Select
Range("E2").Select
ActiveSheet.Next.Select
Range("D2").Select
ActiveSheet.Next.Select
Range("A8").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A1").Select

'Save

ActiveWorkbook.Save

'Create Manufacturing Late report

Sheets("FH 30 O.Orders").Select
Cells.Select
Selection.AutoFilter
Range("S5").Select
Selection.AutoFilter Field:=21, Criteria1:="Late"
Selection.AutoFilter Field:=22, Criteria1:="Manufacturing"
Sheets("FH 30 O.Delivery").Select
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=21, Criteria1:="Late"
Selection.AutoFilter Field:=22, Criteria1:="Manufacturing"

' Open blank Manufacturing Late Report

ChDir _
"J:\Manufacuring Daily Order Book 2008\Manufacturing Order Book Downloads"
Workbooks.Open Filename:= _
"J:\Manufacuring Daily Order Book 2008\Manufacturing Order Book Downloads\Late Report Manufacturing.xls"
Windows("Manufacturing daily order book 2010.xls").Activate

' Copy Files form FH30 O.Orders

Sheets("FH 30 O.Orders").Select
Range("A1:AC1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Paste FH30.Orders into Late report

Windows("Late Report Manufacturing.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Columns.AutoFit
Range("A1").Select
Windows("Manufacturing daily order book 2010.xls").Activate

'Copy FH30 O.Delivery for Late report

Sheets("FH 30 O.Delivery").Select
Range("A1:AA1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Paste FH30 Delivery into Late Report

Windows("Late Report Manufacturing.xls").Activate
Sheets("Late Open Deliveries").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Columns.AutoFit
Range("AB1").Select
Windows("Manufacturing daily order book 2010.xls").Activate
Selection.AutoFilter
Range("A1").Select
Sheets("FH 30 O.Orders").Select
Selection.AutoFilter
Range("G2").Select
Sheets("CURRENT").Select
Range("A2").Select
' Take a copy of Core stock and place intop the Core Stock hisory
Sheets("Core Stock History").Select
Range("E3:E1000").Select
Selection.Copy
Range("E3").Select
Do While ActiveCell.Formula <> ""
ActiveCell.Offset(0, 1).Select
Loop

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("a1").Select

Application.CutCopyMode = False

Sheets("CURRENT").Select
Range("a1").Select
ActiveWorkbook.Save

'Late report Save and Close

Windows("Late Report Manufacturing.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

Windows("Manufacturing daily order book 2010.xls").Activate



End If

End Sub
'This next file will email the late reports - THIS IS WHERE IT STOPS AND WILL NOT CARRY ON

Sub Email1()
Dim oSess As Object
Dim oDB As Object
Dim oDoc As Object
Dim oItem As Object
Dim direct As Object
Dim Var As Variant
Dim flag As Boolean
Set oSess = CreateObject("Notes.NotesSession")
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL
flag = True
If Not (oDB.IsOpen) Then flag = oDB.Open("", "")
If Not flag Then
MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
GoTo exit_SendAttachment
End If
On Error GoTo err_handler
'Building Message
Set oDoc = oDB.CREATEDOCUMENT
Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
oDoc.Form = "Memo"
oDoc.Subject = "Manufacturing Late Report"
oDoc.sendto = "email address"
oDoc.body = ""
oDoc.postdate = Date
oDoc.SaveMessageOnSend = True
'Attaching DATABASE
Call oItem.EmbedObject(1454, "", "J:\Manufacuring Daily Order Book 2008\Manufacturing Order Book Downloads\Late Report Manufacturing.xls")
oDoc.visable = True
'Sending Message
oDoc.SEND False
exit_SendAttachment:
On Error Resume Next
Set oSess = Nothing
Set oDB = Nothing
Set oDoc = Nothing
Set oItem = Nothing
'Done
Exit Sub
err_handler:
If Err.Number = 7225 Then
MsgBox "File doesn't exist"
Else
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo exit_SendAttachment
End Sub



' Message box when finished


var1 = MsgBox("Imports Completed and Document Saved", vbOKOnly + vbInformation, "Thankyou")


End If


End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,823
Office Version
2013
Platform
Windows
At the moment it is 2 separate macros. You can either put Call Email1 at the end of the first macro to start the second or remove the following to make it continue.

Code:
End Sub
[B]'This next file will email the late reports - THIS IS WHERE IT STOPS AND WILL NOT CARRY ON[/B]

Sub Email1()
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,322
Messages
5,510,586
Members
408,805
Latest member
botanist

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top