To save, name and email a document

[Kinell]

Hi

My experience of coding is very limited, so any help with this would be most appreciated. Here's my problem:

I have a team of staff who are very good at their technical jobs, but very poor at saving documents in the correct location and even worse at giving those documents meaningful names. I have set up a single page Word document (Word 2000) which is a non-compliance report that we send to a particular contractor in the event of a - well...non-compliance. The document comprises about six text form fields which my staff fill in. The document is then saved to a particular folder and a copy of the document emailed to said contractor.

I would like to include a Command Button on the document with code assigned to perform the following with one click of the button.

1. Save a copy of the completed document to a specific folder on a networked drive;
2. Name this copy with data from one of the text form fields (a six digit reference number) preferably prefixed with the letters NCR;
3. E mail (as an attachment in Outlook 2000) to the contractor (preferably with the recipients e-mail address automatically generated and if possible a short message automatically generated).

I hope that I'm not asking for the impossible, and I'm sorry I have no code at all as yet. :dunno

[CreganTur]

Welcome to the forum- always good to have new members!

You'll be glad to know that what you want to accomplish is very possible.


1. Save a copy of the completed document to a specific folder on a networked drive;
The code to do this is pretty simple:
ActiveDocument.SaveAs FilePath
Now, you will need to repalce FilePath with the actual filepath to your network drive. It will need to contain the name you want to save the document as, as well as the document's file extension. Example: C:\Folder\Document.doc


2. Name this copy with data from one of the text form fields (a six digit reference number) preferably prefixed with the letters NCR;

This is also easy, and it builds off of the code provided in part 1 of your question. On your document right click on the form field that contains your reference number and select Properties. You'll notice under Field Settings that you'll see Bookmark. This is the bookmark name that refers to your formfield. We will get the reference code by grabbing the bookmark's value. Something like:
Dim DocName As String
DocName = ActiveDocument,Bookmarks("BookmarkName").Value
ActiveDocument.SaveAs "C:\Folder\" & "NCR" & DocName & ".doc"
The above code first declares a variable that will hold your reference number- called DocName.
Next we grab the value of your reference number formfield's bookmark- replace "BookmarkName" with the actual name of the bookmark. Give the bookmark a unique name- don't use the default Text3, or whatever it is.
Finally, we put it all together with the SaveAs method.


3. E mail (as an attachment in Outlook 2000) to the contractor (preferably with the recipients e-mail address automatically generated and if possible a short message automatically generated).

The code for creating an outlook e-mail would look something like this:
Dim appOutlook As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim objMailItem As Outlook.MailItem
Set appOutlook = New Outlook.Application
Set objNameSpace = appOutlook.GetNamespace("MAPI")
Set objMailItem = appOutlook.CreateItem(olMailItem)
With objMailItem
.To = strRecipient
.Subject = strSubject
.Body = "Message"
.Attachments.Add "C:\test.xls"
.Display
End With
Set objMailItem = Nothing
Set objNameSpace = Nothing
Set appOutlook = Nothing

HTH:thumb

[Dragon71]

Sorry fairly new to VBA coding and just trying to get a worksheet to do the above but in Lotus Notes
Unfortunately that is the mail system we are stuck with at work,so not other option is available
How could i modify the above code to do the same thing in Lotus Notes
Have tried replacing all the Outlook references with nlnotes(the Lotus executable) but does not seem to work??
:help

[fumei]

You will need to look up the relevant objects in Lotus Notes.

BTW: you did say Outlook.

"3. E mail (as an attachment in Outlook 2000) to the contractor (preferably with the recipients e-mail address automatically generated and if possible a short message automatically generated)."

Cregan, it is generally better to use the actual object. as these are formfields, then use formfields.

ActiveDocument.Formfields("Name").Result

Good advice regarding the naming, re: not "Text3".

Even though Properties of the formfield has "Bookmark name", this is inaccurate. True, if you replace "Text3" with "Yadda", then "Yadda" will show up under Insert > Bookmarks.

However...if you delete the bookmark "Yadda", and look at the Properties of the formfield, it will STILL show as Bookmark name "Yadda".

Formfields have - or have NOT - bookmarks, but they are not bookmarks themselves.

In the case above (deleting the bookmark)

ActiveDocument.Bookmarks("Yadda").Value would give a run-time error...but:

ActiveDocument.Formfields("Yadda").Result would still return the contents of the formfield.

[CreganTur]

The only other option I can think of is to use MailTo, which will launch the default e-mail application. I don't know how to add attachments to it, but you might be able to find something.

This code uses the API ShellExecute to launch MailTo:

Private Sub Email_Click()
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Long, _

ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Const SW_SHOW As Long = 5



Sub SendEmail(strTo As String, strSubject As String, strBody As String)
Dim rc As Long
Dim strFile As String
'build the lpFile argument
strFile = "Mailto:" & strTo
strFile = strFile & "?subject=" & strSubject
strFile = strFile & "&body=" & strBody
rc = ShellExecute(0, "open", strFile, "", "", SW_SHOW)



End Sub

[fumei]

BTW: as formfields have bookmarks, and bookmarks can be nested, it is very possible - and sometimes useful - to make a bookmark within the contents of a formfield.

[CreganTur]

gerry,

So you're saying that it's better practice to refer to the Formfield object when a Formfield is used, instead of referencing the underlying bookmark? Makes sense with the description you provided- thanks :thumb

[Dragon71]

Code i have to open the email that is working at the moment is


Private Sub Email_Click()
ActiveDocument.FollowHyperlink _
Address:=test@hotmail.com

End Sub


This opens Lotus notes and stipulates the address shown
Just cant work out how to attach the document once this is done
Tried converting the Outlook stuff as advised,but does not work

[geekgirlau]

Untested, but a quick Google search found the following:

http://www.fabalou.com/VBandVBA/lotusnotesmail.asp
http://www.forumtopics.com/busobj/viewtopic.php?t=26805
http://www.ozgrid.com/forum/showthread.php?t=25809

[Dragon71]

Thanks guys........had a quick look and looks like some good useful info
Bit busy so will have a look asap

Once again thanks for the help

:bow:

[fumei]

mailto protocol does not handle attachments.

cregan: "So you're saying that it's better practice to refer to the Formfield object when a Formfield is used, instead of referencing the underlying bookmark? "

That is indeed that I am saying. Except to call it the underlying bookmark is kind of wonky. If what you want is the content of the formfield then yes, use the object that has the content. Yes, it is true - and can be used this way - that the Range of the formfields (used to create the bookmark) has the content. That is true, but it is an some ways an indirect container, PLUS the container may not even actually exist.

That is, the formfield (with its contents) exists, but there is NO bookmark.

This is exactly what happens if you copy and paste formfields. If you copy "Text2" and paste it...well you can not have two "Text2"...so one of them ends up with a blank (non-existent) Bookmark name.

More than that, it does not HAVE a bookmark. The formfields exists, but does not HAVE a bookmark.

BTW: did you actually execute your code???:
Dim DocName As String
DocName = ActiveDocument,Bookmarks("BookmarkName").Value


1. there is a syntax error (the comma before Bookmarks)

2. bookmarks do not HAVE a .Value property

Using .Value like that should get you a compile error.