method 'publish' of object 'PublishObject' failed

[clares]

Hi All

I come across code (from a link from the message board) for sending a range in Excel via outlook. Although it said just paste into a module, it gives me the above error. The code is here and its the following line that it bombs out of:

ActiveWorkbook.PublishObjects.Add(4, strTempFilePath, _
rngeSend.Parent.Name, rngeSend.Address, 0, "This is a test", "").Publish True

Private Sub CommandButton1_Click()
'Sends a specified range in an Outlook message and retains Excel formatting
'Written by Daniel Klann '2002

'Dimension variables
    Dim oOutlookApp As Object, oOutlookMessage As Object
    Dim oFSObj As Object, oFSTextStream As Object
    Dim rngeSend As Range, strHTMLBody As String, strTempFilePath As String
        
    'Select the range to be sent
    On Error Resume Next
    Set rngeSend = Application.InputBox("Please select range you wish to send.", , , , , , , 8)
    If rngeSend Is Nothing Then Exit Sub    'User pressed Cancel
    On Error GoTo 0

    'Get the temp folder path
    Set oFSObj = CreateObject("Scripting.FilesystemObject")
    strTempFilePath = oFSObj.GetSpecialFolder(2)
    'strTempFilePath = "O:\Daily Sales Figures"
    strTempFilePath = strTempFilePath & "\DailySalesFigures.htm"


    'Now create the HTML file - NOTE! xlSourceRange and xlHtmlStatic have been replaced by their
    'numeric values due to a potential error (unexplained) noted by Ivan F Moala 15/5/03
        
    ActiveWorkbook.PublishObjects.Add(4, strTempFilePath, _
                    rngeSend.Parent.Name, rngeSend.Address, 0, "This is a test", "").Publish True
    
    'Create an instance of Outlook (or use existing instance if it already exists
    Set oOutlookApp = CreateObject("Outlook.Application")

    'Create a mail item
    Set oOutlookMessage = oOutlookApp.CreateItem(0)

    'Open the HTML file using the FilesystemObject into a TextStream object
    Set oFSTextStream = oFSObj.OpenTextFile(strTempFilePath, 1)
    
                                         
    'Now set the HTMLBody property of the message to the text contained in the TextStream object
    strHTMLBody = oFSTextStream.ReadAll
    
    'By default the range will be centred.  This line left aligns it and you can
    'comment it out if you want the range centred.
    strHTMLBody = Replace(strHTMLBody, "align=center", "align=left", , , vbTextCompare)

    oOutlookMessage.HTMLBody = strHTMLBody

    oOutlookMessage.Display

End Sub

Does anyone have a quick solution?

Kindest Regards

Peter

 

[mjbeam]

Could you set a breakpoint at this line:

strTempFilePath = strTempFilePath & "\DailySalesFigures.htm"

and tell me what the variable strTempFilePath is equal to at this point in the code?


-Mike

 

[chad1222]

could be a reference issue in VBA, but not sure of the ones you need to use to use the outlook references. Browse through them in the VBA editor -> tools -> references.
Chad

 

[clares]

Thanks for you repsonses. Now sorted.

Kind Regards

 

[rwhiton]

LinkedCells... another option

Sounds like you are having trouble sharing data with others. You might try out this product (http://www.LinkedCells.com).... it is a very easy way to share data with others.

 

[Nephid]

I've had this same problem for a while, and I now beleive it's something to do with what Excel has focus set to at time of publish.
I found setting the application to focus on range A1 (could be any range on the publish sheet I believe) prior to publish, stops the error message.
The code I added into my project was this one-liner just before the publish call:

ThisWorkbook.Sheets("PublishSheet").Range("A1").Select

After this focus has been set, it seems to allow the publish to run without error

 

[sulovsky]

My publish failed issue was due to the fact, that the file to which I tried to publish was locked... Make sure you have permissions to publish to location where you want to publish/ the file is properly closed in case you do some postpublishing processing with it.

Greets, Jiri

 

[vladisan]

Hello all,


I know it has been a long time since this discussion took place, but I ended up finding it just now and would like to post steps for a solution and an explanation (partially as I do not have full knowledge of the PublishObject and how its properties and methods behave)


I found out that using ActiveX Controls with .publish method from PublishObject fails when using that type of control. For example in the initial post from clares we can see:

Private Sub CommandButton1_Click()

This i think is a button created from the ActiveX control menu. Once the button is created, upon double clicking on it that piece of code is generated automatically inside the development environment in the same way as mentioned above. Now all the running code is within its scope and it runs ok if executed from the environment, but not if called from the button. Another user in this thread, Nephid, mentions that it has something to do with the focus that Excel has at the moment of execution which indeed seems to be the case. However upon testing the code only worked when the focus was "right", meaning in the sheet where the to be printed ranges existed, while executed through the run button (F5) in the development environment and did not execute through the ActiveX button regardless of focus.

However, I found out that by using Form controls button instead of the ActiveX one it works flawlessly. The solution was to create my code using the .publish method inside a regular sub, then creating a Form Control button, right clicking on it, selecting the "Assign macro".

So to summarize the solution would be like this:
- Check for references (as mentioned in previous posts) making sure that Outlook objects are enabled along with other needed libraries.
- Make sure that you have write access to the location in which you are creating the .htm file
- Last but not least and the one that after all the done tests seemed to be the "winner" use a form control with this method and not ActiveX.

Cheers and hope it helps in case someone else finds a similasr problem.

Kind regards,
Vlad