Outlook VBA Calling an Excel Macro
<p>I have an Outlook macro which processes an email and pastes it into Excel, and then calls an Excel macro for further processing.  When called separately, the two macros work as expected.  However, if I try to call the Excel macro from the Outlook macro, the email will <strong>not</strong> paste into the Excel workbook, and then when the Excel macro is called it generates an error because there is no data.  Any idea why </p>

<pre><code>    xlApp.Run ("PERSONAL.XLSB!Commissions_Report_Format")
</code></pre>

<p>would cause the data to not paste from Outlook into Excel?  The error only occurs when this line of code is present.  Thanks in advance!</p>

<pre><code>Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Option Explicit

Sub PasteToExcel(item As Outlook.MailItem)
Dim activeMailMessage As MailItem
Dim xlApp As Excel.Application
Dim Wb As Excel.Workbook
Dim Ws As Excel.Worksheet

    'Get a handle on the email
    Set activeMailMessage = ActiveExplorer.Selection.item(1)

    'Copy the formatted text:
    activeMailMessage.GetInspector().WordEditor.Range.FormattedText.Copy  

    'Ensure Excel Application is open
    Set xlApp = CreateObject("Excel.Application")

    'Make Excel Application visible
    xlApp.Visible = True

    'Open the Personal Macro Workbook, or the Excel macro won't run
    xlApp.Workbooks.Open ("C:\Users\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xlsb")

    'Name the Excel File
    Set Wb = xlApp.Workbooks.Add

    'Paste the email
    Set Ws = xlApp.Sheets(1)
    Ws.Activate
    Ws.Range("A1").Select
    Sleep 3000
    Selection.PasteSpecial xlPasteValues
    Sleep 3000 'wait for 3 seconds

    'Run the Excel macro to clean up the file
    xlApp.Run ("PERSONAL.XLSB!Commissions_Report_Format")

End Sub
</code></pre>