Word userform values cleared on open as email attachment - want them to stay

[lrtaylor]

Hi,

New to vba so don't have much idea what I'm doing.

I'd like a userform that the user completes (form located on intraet), then emails the completed userform to predefined multiple users with a predefined subject.

I've created the userform, and it loads as an attachment, however when the recipient opens the attachment the form is reset and the entered values aren't there. I'm thinking it might be an initialize thing but really don't have a clue.

Also, if you get the urge, with the Private Sub txt_fw_Exit(ByVal cancel As MSForms.ReturnBoolean) the user has to hit 'cancel twice' instead of once.

Private Sub UserForm_Initialize()


'populates business and cost centre combo boxes
cmb_employer.AddItem "Avalon Property Group"
cmb_employer.AddItem "Media Six"
cmb_employer.AddItem "Bond (Gold Coast)"
cmb_employer.AddItem "Makbuild Homes"
cmb_employer.AddItem "North Brisbane Homes"
cmb_employer.AddItem "RB Douglas"
cmb_employer.AddItem "Port Macquarie"
cmb_employer.AddItem "Townsville"

cmb_costcentre.AddItem "Sales"
cmb_costcentre.AddItem "Construction Admin"
cmb_costcentre.AddItem "Management"
cmb_costcentre.AddItem "Finance&Admin"
cmb_costcentre.AddItem "Development"
cmb_costcentre.AddItem "Construction"
cmb_costcentre.AddItem "DesignBuild"
cmb_costcentre.AddItem "Home Expert Centre"
cmb_costcentre.AddItem "Windemere"


cmb_locn.AddItem "Office"
cmb_locn.AddItem "Display"
cmb_locn.AddItem "Home Expert Centre - SC"

cmb_emptype.AddItem "Full-time"
cmb_emptype.AddItem "Part-Time"
cmb_emptype.AddItem "Casual"
cmb_emptype.AddItem "Fixed term"
cmb_emptype.AddItem "Contractor"

cmb_buscards.AddItem ""
cmb_buscards.AddItem "250"
cmb_buscards.AddItem "500"
cmb_buscards.AddItem "1000"

End Sub


Private Sub chb_fw_change()
'this reveals/hides the framework (fw) 'same as who' text box

If chb_FW.Value = False Then
lb_fw.Visible = False
txt_fw.Visible = False

Else
lb_fw.Visible = True
txt_fw.Enabled = True
txt_fw.Visible = True
txt_fw.SetFocus

End If
End Sub

Private Sub txt_fw_Exit(ByVal cancel As MSForms.ReturnBoolean)
' this opens a message box where hitting 'ok' returns you to enter a name in the textbox, _
and cancel unchecks the checkbox and hides the textbox.

If txt_fw = "" Then

If MsgBox("You must enter a user profile to clone. Hit OK to enter a user name or cancel for no Framework access", _
vbOKCancel) = vbCancel Then

chb_FW.Value = False
cancel = False


Else

cancel = True

End If

End If

End Sub




Private Sub chb_tl_change()
'this reveals/hides the timberline (tl) 'same as who' text box
If chb_TL.Value = False Then
lb_TL.Visible = False
txt_TL.Visible = False

Else
lb_TL.Visible = True
txt_TL.Visible = True


End If


End Sub

Private Sub chb_drive_change()

If chb_drive.Value = True Then

txt_drive.Visible = True

Else

txt_drive.Visible = False

End If
End Sub

Private Sub cb1_Click()
Me.PrintForm
End Sub

Sub cb_2_click()

Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim oSubject As String



On Error Resume Next

'Start Outlook if it isn't running
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
End If



'Create a new message
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Recipients.Add Name:="[email protected]"
.Recipients.Add Name:="[email protected]"
.subject = ("System Setup") & (" ") & txt_employee.Value
.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue, _
DisplayName:="Document as attachment"
End With

'Display the message
oItem.Display


'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing
Set objInsp = Nothing
Set wdEditor = Nothing

End Sub

[westconn1]

i think you would need to do a saveAs of the document, or print to a pdf or something to retain the forms fields, then attach the new file

[lrtaylor]

Hi Pete,

Yeah I thought of those 2 as well. The first I want to avoid, as there is no reason for the user to keep a copy of the document.

The 2nd = I can't find any code that would allow me to pdf the userform and attach to an email. I've found code that will allow me to pdf the activedocument but not the userform.

I've also thought of placing the checked checkboxes and contents of textboxes (next to corresponding labels) in the body of an email but just don't know how to do this.

Also thought of capturing an image of the userform and inserting as body in an email, but again, no idea how to acocmplish this.

I'm using word 2007.

[westconn1]

sorry, misread, thought you were using a forms document

you could make a word template to fill with details from the userform, word 2007 has provision to save as pdf
i would be creating and email body using the values from the userform

you can always delete previous files within your code

[si_the_geek]

Thread moved from 'VB6' forum to the 'Office Development/VBA' forum (while VBA and VB6 have some similarities, they are not the same thing)