Help needed for Command Button in word to send email and close word

[adriancd]

Hi All,

I am in a bit of a pickle.

I have setup a command button in a word document that users can click and it sends the document to a specified email address and closes down word automatically.

After several days of google site hopping, and failed attempts to get this thing going, I am at the point where I think I am going to go either bald or grey and I am only 24.

I have the code sorted however when I check the email attachement, the document appears blank without the user's changes. The file is subject to fields and the users are unable to edit the document apart from those fields I have created. I do not require them to save the file as I am the only one who needs the finalised version.

Here is my code:


Private Sub CommandButton1_Click()
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
'You'll need to add the Outlook Object Library to VBA Tools References
Dim oItem As Outlook.MailItem


On Error Resume Next
If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
ActiveDocument.Save 'so save it
End If

'see if Outlook is running and if so turn your attention there
Set oOutlookApp = GetObject(, "Outlook.Application")

If Err <> 0 Then 'Outlook isn't running
'So fire it up
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

'Open a new e-mail message
Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem 'and add the detail to it
.To = "adrian.dressekie" 'send to this address
.Subject = "Complimentary Ticket Request" 'This is the message subject
.Body = "Please process the attached document" ' This is the message body text
.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue
.Send
'**********************************
'If you want to view the message before it goes
'change the line above from .Send to .Display
'Otherwise the message is sent straight to the Outbox
'and if you have Outlook set to send mail immediately,
'it will simply be Sent
'with no obvious sign that Outlook has operated.
'Apart from the copy in the Outlook Sent folder
'**********************************
End With

If bStarted Then 'If the macro started Outlook, stop it again.
oOutlookApp.Quit
End If
'Clean up

Set oItem = Nothing
Set oOutlookApp = Nothing
Application.ScreenUpdating = False
ActiveDocument.Close wdDoNotSaveChanges
End Sub



I have activated all of the relevant references, but still no luck.

Could someone please help.

In a summary here is what I would like it to do:
User opens document (if possible as a template)
User fills in First and Last Name (in fields)
User selects (from drop down fields) the amount of tickets they require
User types their initials as digital signature (in field)
User hits submit (control toolbox command button) which then sends the completed document automatically to my mailbox and closes word without any save as dialog boxes or anything.

If the above is not possible, would I be able to have it so:
User opens document
User fills in First and Last Name
User selects the amount of tickets they require
User types their initials as digital signature
User hits submit and which then saves the document to their default document folder, sends me the document as an attachment to my mailbox and closes word down.

I am not really bothered if it saves or not, but I really need it to be dummy proof for the users and that they do not see any dialog boxes.

Also if possible to set it so that it sends an automatic email back to the user confirm receipt of the attachment and inform them their tickets will be ready within 5 working days.

I know it may seem like alot but the help will be very much appreciated.

Adrian

Edited 2-Sep-10 by geekgirlau. Reason: insert vba tags

[Imdabaum]

What error message are you getting?

[fumei]

"In a summary here is what I would like it to do:
...
User hits submit (control toolbox command button) which then sends the completed document automatically to my mailbox and closes word without any save

But you have an explicit save instruction.
If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
ActiveDocument.Save 'so save it
End IfIn any case you MUST save it in order to send it as a file (attachment).

I can not duplicate your problem.

1. I created a template (TestFileAndSend.dot). It has three formfields and a ActiveX commandbutton. It is protected for forms so the formfields will work.

2. I clone a new document and I fill in the formfields and click the Send button.

3. I get the SaveAs dialog. Now...this MUST happen because of the:
If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
ActiveDocument.Save 'so save it
End If It does not have to happen if you (the programmer) do an explicit SaveAs instruction within your code. In other words, it is possible to do a programmatic SaveAs to some location with no user interaction. Then you could delete it after, or not.

4. When I either accept the default name (the text of the first formfield), or type in a new one - and again this can be bypassed if you programmatically do a SaveAs - it performs exactly as you seem to want it to.

It sends an email to the given address, and the attached file DOES have the entries I typed into the cloned-from-template document.

[fumei]

Oh, and unless you are using a third party button clicker, you do get the Outlook Security warning dialog.

[adriancd]

Hi Fumei

Would you be able to send me that TestFileAndSend.dot template?

Best regards

Adrian

[Tinbendr]

I am not really bothered if it saves or not As your Outlook code dictates (and as Gerry has pointed out.), if you're going to attach it, then the file has to be saved.

But could you just add the info to the Message body?
.Body = activedocument.Formfields("Name").Result & " has requested " & _
Activedocument.Formfields("NumTickets").Result & " for " & _
Activedocument.Formfields("EventDate").Result

... but I really need it to be dummy proof for the users ... Nothing is foolproof because fools are so ingenious


Also if possible to set it so that it sends an automatic email back to the user confirm receipt of the attachment and inform them their tickets will be ready within 5 working days. Of course, you can setup a delivery request, but I'm not sure if you can send an automated response, say, based on a Subject line. Hmmm.. interesting.

I had a sample doc setup, but forgot to comment out theActiveDocument.Close wdDoNotSaveChanges on the LAST test run!:banghead:
Maybe later.....

[adriancd]

Hi Tinbendr
I need to have the actual document as an attachment for audit purposes. I did ask if having the document as the email body would be okay, but it is not allowed.

I am a real beginner to this programming stuff, so most of the coding goes straight over my head.

If I have to save the document first, would it be possible to specify a set filename and path that can also pull the users name and place it in the document title. I work for a local government org, so we all use outlook and all have our info set in our systems. I use field codes to add our individual names to the signature of generic letters we have to send out, so I am presuming that I can get this info into the title.

I would however, like this to be hidden from the user, so once they click the submit button, everything disappears without any dialog boxes to save or close. The only way they should be able to find out what has happened is if they look in their sent items and see the email has been sent to myself.

As for the return message, I can setup a rule in outlook to pick up any emails with the document title in it that is set by the coding and have an auto-reply sent back to it that way.

Everything else is fine, apart from the save as part, so if you could send me the code that would be much appreciated. Once I have it sorted I will put up the code for others to use/follow.

Best regards

[Tinbendr]

I need to have the actual document as an attachment for audit purposes. Ok, then. You just need a hard copy on your end? Does the hard copy HAVE to come from the users computer? Could you run a macro, say from Word, that would iterate through your mailbox, picking out the Reserve seat e-mails and printing them out? (I'm not try to circumvent any requiremnt, just looking for less user intervention/resources.) If that will meet the requirements, then I would suggest a userform that would show upon the opening of the template. Collect the information from user, then stuff that in an email to you.

You would run the another maco on your machine (maybe once a day) to printout the orders.


As for the return message, I can setup a rule in outlook to pick up any emails with the document title in it that is set by the coding and have an auto-reply sent back to it that way.
Of course... I was thinking code wise. (just overthinking it.)

[fumei]

"I need to have the actual document as an attachment for audit purposes. "

How on earth does that fit with your statement: "I am not really bothered if it saves or not"

This is a contradiction. But in any case, you MUST save it in order to send a file as an attachment. Attachments are files, therefore you must have a file. A Word document on screen is NOT a file until you save it.

I did not change your code at all, except of course to use my email address.

"would it be possible to specify a set filename and path that can also pull the users name and place it in the document title"

Yes, absolutely.

username can be returned programmatically by:
Environ("UserName")


So, for me, this returns: gerry.knight

It is the system (Windows) logon name.

Now this is simply a string. You may (and I would recommend you do so) want to replace any "." you may have in your logon names, if you are going to use it in a filename.

Replace(Environ("username"), ".", "_")

So......add this to your code to "set filename and path that can also pull the users name and place it in the document title"


ActiveDocument.SaveAs Filename:="THE_PATH\" & _
Replace(Environ("username"), ".", "_") & ".doc"


YOU must supply THE_PATH. Make sure you put the "\" at the end.

ActiveDocument.SaveAs Filename:="U:\Whatever\SentDocs\" & _
Replace(Environ("username"), ".", "_") & ".doc"


will save the document as: U:\Whatever\SentDocs\gerry_knight.doc


Then, in your Outlook part of your code:

.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue
will send the document.