Solved: Email Button Isuue

[mugcy]

I have a small situation with VBA for word.

We have some payroll info forms (as in the attachment) that we send to our stores.

I need to have a button so when that's presses it can be mailed to a specific email address. I don't want the initial form to be saved on the strore computer as it is to be left as a template.

I found some code on the net and created the button. But the problem now is when the employee fills the form and button is pressed it only sends the unfilled form to us. Is there anyway to fix this?

Please help

The Code is
Private Sub Email_Form_Click_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



With EmailItem
.Subject = "Application For Leave Form"
.To = "payroll@abc.com"
.Importance = olImportanceHIgh
.Attachments.Add Doc.FullName
.Display

End With

Application.ScreenUpdating = True
Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing
End Sub

[Ken Puls]

I didn't actually look at the attachment, but try this. The email address validation is very dirty (it only checks for an @ and a . following the @) but it may be enough for your purposes:

Private Sub Email_Form_Click_Click()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Dim EmailTo As String
Dim bFailed As Boolean

EmailTo = InputBox("Enter the address you want to send this to", _
"Send Email To")
If Not InStr(1, EmailTo, "@") > 1 Then bFailed = True
If Not InStr(1, EmailTo, ".") > InStr(1, EmailTo, "@") Then bFailed = True

If bFailed = True Then
MsgBox "Email address does not appear valid", _
vbOKOnly + vbCritical, "Error"
Exit Sub
End If


Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument



With EmailItem
.Subject = "Application For Leave Form"
.To = "payroll@abc.com"
.Importance = olImportanceHIgh
.Attachments.Add Doc.FullName
.Display

End With

Application.ScreenUpdating = True
Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing
End Sub

Also, just an observation... you're using late bound code with constants. To use the constants you must be using early bound code, with a reference set in the IDE to the Outlook library. You should probably throw in the towel one way or the other. (Either change your olMailItem to the appropriate values, or change your "As Object" to the actual object types.)

[mugcy]

Thank you very much for the code Ken,

But the problem is still that the attachment in the email dosen't have the changes made when updating the form.

I'm very new to VBA and not quite sure about the object types.

[Ken Puls]

Hmm... have you actually saved the document? I'd try that first. If it doesn't work, then you could save a copy and email that.

Adjust the middle of your code from:
Set Doc = ActiveDocument
To

Set Doc = ActiveDocument
Doc.Save

If that doesn't work, we'll see about using a temp file.

[mugcy]

What if I don't want that document to be saved in the computer so it can be left as a template for later use. how can I attach the docement then?

If I were to use a Temp file how can I delete it at the end?

[mugcy]

I tried doing the following. But cannot delet file since it will still be open.




Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)


Set Doc = ActiveDocument
Doc.SaveAs ("C:\pay\test.doc")


With EmailItem
.Subject = "Application For Leave Form"
.To = "PAYROLL@abc.com.au"
.Importance = olImportanceHIgh
.Attachments.Add Doc.FullName
.Display

End With




Application.ScreenUpdating = True
Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing

Kill ("c:\pay\test.doc")
End Sub

[Ken Puls]

A little mod at the end should take care of it, I think. I haven't tested this myself, but...


Application.ScreenUpdating = True
Doc.Close
Set Doc = Nothing

[mugcy]

I tried but still getting error"Runtime-error 70, Permision Denied"

Private Sub Email_Form_Click_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 ("C:\pay\test.doc")


With EmailItem
.Subject = "Application For Leave Form"
.To = "payroll@abc.com.au"
.Importance = olImportanceHIgh
.Attachments.Add Doc.FullName
.Display

End With




Application.ScreenUpdating = True
Set OL = Nothing
Set EmailItem = Nothing

Kill ("c:\pay\test.doc")
Doc.Close
Set Doc = Nothing
End Sub

[Ken Puls]

Ah! Sorry, I'm an idiot. Too early in the morning when I posted that, I think. Try this:

Private Sub Email_Form_Click_Click()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Dim sTempFilePath As String

Application.ScreenUpdating = False
sTempFilePath = ("C:\pay\mytest.doc")

Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)

Set Doc = ActiveDocument
Doc.SaveAs ("C:\temp\test.doc")

With EmailItem
.Subject = "Application For Leave Form"
.To = "payroll@abc.com.au"
.Importance = olImportanceHIgh
.Attachments.Add sTempFilePath
.Display
End With

Application.ScreenUpdating = True
Set OL = Nothing
Set EmailItem = Nothing

Kill sTempFilePath
Doc.Close
Set Doc = Nothing
End Sub

Make sure you have a C:\temp folder that you can write to here. (Or change it to something else.) The key is that you're trying to save a file with the same name to another directory. That way when it's attached to the email the name remains.

Let me know if it works.

[mugcy]

I assume the main template file is "Mytest.doc" which is in the "pay" folder. But this script deletes this file. But I need the "Mytest.doc file to stay there without deleting and changing. The "test.doc" file should get deleted.

[fumei]

"The "test.doc" file should get deleted."

Then change the deletion instruction to that file.
Private Sub Email_Form_Click_Click()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Dim DocToKill As String

Application.ScreenUpdating = False

Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)

Set Doc = ActiveDocument
Doc.SaveAs ("C:\temp\test.doc")
' your Active Document is now saved
' get the PATH!
DocToKill = Doc.FullName
' now close the active document
Doc.Close
Set Doc = Nothing

With EmailItem
.Subject = "Application For Leave Form"
.To = "payroll@abc.com.au"
.Importance = olImportanceHIgh
' this attaches "test.doc"
' what WAS the active document
.Attachments.Add DocToKill
.Display
End With

Application.ScreenUpdating = True

' normally setting objects to Nothing is done
' as last in, first out...
Set EmailItem = Nothing
Set OL = Nothing

' this deletes "test.doc"
Kill DocToKill
End Sub

[mugcy]

thanks a lot guys