Getting word to save it's content before emailing

[Mr.Petes]

Hi All,

I have created a form in VBA for rejecting certain work types. The purpose is to make it easy and time efficient for a specific business unit to reject bad applications etc.

The problem I am having is when the word doco is emailed all the fields have been reset (as they are populated by VBA code) to null values.

I am looking for a way to retain the field values intact so when the recipient open the mail the data is preserved.

I'm also having an issue where the userform_initialize isn't working whent he document starts?

Here is the code I am running:


Private Sub CmdBtnSendReject_Click()

Dim bStarted As Boolean
Dim OutlookApp As Outlook.Application
Dim Item As Outlook.MailItem
Dim TechLead As String
Dim TeamManager As String
Dim WorkPkgName As String
Dim projectName As String
Dim WpNumber As String
Dim newDelivery As Date

TechLead = cboTechLead.Value
TeamManager = cboTeamManager.Value
WorkPkgName = txtWPN.Value
projectName = txtProjectName.Value
WpNumber = TxtWPnumber.Value
newDelivery = DTPicNewDel.Value


On Error Resume Next

If Len(ActiveDocument.Path) = 0 Then
MsgBox "Document needs to be saved first"
Exit Sub
End If

Set OutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set OutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

Set Item = OutlookApp.CreateItem(olMailItem)

With Item
.To = "randomemail@fakemail.comu"
'.CC = TechLead & ";" & TeamManager
.BCC = ""
.Subject = "Rejection of Work Package Notification: " & projectName & " : " & " " & WpNumber & " - " & WorkPkgName
.Body = "Please note the delivery of this Work Package has been delayed to: " & newDelivery & " if the workpackage is amended and resubmitted today"
.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue, _
DisplayName:="Document as attachment"
.Send
End With

If bStarted Then
OutlookApp.Quit
End If

Set Item = Nothing
Set OutlookApp = Nothing

End Sub

Any help appreciated.

-Petes

[Mr.Petes]

Oh and forgot, the code works fine for populating the fields, it just doesn't run when the doc loads up for the first time.

[Frosty]

There's nothing that jumps out at me as something necessarily intrinsically wrong with your code.

Your test on whether the document is saved is really whether the document has *ever* been saved, which may not be what you want in this scenario (checking whether the .Path exists is the difference between a new document and an existing document, but the .Saved property tells you if the document has content which has not yet been saved).

If you are unprotecting and reprotecting a formfield document via code... you will clear out fields if you don't use an argument to preserve existing data. But other than that, you may need to actually upload a sample of your project.

Also, you're not showing enough code to diagnose what's going on (or failing to) when the "doc loads up for the first time" ... are you using the Document_New event? Document_Open? Automacros? Application events? There are many ways to have code run "automatically"... but a single routine from the user form isn't enough to give you advice on that for your situation.

In short: I don't have a clear enough picture what state your ActiveDocument is in when you attach it to the email you're creating to really help you troubleshoot. And the only test you have before executing the attachment is whether the ActiveDocument has *ever* been saved (so, if you save the document immediately after creation, then do a bunch of processing which fills in data, then run this macro... I suspect you're attaching the document as it existed when it was last saved, not as it currently is).

[Mr.Petes]

Hi Frosty,

Sorry I completely cut off the userform_initialize()

I've done a fair bit of work with Excel but I have to admit this is my first Word document that I'e used VBA on. usually userform_initialize does the trick but in Word it seems to completely ignore the code when loading. I know the code works.

Also needed to blank out some names for security reasons:


Private Sub Userform_Initialize()
cboTechLead.AddItem "Anoop **"
cboTechLead.AddItem "Chris **"
cboTechLead.AddItem "Carey **"
cboTechLead.AddItem "Greg **"
cboTechLead.AddItem "Joseph **"
cboTechLead.AddItem "Josh **"
cboTechLead.AddItem "Paul **"
cboTechLead.AddItem "Paul **"
cboTechLead.AddItem "Peter **"
cboTechLead.AddItem "Ray **"
cboTechLead.AddItem "Rob **"
cboTechLead.AddItem "Travis **"
cboTechLead.AddItem "External to ***"
cboTeamManager.AddItem "Jason **"
cboTeamManager.AddItem "Kevin **"
cboTeamManager.AddItem "Louise **"
cboTeamManager.AddItem "Mike **"
cboTeamManager.AddItem "Nick **"
cboTeamManager.AddItem "Paul **"
cboTeamManager.AddItem "Paul **"
cboTeamManager.AddItem "Ray **"
cboTeamManager.AddItem "Rob **"
cboTeamManager.AddItem "Ross **"
cboTeamManager.AddItem "Scott **"
cboTeamManager.AddItem "Stephen **"
cboTeamManager.AddItem "Wayne **"
CboTeamName.AddItem "Applications **"
CboTeamName.AddItem "Enterprise **"
CboTeamName.AddItem "**"
CboTeamName.AddItem "**"
CboTeamName.AddItem "Network **"
CboTeamName.AddItem "Network **"
CboTeamName.AddItem "Operational **"
CboTeamName.AddItem "**- Server **"
CboTeamName.AddItem "** - Third Level **"
CboTeamName.AddItem "Server **"
CboTeamName.AddItem "Server **"
CboTeamName.AddItem "Solutions **"
End Sub

I have uploaded a copy of the sheet, as you can see it's a work in progress. I'm just trying to get the functionality down before I add the bells and whistles.

[Frosty]

I can't actually utilize the document, as it contains a reference to Windows Common Controls-2 6.0 (SP6), which I apparently don't have on my machine. I'm surprised by that, but looking through all my references, I wasn't able to find anything which even seemed to approximate that.

However, that said... I'm not terribly well-versed in what you're trying to do here (utilize a word document as a "form").

But if I'm going to give a guess...your "Userform_Initialize" procedure in the ThisDocument code module is probably a meaningless name (i.e., it's not triggering on any sort of "event"-- so you might as well name it "ScoobyDoo" for all the good the name does). My guess is that you want to add the following to your ThisDocument module.

Private Sub Document_New()
Userform_Initialize
End Sub
That event "runs" whenever you create a new document based on this document. You can look into Document_Open if that's the better event.

But it seems as if you want "stuff" to "happen" when you open the document, and Document_New or Document_Open are probably the places to do that, for your purposes.