title: Task Scheduler does not run Excel VBA Code to send PDF as Email Attachment
body: <p>Here is the software/systems I am using:
  <br>Microsoft Office 2010;
  <br>Task Scheduler; 
  <br>Windows Server 2008 R2 Standard
  <br></p>
  
  <p>I am running some VBA code within an Excel file that does the following:</p>
  
  <p><br>1. Retrieves Data from our Database via SQL/ODBC connections
  <br>2. Uploads data to a raw data table within the workbook and time stamps the workbook in a cell with the now function
  <br>3. Refreshes and formats each pivot table in the workbook
  <br>4. Exports and saves specified sheets as a PDF document and saves the document name with the time stamp from step 2
  <br>5. Saves the workbook
  <br>6. Emails that specific PDF document just created as an email attachment in Excel.
  <br>7. Closes the Excel Application</p>
  
  <p>I run this whole series in a private sub called Workbook_Open which checks to see if current time matches the specified runtime. If it does, it runs steps 1-7, if it is an hour later, it closes the workbook (that way I can work on it other than that two hour window).</p>
  
  <p>Here is the code being used:
  *Note, this code below is run in the "ThisWorkbook" Excel Object.</p>
  
  <pre><code>'This Macro will use check to see if you opened the workbook at a certain time, if you did, then it will run the Report Automation Macros below.
  
  Private Sub Workbook_Open()
  
  HourRightNow = Hour(Now())
  
  If HourRightNow = 13 Then
  
  Call RefreshDataTables
  Call RefreshPivotTables
  Call SaveWorkbook
  Call ExportToPDFFile
  Call EmailPDFAsAttachment
  Call CloseWorkbook
  
  ElseIf HourRightNow = 14 Then
  
  Call CloseWorkbook
  
  End If
  
  End Sub
  
  
  Sub RefreshDataTables()
  '
  ' RefreshDataTables Macro
  ' This Macro is used to refresh the data from the Dentrix Tables.
  '
  'This selects the table and refreshes it.
  
  Sheets("raw").Select
  Range("D4").Select
  Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
  Worksheets("NomenclatureVBA").Range("A2").Formula = "=now()"
  
  End Sub
  
  
  Sub RefreshPivotTables()
  '
  ' RefreshPivotTables Macro
  ' This Macro refreshes each Pivot Table in the document.
  '
  
  'This goes through each sheet and refreshes each pivot table.
      Sheets("D0150 VS D0330 BY BIZLINE").PivotTables("D0150 vs D0330 by BIZLINE").PivotCache.Refresh
  
     Columns("B:DD").Select
      With Selection
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlCenter
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With
  
  
      Sheets("D0150 VS D0330").PivotTables("D0150 COMP EXAM vs D0330 PANO").PivotCache.Refresh
  
      Columns("B:DD").Select
      With Selection
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlCenter
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With
  'Formnats to the specific date format below.
  
  
  
  
  
  
  End Sub
  
  '--------------------------------------------------------------------------------------------------------------
  
  Sub SaveWorkbook()
  
  ' Saves Active (Open) Workbook
  
      ActiveWorkbook.Save
  
  End Sub
  
  
  '**********************READY************************
  'More simplified and tested version of the Export To PDF format
  'Make sure to update the filePaths, worksheets,
  
  Sub ExportToPDFFile()
  Dim strFilename As String
  
  
  'Considering Sheet1 to be where you need to pick file name
  strFilename = Worksheets("NomenclatureVBA").Range("C2")
  
  
  Sheets(Array("D0150 VS D0330", "D0150 VS D0330 BY BIZLINE")).Select
  Sheets("D0150 VS D0330").Activate
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      "\\****(ServerNameGoesHere)****\UserFolders\_Common\DentrixEntrpriseCustomReports\Public\Owner Reports\DataAnalystAutomatedReports\Reports\D0150 COMP EXAM vs D0330 PANO\" &amp; strFilename &amp; ".pdf" _
      , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
      :=False, OpenAfterPublish:=False
  
  Sheets("NomenclatureVBA").Select
  
  'This is where the exporting ends, now we will proceed to email the file.
  '-----------------------------------------------------------------------------
  
  'The emailing begins here
  'This says that if there is a file name stored in the strFileName variable, then....
  End Sub
  
  
  
  'This Macro Closes the workbook... Note that it closes the very specific workbook you choose.
  
  Sub CloseWorkbook()
  
  'Workbooks("Automated D0150 COMP EXAM vs D0330 PANO.xlsm").Close SaveChanges:=False
  Application.DisplayAlerts = False
  Application.Quit
  
  End Sub
  </code></pre>
  
  <p>Then I also have the macro that emails the PDF file in the Modules section of VBA. It looks like this:</p>
  
  <pre><code>Sub EmailPDFAsAttachment()
  ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
  ' This example sends the last saved version of the Activeworkbook object .
      Dim OutApp As Object
      Dim OutMail As Object
      Dim FilePath As String
  
      'This part is setting the strings and objects to be things. (e.g. FilePath is setting itself equal to the text where we plan to set up each report)
  
      FilePath = "\\***(ServerGoesHere)***\UserFolders\_Common\DentrixEntrpriseCustomReports\Public\Owner Reports\DataAnalystAutomatedReports\Reports\D0150 COMP EXAM vs D0330 PANO\" _
      &amp; Worksheets("NomenclatureVBA").Range("C2") &amp; ".pdf"
  
      Set OutApp = CreateObject("Outlook.Application")
      Set OutMail = OutApp.CreateItem(0)
  
      On Error Resume Next
     ' Change the mail address and subject in the macro before you run it.
     '
  
      With OutMail
          .To = "email@example.com"
          .CC = ""
          .BCC = ""
          .Subject = Worksheets("NomenclatureVBA").Range("C2")
  
          .HTMLBody = "Hello all!" &amp; "&lt;br&gt;" &amp; _
          "Here is this week's report for the Comp Exam vs. Pano." &amp; "&lt;br&gt;" &amp; _
           "Let me know what you think or any comments or questions you have!" &amp; "&lt;br&gt;" &amp; _
           vbNewLine &amp; Signature &amp; .HTMLBody
  
          .Attachments.Add FilePath
          ' In place of the following statement, you can use ".Display" to
          ' display the mail.
          .Send
      End With
      On Error GoTo 0
  
      Set OutMail = Nothing
      Set OutApp = Nothing
  End Sub
  </code></pre>
  
  <p>So this all runs fine when I open up the workbook at the 13th hour (1pm), however, when I try to run this in Task Scheduler during the 13th hour, it runs everything up until the EmailPDFAsAttachment macro/sub and it gets hung up somewhere in the macro and stops running. </p>
  
  <p>I should also state that I have the Trust Center Settings to the following in both Outlook and Excel:
  <a href="http://i.stack.imgur.com/zI9CA.png" rel="nofollow">TrustCenterSettings</a></p>
  
  <p>Anyone know whats causing the macro to run perfectly when I personally open the file and then when I try and open the file via Task Scheduler it stalls in the same spot? 
  <b>And anyone know how to make it run correctly via Task Scheduler?</b></p>
  
  <p>Thanks!</p>