Pause macro until print job has finished

tony.reynolds

Board Regular
Joined
Jul 8, 2010
Messages
97
I have a macro as below that prints the sheet to a specific printer then it emails it to a recipiant selected from a userform "Email choice"

This macro is the macro in the userform for the first button on the user form "Email Choice"

I found that excel did not like running this if the print job from previous macro was not finished, so i had to add this wait time so it did not cause excell to crash. which is a real pain

Can someone help me either make it pause until print job is finished or help me eliminate the problem some other way?

First this is the end of the prevoius macro..

Code:
Application.ActivePrinter = "SHARP MX-2600N PCL6 on Ne00:"
                
    SENDTHISWORKBOOKVIAEMAIL.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        
SENDTHISWORKBOOKVIAEMAIL.Activate

Application.ScreenUpdating = True
 
EmailChoice.Show
Then obviously the userform shows and the user selects on of the three options this being one of them, (the only difference between the three is the email address)

Code:
Private Sub Accounts_Click()
EmailChoice.Hide
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 8
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Set emailthis = ActiveWorkbook
AONumber = Range("ACTION_ORDER_NUMBER").Value
AOSoldTo = Range("SOLDTOCOMPANY").Value
    
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
                
    On Error Resume Next
                                     
        With OutMail
        .To = "[EMAIL="accounts@xyz.com.au"]accounts@xyz.com.au[/EMAIL]"
        .Subject = "Action Order " & AONumber & " for " & AOSoldTo
        .Body = "Here is Action Order " & AONumber & " for " & AOSoldTo
        .Attachments.Add emailthis.FullName
        .Send
        End With
                            
    Set OutMail = Nothing
    Set OutApp = Nothing

   EmailChoice.Hide
   
   Windows("Action Order.xlsm").Activate
   Range("PLACED_BY").Value = "Tony Reynolds"
   ActiveWorkbook.Save
   ActiveWorkbook.Close
End Sub
any one that can clean up my code a bit and fix these issues would be great.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,555
above your modules place this declare

Code:
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

use this to pause for n milliseconds

eg 1 second=1000milliseconds
Code:
Sleep 1000
 

tony.reynolds

Board Regular
Joined
Jul 8, 2010
Messages
97
hey thanks diddi seems to work...

What exactly is kernal32? like what does this actually do
?
Is it pausing the macro until the printer drver thingo is free?

Thanks a stack
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,368
Office Version
2019, 2016, 2013
Platform
Windows
can you manually set your print settings to spool, and the data should then print from the printer and not the PC
 

Watch MrExcel Video

Forum statistics

Threads
1,102,360
Messages
5,486,387
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top