Macro not working

harry · #1 11-25-2022, 05:58 PM

1. I created the following simple macro in Word, to remove space before and after paragraphs in a document.

Code:

Sub ClearParagraphSpacing()
Selection.WholeStory
With Selection.ParagraphFormat
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 0
.SpaceAfterAuto = False
End With
End Sub

This works fine in Word, but when I copy it into Outlook, I get an error when I run it on a new email or a reply: "Object variable or With block variable not set." How can I fix this?

2. I can add a shortcut for the macro on the Ribbon or the Quick Access Toolbar, but when I click on it, nothing happens. However, as the macro needs to work in a message editing window (not in the main folder window), does it need a different kind of shortcut?

AlanCantor · #2 12-04-2022, 10:24 AM

VBA code for Word won't work in Outlook -- or in any other VBA-supported Microsoft product.

VBA comes in different "flavours." Each application has a unique object model, and for that reason, VBA for each application has to be different.

I find VBA for Outlook especially difficult to understand. Hopefully you'll find code samples on the web that give hints on how to accomplish what you want to do.

Another way to do this, albeit less elegant, is to use a third-party automation tool to do the heavy lifting. For example, this Macro Express script (tested only once) might get the job done by sending sequences of keys. To make this script reliable, you may need to add delays after some of the steps.

Text Type (Simulate Keystrokes): <CONTROL>a // Select all
Text Type (Simulate Keystrokes): <F10> // Give the Ribbon focus
Text Type (Simulate Keystrokes): o // Format ribbon
Text Type (Simulate Keystrokes): pg // Paragraph settings
Text Type (Simulate Keystrokes): <ALT>b // Navigate to Spacing Before field
Text Type (Simulate Keystrokes): 0 pt
Text Type (Simulate Keystrokes): <TAB> // Navigate to Spacing After field
Text Type (Simulate Keystrokes): 0 pt
Text Type (Simulate Keystrokes): <ENTER> // Activate the default OK button

gmayor · #3 12-04-2022, 09:58 PM

As Outlook uses a subset of Word to edit messages, you can use some Word VBA in Outlook messages, but you have to tell the macro what you are doing. The following will remove the paragraph spacing (attributable to the paragraph formatting) of the selected message:

Code:

Sub ClearParagraphSpacing()
'Graham Mayor - https://www.gmayor.com - Last updated - 05 Dec 2022 

 Dim olMsg As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object

    On Error Resume Next
    Select Case Outlook.Application.ActiveWindow.Class
        Case olInspector
            Set olMsg = ActiveInspector.currentItem
        Case olExplorer
            Set olMsg = Application.ActiveExplorer.Selection.Item(1)
    End Select
    On Error GoTo lbl_Exit

    With olMsg
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        .Display
        With oRng.ParagraphFormat
            .SpaceBefore = 0
            .SpaceBeforeAuto = False
            .SpaceAfter = 0
            .SpaceAfterAuto = False
        End With
    End With
lbl_Exit:
    Set olMsg = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub

harry · #4 12-12-2022, 12:22 AM

Thanks for your replies. Graham's code does exactly what I wanted, and I have successfully assigned it to a button on the Quick Access Toolbar.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need Help with this Macro-Partially Working	rsrasc	Excel Programming	10	09-10-2020 02:38 PM
Just one macro not working	Ulodesk	Word VBA	1	04-01-2019 09:16 AM
How to move data - Macro not working	ecarter312	Excel Programming	1	08-10-2016 11:26 PM
Fraction macro not working	Ulodesk	Word VBA	1	07-18-2012 04:01 PM
Macro to open template not working	Brandi	Mail Merge	2	06-24-2010 09:29 AM