Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-13-2012, 11:20 PM
bigbird69 bigbird69 is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 64bit
Novice
Command button - save in temp folder and send email with attachment
 
Join Date: Nov 2012
Posts: 8
bigbird69 is on a distinguished road
Default Command button - save in temp folder and send email with attachment

Hello all,




i have spent some time making a new user form for the hr staff.

i have a command button which will save the form and attach to a new email with email address and subject fields filled in. my only problem is once the form has been filled in and sent off the info is saved into the form. is there a way around this?

it is a macro enabled form which contains a date picker, active x text box, and check boxes. i have also protected the form from being edited.


here is the current code i am using
Code:
Private Sub CommandButton1_Click()
 
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.Save
With EmailItem
  .Subject = "New company Employee"
  .Body = "" & vbCrLf & _
    "" & vbCrLf & _
    ""
  .To = "ITSupport@company.com.au"
  .Attachments.Add Doc.FullName
  .Display
End With
Application.ScreenUpdating = True
Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing
 
End Sub

Last edited by macropod; 11-14-2012 at 02:21 AM. Reason: Added code tags & formatting
Reply With Quote
  #2  
Old 11-14-2012, 02:25 AM
macropod's Avatar
macropod macropod is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi bigbird69,

That suggests you're using a document for the form, rather than a template. With a template, the data are added to its child documents, rather than to the template itself (unless you mis-code).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 11-14-2012, 02:40 AM
bigbird69 bigbird69 is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 64bit
Novice
Command button - save in temp folder and send email with attachment
 
Join Date: Nov 2012
Posts: 8
bigbird69 is on a distinguished road
Default

Hi macropod,

thank you for the reply

it is in fact a macro document the extension is .docm. i will save the doc as a template and see if this works. thanks for the heads up . by the way does the code look right?
Reply With Quote
  #4  
Old 11-14-2012, 04:33 AM
macropod's Avatar
macropod macropod is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Re the code, you'll need to change 'Doc.Save' to 'Doc.SaveAs2' and give it an appropriate path & filename at least. You might also want to specify the fileformat (which is one of the SaveAs2 parameters).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 11-14-2012, 06:09 PM
bigbird69 bigbird69 is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 64bit
Novice
Command button - save in temp folder and send email with attachment
 
Join Date: Nov 2012
Posts: 8
bigbird69 is on a distinguished road
Default

hi macropod,

im unable to use the doc as a template the command button appears to stop working .

regarding the 'Doc.SaveAs2' line how would i write the code? the path i would like to use is a network share.
would it be possible to add a line of text that can save the document under the employees name from the text-box field?
Reply With Quote
  #6  
Old 11-14-2012, 06:20 PM
macropod's Avatar
macropod macropod is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Without seeing the document/template in question, I can't really commnent on why it's not working.

For the save, you might use:
Doc.SaveAs2 FileName:=Environ("temp") & "\" & Environ("username"), Fileformat:=wdFormatDocument, AddToRecentFiles:=False
The above saves the document in the older .doc format. If you prefer the .docx format, change 'wdFormatDocument' to 'wdFormatXMLDocument'. Alternatively, if you'd prefer to send a PDF, you could use 'wdFormatPDF'.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 11-14-2012, 06:57 PM
bigbird69 bigbird69 is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 64bit
Novice
Command button - save in temp folder and send email with attachment
 
Join Date: Nov 2012
Posts: 8
bigbird69 is on a distinguished road
Default

hi macropod i have attached the document that way you can get a better view/understanding of it

Last edited by bigbird69; 11-15-2012 at 12:49 AM.
Reply With Quote
  #8  
Old 11-14-2012, 11:59 PM
macropod's Avatar
macropod macropod is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi bigbird69,

Upon opening your document, I found that you've used ActiveX Controls, Formfields and a Content Control for data entry. You really should stick to just one kind - preferably Formfields or Content Controls.

If the document is to be used by people who don't have access to Word 2007 & later, you should only use Formfields. That, of course, rules out using the date picker.

Furthermore, if the user has a Mac with Office 2008, they won't have any macro support.

I'd also suggest that, rather than forcing the document to be saved to the user's TEMP folder, you give them the option of where to save.

The attached template uses formfields, plus a macro that intercepts the document close event to prompt the user to email it. As only a saved document can be emailed, the user is also given the option to save it.
Attached Files
File Type: zip New User.zip (16.2 KB, 75 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 11-15-2012, 12:22 AM
bigbird69 bigbird69 is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 64bit
Novice
Command button - save in temp folder and send email with attachment
 
Join Date: Nov 2012
Posts: 8
bigbird69 is on a distinguished road
Default

hi macropod thanks for the help,

this document is going to be used for internal use only, the whole office is running office 2010 and windows 7.

i was going to give them the option, after much thought i have decided to keep these forms for future reference.

i have used the code you provided me previously. which is working great instead of the document saving to the temp folder it now saves on the shared drive.

one problem i have is the naming of the document.
it is saving the document under the username but im unsure how to get the document to save using the info that has been written in the ActiveX TextBox

The ActiveX TextBox i want grab the info from is named TextBox111
are you able to help? i have also copied the latest code i am using

regards matt


Code:
Private Sub CommandButton1_Click()
 
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.SaveAs2 FileName:=("S:\DATA\InformationTech\Secured\New Staff") & "\" & Environ("username"), Fileformat:=wdFormatDocument, AddToRecentFiles:=True
With EmailItem
  .Subject = "New Employee"
  .Body = "" & vbCrLf & _
    "" & vbCrLf & _
    ""
  .To = "ITSupport@compnay.com.au"
  .Importance = olImportanceHigh
  .Attachments.Add Doc.FullName
  .Display
End With
Application.ScreenUpdating = True
Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing
 
End Sub
Attached Images
File Type: png textbox111.PNG (3.4 KB, 77 views)

Last edited by macropod; 11-15-2012 at 12:27 AM. Reason: Added code tags & formatting
Reply With Quote
  #10  
Old 11-15-2012, 12:32 AM
macropod's Avatar
macropod macropod is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The template attached to my last past shows how you can extract them emplyee name from a formfield, for inclusing in both the document's filename and the email subject line. The same approach can be used with Content Controls, which is what you really should be using in an exclusively Office 2010 environment, or with ActiveX Controls.

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 11-15-2012, 12:52 AM
bigbird69 bigbird69 is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 64bit
Novice
Command button - save in temp folder and send email with attachment
 
Join Date: Nov 2012
Posts: 8
bigbird69 is on a distinguished road
Default

I completely forgot to check the code on the doc
oh thanks for the tip
Reply With Quote
  #12  
Old 11-15-2012, 07:00 PM
bigbird69 bigbird69 is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 64bit
Novice
Command button - save in temp folder and send email with attachment
 
Join Date: Nov 2012
Posts: 8
bigbird69 is on a distinguished road
Default

Hi again,

showed the girls the form you provided me and they prefer to have the Active X text box fields

i cant work how to use the code you provided to work with the code i have


your line of code - how do i use this line of code with my line code for using a textbox field?
Code:
.Name = "New Employee - " & Doc.FormFields("LastName").Result
my line of code
Code:
Doc.SaveAs2 FileName:=("S:\DATA\InformationTech\Secured\New Staff") & "\" & Environ("username"), Fileformat:=wdFormatDocument, AddToRecentFiles:=True
Reply With Quote
  #13  
Old 11-17-2012, 12:01 AM
macropod's Avatar
macropod macropod is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try the attached template. I changed the form from the use of Formfields to Content Controls (with border formatting).

FWIW, MS has largely deprecated the use of ActiveX controls because of their potential security risks. And, as I said previously, you really shouldn't mix ActiveX Controls, Formfields and Content Controls in the same document - no matter what your HR staff say they like.

Some consideration should also be given to a document structure that will facilitate data extraction by the IT staff so they can automate the creation of user accounts, etc.
Attached Files
File Type: zip New User.zip (36.9 KB, 98 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #14  
Old 11-18-2012, 10:06 PM
bigbird69 bigbird69 is offline Command button - save in temp folder and send email with attachment Windows 7 64bit Command button - save in temp folder and send email with attachment Office 2010 64bit
Novice
Command button - save in temp folder and send email with attachment
 
Join Date: Nov 2012
Posts: 8
bigbird69 is on a distinguished road
Default

thank you for all the help. this form is much better
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Command button - save in temp folder and send email with attachment Command Button doesnt work on network rmw85 Word VBA 1 04-25-2012 01:02 PM
outlook 2003 always not remove temp copy of opened attachments in temporary folder c.itech Outlook 0 06-20-2011 10:34 PM
Command button - save in temp folder and send email with attachment email as pdf attachment - subject line and attachment named after mail merge Nexus Mail Merge 12 04-13-2011 11:34 PM
Command button - save in temp folder and send email with attachment Command Button cksm4 Word VBA 7 02-27-2011 08:47 PM
How can I save multiple email messages to a file folder? (crosspost) tupham Outlook 0 08-04-2008 07:56 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:08 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft