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
– Bob Goblin, Commented Sep 15, 2016 at 15:58
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, Commented Sep 16, 2016 at 15:09

battery514 · Accepted Answer · 2016-09-15 16:14:13Z

3

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

answered Sep 15, 2016 at 16:14

battery514

2496 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Comintern · Accepted Answer · 2016-09-15 16:00:49Z

1

You can't use xlPasteValues if you want to past formatted text from the clipboard. Use this instead:

Selection.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

Note, that will paste the content as plain text. If you need formatting, you can change the Format parameter to "HTML".

answered Sep 15, 2016 at 16:00

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Collectives™ on Stack Overflow

Outlook VBA Calling an Excel Macro

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related