Outlook VBA Calling an Excel Macro

Question

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 not 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

    xlApp.Run ("PERSONAL.XLSB!Commissions_Report_Format")

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!

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

I see you are using Sleep in your code, are you sleeping long enough for the email to be pasted? — Bob Goblin
Ok - so it appears this wasn't the fix. I get an intermittent run-time 91 error with this code. Sometimes the code works fine, sometime it doesn't. Any thoughts? — battery514

battery514 battery514 · Accepted Answer · 2016-09-15T16:14:13

user2676140's suggestion worked. I changed the sleep time to 15 seconds, and that has done the trick. Thanks!

Outlook VBA Calling an Excel Macro

2 Answers