Macro error when emailing table

[martin0852]

Hi guys,

I have the following macro which is to be used on a protected word table to email the table, when I added a macro to the taskbar (up by save, undo, redo) and attached the macro to that button it worked fine but I need something that will be within the document so that when I distribute the file the macros can be used.

Here is the code:

Private Sub CommandButton2_Click()
If ActiveDocument.ProtectionType = wdNoProtection Then

ActiveDocument.Protect Password:="0852", NoReset:=True, Type:= _
wdAllowOnlyFormFields, UseIRM:=False, EnforceStyleLock:=False

End If


If ActiveDocument.ProtectionType = wdNoProtection Then

ActiveDocument.Protect Password:="0852", NoReset:=True, Type:= _
wdAllowOnlyFormFields, UseIRM:=False, EnforceStyleLock:=False

End If

ActiveDocument.Unprotect Password:="0852"


Dim oAp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim oInsp As Outlook.Inspector
Dim wdEditor
Dim strBody As String
Dim Z As Long

'~~> Get/Create an instance of Outlook
Set oAp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set oAp = CreateObject("Outlook.Application")
End If

'~~> Create new item
Set oItem = oAp.CreateItem(olMailItem)

'~~> Copy the table
ActiveDocument.Tables(1).Range.Copy

Set oInsp = oItem.GetInspector
Set wdEditor = oInsp.WordEditor

With oItem
.To = "martin.oleary@asml.com"
.CC = ""
.BCC = ""
.Subject = StrSubject & " Passdown " & Format(Now, "dd-mm-yy")
.HTMLBody = rng
.Attachments.Add ActiveDocument.FullName

End With

Z = wdEditor.Characters.Count

'~~> Paste the table in the email body
wdEditor.Characters(Z).PasteAndFormat (wdFormatOriginalFormatting)

'~~> Display the message. use .Send to actually Send the message
oItem.Display

'~~> Flushing the Toilet
Set oItem = Nothing
Set oAp = Nothing
Set oInsp = Nothing
Set wdEditor = Nothing

If ActiveDocument.ProtectionType = wdNoProtection Then

ActiveDocument.Protect Password:="0852", NoReset:=True, Type:= _
wdAllowOnlyFormFields, UseIRM:=False, EnforceStyleLock:=False

End If

End Sub

I have that code in the CommandButton_Click() sub of the "This Document" but when I click the CommandButton I get the error:


Run-time error '91':

Object Variable or With block Variable not set

And it highlights the

Z = wdEditor.Characters.Count

line of code.

Any help greatly appreciated.

TIA

M

[fumei]

Are you using Option Explicit?

.HTMLBody = rng


rng is neither declared, or set.

However, I believe the error lies in the use of WordEditor. This is an object, and I am not sure it has a Character.Count.

Why are you not using .Body of your mailitem to insert the table?

[martin0852]

Hi, I'm not sure what option explicit means...

I'm not very adept in VBA and have got this code in bits and pieces from people so I'm not sure what you mean, sorry!

One thing I know is that I was told that converting the table to html is supposed to make for a better paste to the body of the mail.

Thanks for your reply...can you identify what changes I need to make to the code to make this work with a command button click?

Thanks

M

[fumei]

Option Explicit - which is set by checking Require Variable Declaration under Tools > Options in the Visual Basic Editor. It means that if you do not declare variables, or you mis-spell something, the editor will not let you know. It also allows the use of IntelliSense which causes popup choices for all parameters (properties and methods).

I no longer use Outlook, so I can not check for myself.

Add Option Explicit at the top of the module. At wdEditor. and press Ctrl-Spacebar.

What do you get? Does Characters come up as a property?

[martin0852]

Hi,

Thanks again for the reply.

So I went to toll>options and turned require variable declaration on.

I created a new module and pasted the whole code from my first post in, it asked me when I wentto compile to set rng as something, so I set it as Range, it then asked me to define strSubject, all little mistakes.

Then when I compiled and pressed the button it did what I wanted, but then when I close the new email it creates, and press the button again it errors with the fllowing:


Run-time error '4605':

This method or property is not available because no text is selected.
highlighting the following code:
'~~> Copy the table
ActiveDocument.Tables(1).Range.Copy
I don't get this but I'm guessing that the macro needs to be reset after the initial run or something?

WHy else would it run once and then not again??

Thanks

M

[fumei]

First of all, let me get this straight...you are putting Tables(1) into the email body AND attaching the whole document?

That aside, I do not see how this error is happening. 4605 errors usually come from a focus problem, or a state where some function is disabled. Like when a document is protected, some functions are not available. However, you have unprotect code.

Where is this button again? You are still working from the ActiveDocument?

[martin0852]

Hi fumei,

That is correct, I want just the table 1 to be pasted to the body and the doc attached. i have important info in table 1 and related data in the remainder of the doc so I need the table to be pasted to the body.

I have attached the file so may be you can spot what I have done wrong here.

As you will see the button is in a table in the document, I would prefer to have it separate to the table being emailed but I couldn't input it anywhere else.

Thanks,

M

[fumei]

Can someone look at the docm file and see if they can find anything?



As you will see the button is in a table in the document, I would prefer to have it separate to the table being emailed but I couldn't input it anywhere else.Are you saying you have this button IN the table because you think you HAVE TO?

[martin0852]

Well when I tried to place the cursor outside the table to place the button it wouldn't allow me. My needs to have the format of the document as is (without any page breaks) may be messing that up I'm not sure.

[fumei]

Make your code execute from a keyboard shortcut - NOT the button.

Do it work multiple times?

If it does, then it is a focus issue.