Solved: Save and Email word file - Need Help

[megha]

I am using the following code to email word document (its actually a form) as attachment and to save it to my "P:/" drive. It seems working but the code will need to modify for the following reasons:

Once I am done filling out the form and hit the command button to run the macro… the document (form) actually sent to email and also saved to my "P:/" drive successfully but its over saved the master file. I need the form to be blank at all time.
Also, I want to have the files to be stamp with date and time (as a file name for the form) that going to be email and save to "p:/" drive.I will appreciate any help. Thanks!!
The code I am using is:
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.SaveAs
With EmailItem
.Subject = "Insert Subject Here"
.Body = "Insert message here" & vbCrLf & _
"Line 2" & vbCrLf & _
"Line 3"
.To = "name@company.com"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Doc.FullName
.Send
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing

Flag = True
sPath = "P:\"
ActiveDocument.SaveAs FileName:=sPath & ActiveDocument.Name
Flag = False
Application.Documents.Close
CommandButton1.Visible = False

End Sub

[fumei]

Change your code to save the file first, then attach that file.

"its over saved the master file. I need the form to be blank at all time."

It should be a template file, thus the created document will not affect the "master" - the template .dot file.

[megha]

How should I modify this code? Can you please suggest?

[megha]

Thanks, Fumei. It does work. I change my code to save file first then email it.

I have one more question. How should I modify my code to stamp date and time for the file name?

[fumei]

Get the date and time and use it for the SaveAs filename. Use the Format function for the date/time. Since you did not state exactly how you want that, it is impossible to say. For example:

Format(Now, "mmmmddyyyy")
comes out as: October292009

Format(Now, "ddmmmmyyyy")
comes out as 29October2009

There are many many ways to format a date, including time.

However, again, it appears you are NOT using a template, but a re-used document.

[megha]

I am using the following code that 'SaveAs' the file first to my "p:/ drive" then emailing it as an attachment. I want to have my each file to be name as current date and time. Can someone please help me with it? I would appriciate if you can tell me where and what exactly need to be modify in this code as I am not at all an expert with VBA. Thanks!

Private Sub CommandButton1_Click()
Flag = True
sPath = "P:\test\"
ActiveDocument.SaveAs FileName:=sPath & ActiveDocument.Name
Flag = False


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

With EmailItem

.Subject = "Insert Subject Here"
.Body = "Insert message here" & vbCrLf & _
"Line 2" & vbCrLf & _
"Line 3"
.To = "name@company.com"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Doc.FullName
.Send
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing



Application.Documents.Close
CommandButton1.Visible = False



End Sub

[RolfJ]

Try:



ActiveDocument.SaveAs _
FileName:=sPath & _
Format(DateValue(Now()), "mmm_dd_yyyy") & "_" & _
Format(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())), "hh_mm_ss_AM/PM")


Hope this helped,
Rolf

[megha]

Thanks Rolf. It does work.

The emails are also saving to the sent item which is I don't want. Can someone please help?

The code I am using is:

Private Sub CommandButton1_Click()
Flag = True
sPath = "P:\test\"
ActiveDocument.SaveAs FileName:=sPath & ActiveDocument.Name
Flag = False

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

With EmailItem
.Subject = "Insert Subject Here"
.Body = "Insert message here" & vbCrLf & _
"Line 2" & vbCrLf & _
"Line 3"
.To = "name@company.com"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Doc.FullName
.Send
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing


Application.Documents.Close
CommandButton1.Visible = False


End Sub

[fumei]

Please use the VBA code tags when posting code. Thanks.

Having it also saved in Outlook in the Sent folder is standard for Outlook. To change this, either change Outlook itself, or using your instance of Outlook, delete the last MailItem in the Sent Folder BEFORE you destroy your instance of Outlook (Set OL = Nothing).

BTW: it is a good practice to create/destroy objects (instances) on a standard first in / last out basis. In other words, instead of:
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)

' stuff

Set OL = Nothing
Set EmailItem = Nothing
it would be:
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)

' stuff

Set EmailItem = Nothing
Set OL = Nothing