Write a title/header/anything in a Word doc via VBA and then retrieve it via VBA

Hi All

I have a macro that I run in Outlook. At the end of that macro there is this code:

job = ImputBox("Enter Job Number")


Dim word As Object
Set word = CreateObject("Word.Application") 
Template = "C:\Users\tbaker\Documents\Template.docm" 


With word 
    .Visible = True
    .Application.Activate 
    .Documents.Open (Template) 
End With

I am wondering if there is some code I can put into the "With word" block that would write the "job" variable into the Word document, and then some Word VBA code to read this "job" variable for use inside some Word macros saved in the Template.docm file that this code is opening.

I don't have any idea how to write this, but something like this mock code:

With word
     .Visible = True
     .Application.Activate
     .Documents.Open (Template)
     .Write.Header = job 'made up code haha I have no idea what the code would actually be but you get the idea
End with

And then in the Template.docm file a macro would be something like this:

Sub readjob()


Dim job As String
job = Header.Value 'Completely made up code but you get the idea, its passing the variable from Outlook into Word via writing it in the word document and then VBA reading it again into a variable in Word

Is anything like this possible? I imagine (though I haven't tested it) that it would be possible in excel by using something like "Excel.Sheets("Sheet1").Range("A1") = job" and then in excel "job = Sheets("Sheet1").Range("A1")" or something similar, but don't have any idea how/if its possible with word and outlook?

Thanks in advance.

Re: Write a title/header/anything in a Word doc via VBA and then retrieve it via VBA

With a little googling I found this: http://msdn.microsoft.com/en-us/library/office/ff194465.aspx using the second vba example could I use something like the below in my Outlook code:

ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.Text = job

I have tried various positions of the this in:

job = ImputBox("Enter Job Number") 


Dim word As Object
Set word = CreateObject("Word.Application") 
Template = "C:\Users\tbaker\Documents\Template.docm" 


With word 
    .Visible = True
    .Application.Activate 
    .Documents.Open (Template) 
End With

But nothing has worked so far??

Cheers

Re: Write a title/header/anything in a Word doc via VBA and then retrieve it via VBA

Quote

I'm trying to understand the sequence of events - unfortunately there's simply not information information as to how your code is used.

For example, storing the Job number somewhere (I'd suggest the template is not the place, though) is fine as long as the user runs the Word portion of the process before the Outlook process runs/is run again... and you can guarantee that happens every time.

Heres the sequence of events:

IN OUTLOOK:

- User gets an email to start a new job.

- With this email selected in Outlook, they run the macro I have created(via a customs ribbon button), the first part of the macro asks the user for a job number (which they designate starting here, this job number does not exist previously). This is currently captured by Outlook VBA using a "job = InputBox("Enter Job Number")" variable.

- The second part of the macro creates a job folder (using the entered job number, currently using "MkDir "C:\Users\tbaker\Documents\Jobs\" & job"), it then saves all the attachments in the selected email into this folder.

- The third part of the macro is what I am working on above, it simply opens the Word template.docm.

IN WORD:

- Upon the opening of the template.docm there is some Word macros that automatically run that generates a document based on user selections (via some Userforms in this template.docm).

- Here I currently have the user entering in the job number again using a "job = InputBox("Enter Job Number") variable. This is what I am hoping to eliminate by somehow passing the first initially entered job number from Outlook into Word.

- The document is then saved as a PDF using the job number as a file name, and saved into the newly created job folder (created in Outlook VBA).

- The next part of the macro then creates an email, and now attachs all the documents in the jobs folder (this is what my other thread was about, which is now solved ish). Once send the macro closes the Word template.docm (without saving over it so it can be used afresh again), and returns to the user to Outlooks from whence they started.

Quote

What you really need is some persistent, external, storage so that the Word macro can query that, and then say to the user "You have Jobs 123, 456 & 789 to process - Please select..."

Unfortunately this is the very start of the job creation process, so the user is essentially creating the job from the start, there is nothing saved anywhere from where we could generate a list of jobs to process.

Quote

Another comment is that TBAKER might be using this, but TBAKER gets promoted/a better job elsewhere/wins the lottery (Won't mention falls under a ...), you're going to have to revise all the code... you need to cover that eventuality now rather than later.

I'm tbaker, and yes I am aware I'll need to amend it later, I am producing the series of macros and testing locally, once completed, tested and ready to roll out the directory will be a generic server storage that all users can access. I've already put the bulk of the directory path into a variable, so it will just be a matter of changing two lines of code (in Outlook and Word) to fix it for everyone.

I hope this better explains the situation. Thanks for all your insight thus far.

Cheers

Re: Write a title/header/anything in a Word doc via VBA and then retrieve it via VBA

Hi All Again

I have been messing around with this solution.

This is the code now in Outlook:

Dim job As String
job = InputBox("Enter Job Number")


Dim objWord As Object
Dim FileName As String
Set objWord = CreateObject("Word.Application")


objWord.Visible = True
objWord.Documents.Open "G:\Airfreight_Export\Export Airfreight\Template.docm"
objWord.Activate


With objWord.ActiveDocument
.Bookmarks("Job").Range.Text = job
.Application.Run MacroName:="Run"
End With


Set objWord = Nothing

This now carries across the job number into a bookmark in the Word template.

In the Word template I now have this code which I was hoping would read the bookmark (and hence the job number carried across from Outlook):

Dim job As String
Selection.GoTo What:=wdGoToBookmark, Name:="Job"
Selection.Expand wdLine
job = Selection.Text
MsgBox ("Click Print Screen")
Selection.Paste


Dim DocName As String
DocName = "N:\Customs\Customs_Docs\" & job & "\" & job & ".pdf"
ActiveDocument.SaveAs2 FileName:=DocName, FileFormat:=wdFormatPDF
ActiveDocument.Saved = True

But now I am getting a run time 4198 command failed error on the "ActiveDocument.SaveAs2 FileName:=DocName, FileFormat:=wdFormatPDF" line.

When I hit debug and hover over DocName it comes up correctly, showing the full path as well as the job number (carried from Outlook) with .pdf on the end. It all looks in order but its just not working???

Any ideas?

Re: Write a title/header/anything in a Word doc via VBA and then retrieve it via VBA

This is what I have in Word now:

Dim job As String
Selection.GoTo What:=wdGoToBookmark, Name:="Job"
Selection.Expand wdLine
job = Selection.Text
Selection.GoTo What:=wdGoToBookmark, Name:="Screen"
Selection.Paste


Dim DocName As String
DocName = "N:\Customs\Customs_Docs\" & job & "\" & job & ".pdf"
ActiveDocument.ExportAsFixedFormat DocName, 17
ActiveDocument.Saved = True

When I step through this code (and also using a MsgBox(job) line to check) it works as intended, the text populates into the "job" variable correctly etc. However when it gets to the Export line it errors out, now with a "That is not a valid filename" error??

When you debug and hover over DocName, it equals "N:\Customs\Customs_Docs\test5\test5.pdf" (test5 begin this example). I can't see whats wrong with this? I can't see any invalid characters or anything?

I also tried this code instead:

ActiveDocument.ExportAsFixedFormat Filename:=DocName Type:=wdExportFormatPDF

But with this code I get a Name Arguement error on both "Filename:=" and "Type:="

Any other ideas?

Thanks in advance.

Re: Write a title/header/anything in a Word doc via VBA and then retrieve it via VBA

Realised I messed up the second format code so here is what I tried now:

ActiveDocument.ExportAsFixedFormat OutputFileName:=DocName, ExportFormat:=wdExportFormatPDF

Now this gets the "This is not a valid filename" error the same as the other code above, so I guess both are identical.
I tried just manually typing in the file name so that the code was this:

ActiveDocument.ExportAsFixedFormat OutputFileName:="N:\Customs\Customs_Docs\test1\test1.pdf", ExportFormat:=wdExportFormatPDF

And low and behold this worked, it saved the document as a pdf under that folder and name???? So there must be something wrong with how vba generates the "DocName" variable. It looks identical to me when I hover over it though??

Check for yourselves, this is a picture of me hovering over the DocName variable when I have debugged:

[Blocked Image: http://i630.photobucket.com/albums/uu22/stildawn/whyisntthisworking_zps286af881.jpg]

Looks identical to what I've manually typed out right?

So I think it all comes back to making the "job" variable from reading the Job bookmark. I don't know how but somehow I believe this is messing it all up.

Cheers