New to VBA - Need help with Word

[LukeA]

Hi Guys,

I've done some dev. work with VB.net, but this is my first attempt in working with VBA.

I'm working on a mail merge document, which at the moment works perfectly.

What I am trying to do is get the document to save as a certain name (taken from the mail merge doc), and then email to a specific email address with the subject line partly taken also from the mail merged doc.

At the moment, I can get the document to save as a file and then attach to an email and send.

I am struggling with getting the field info out of the doc and into the VBA code.
I.E.
I want the subject line of the header to be:
"Certificate for Order No: " & order_no field - how do I code the order_no field text?
The same applies then for saving the document. I need it to save in the following format (each part is a field on the doc)
ProductName_StartBatch_EndBatch.doc
I.E. ABC123_123A_123D.doc

I assume this is possible, I just need some pointers.

Thanks for your help.
Regards,
Luke

[Killian]

Hi Luke and welcome to VBAX :hi:

You can get to them via the Fields collection. AFAIK, members can only be referred to by index. So you can get a look at what you've got in the Immediate window with:Dim f As Field

For Each f In ActiveDocument.Fields
Debug.Print f.Index & vbTab & f.Result.Text
Next

[LukeA]

Thanks Killian, I'll see what I can do with that.

Now for something else...I said I've managed to code it so that it will save the document and add it as an attachment to an email (Outlook XP).

Is it possible to use a variable to add more than one attachment?

For example, instead of sending 3 emails for the same order, if the order number is part of the name of the saved file, and this is then stored, can you do;

Attachments.Add Where Order_No is shown in folder.files FileName

So it looks at all of the files within the current working folder, and for any files that start with the order_no, it adds as attachments...

Regards,
Luke

[Killian]

It depends how you're sending the mail. If you're using the SendMail method in Word with SendMailAttach=True then you're stuck with sending them individually. To add more attachments, you'll need to access a MailItem object by instancing the Outlook app and creating your own.
To match up the files to the email you can then use something like:
(For this example, I've conveniently assumed the filenames start with the order number :whistle: )' assuming use of the FileSystemObject where
' f is defined as a Folder object and
' fldr is defined and set as a target folder

Dim myMailItem As Outlook.MailItem

'get the order number for this doc
strOrderNum = ActiveDocument.Fields(4).Result.Text

For Each f In fldr.Files
If Left(f.Name, Len(strOrderNum)) = strOrderNum Then
myMailItem.Attachments.Add f
End If
Next f

[LukeA]

Killian, using your code above that showed a few bits with the index number in the immediate window.

Do i then need to use the index to get the text?
In .Net I was using textboxes so it was pretty easy, =textbox1.text!

Also I assume these indexes will never change unless the field is deleted from the form and re-added, then it would be the last index?

Luke

[Killian]

Do i then need to use the index to get the text?
In .Net I was using textboxes so it was pretty easy, =textbox1.text!
Also I assume these indexes will never change unless the field is deleted from the form and re-added, then it would be the last index?Yes, this will only really work if your document structure (specifically the fields used) is consistent.
So if you know the order number is in field index 2, then the order number text will be strOrderNum = ActiveDocument.Fields(2).Result.Text(with a Field item, the Result property returns a range representing the result of the field and the Text property of that range returns the string.)
If you want to emulate referring to the fields by name, I suppose you could wrap them in bookmarks - if there is one field contained in each bookmark, it will always be Field(1) for the bookmarks' range.
e.g. for a field inside a bookmark named "bmkOrderNum"strOrderNum = ActiveDocument.Bookmarks("bmkOrderNum").Range.Fields(1).Result.Text

[LukeA]

Thanks Killian, everything works apart from the email bit - I've tried to combine what you gave me with what I was already using - I think it's getting confused on f , I'm not sure what to set this as.

Here is my code:



Sub Save_Email_Document()
TempDocs = "C:\Temp_Docs"
fldr = "C:\Temp_Docs"
strOrderNum = ActiveDocument.Fields(8).Result.Text
strProdCode = ActiveDocument.Fields(11).Result.Text
strBatch = ActiveDocument.Fields(14).Result.Text
Documents(1).SaveAs TempDocs & "\" & strOrderNum & "-" & strProdCode & "_" & strBatch
Dim olApp As Object
Dim olMsg As Object
Dim MsgBody As String

Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)

With olMsg
.Subject = "Certificate for Order No: " & strOrderNum
.To = "luke.argent@somewhere.com"
.Body = "Hello World"
For Each f In fldr.Files
If Left(f.Name, Len(strOrderNum)) = strOrderNum Then
.Attachments.Add f
End If
Next f
.Display
End With
Set olMsg = Nothing
Set olApp = Nothing
End Sub

[Killian]

I mentioned earlier about using the FileSystemObject which I wrongly guessed you were already using, so I'll explain it a little more...

It's a very handy way of navigating through directory structures and getting references to files and folders, and there's a range of methods and properties available. I would recommend having a look here (http://www.devguru.com/Technologies/vbscript/quickref/filesystemobject.html) .

So to the code... you can access the object model by setting a reference (Tools>References: Microsoft Scripting Runtime) and declare the objects you needConst TempDocs = "C:\Temp"

Dim fso As Scripting.FileSystemObject
Dim fldr As Scripting.Folder
Dim f As Scripting.File

'create the FileSystemObject
Set fso = New Scripting.FileSystemObject
'set the target folder
Set fldr = fso.GetFolder(TempDocs) but since you're using late-binding for the Outlook stuff, we can do the same, so no need to set the reference and instead declare them as ObjectsConst TempDocs = "C:\Temp"

Dim fso As Object
Dim fldr As Object
Dim f As Object

'create the FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
'set the target folder
Set fldr = fso.GetFolder(TempDocs)then you can then check all the files in the target folder with'loop thru each file in the target folder
For Each f In fldr.Files
If Left(f.Name, Len(strOrderNum)) = strOrderNum Then
'do something
End If
Next f

[LukeA]

Cool, thanks for that. I think my coding is still a little rusty, I get an "Object doesn't support property or method" error.



Const TempDocs = "C:\Temp_Docs"
Dim fso As Object
Dim fldr As Object
Dim f As Object
strOrderNum = ActiveDocument.Fields(8).Result.Text
strProdCode = ActiveDocument.Fields(11).Result.Text
strBatch = ActiveDocument.Fields(14).Result.Text
Documents(1).SaveAs TempDocs & "\" & strOrderNum & "-" & strProdCode & "_" & strBatch
Dim olApp As Object
Dim olMsg As Object
Dim MsgBody As String
'create the FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
'set the target folder
Set fldr = fso.GetFolder(TempDocs)

Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)

With olMsg
.Subject = "Certificate for Order No: " & strOrderNum
.To = "luke.argent@somewhere.com"
.Body = "Hello World"
For Each f In fldr.Files
If Left(f.Name, Len(strOrderNum)) = strOrderNum Then
olMsg.Attachments.Add f
End If
Next f
.Display
End With

Set olMsg = Nothing
Set olApp = Nothing

[Killian]

oops, sorry, my bad...
should beolMsg.Attachments.Add f.Pathf is a reference to the file object, hence I needed to use the Path property to return it's path

[LukeA]

Sweet, that works a treat.

Thanks for your help on that one!

Regards,
Luke