MS Word and Visual Basic

[Neutron]

I've been given the task to take an existing word document and add a button that says submit. What this button will do is save the word document as a certain file name, and attach it to an email with the To: and Subject: fields already filled out.

How do I do this?

 

[SatinKzo]

Here is how I did something similar in Excel years ago to save it as a PDF

Sub PDFmake()

Range("D6").Select
Dim PDFFilename As String
Dim oApp As Object, oMsg As Object, MyPath As String
Dim OutputString As String
Let PDFFilename = Application.ActiveCell & ".pdf"
Let MyPath = "C:\Documents and Settings\" + UserNameWindows() + "\My Documents"

OutputString = MyPath & Application.PathSeparator & PDFFilename

SendKeys "%F"
SendKeys "P"
SendKeys "%m"
SendKeys "a"
SendKeys "~"
SendKeys "%r"
SendKeys "%v"
SendKeys "~"
SendKeys "~"
SendKeys OutputString & "{ENTER}", False

End Sub

It can be done easier with objects and such, but I used the Sendkeys commands cause I had problem with virusscan not liking object calls and such.

Here is how I had it do the email (assuming using outlook)

Sub email()
Dim MyPath As String, PDFFilename As String, OutputString As String
Dim objOL As New Outlook.Application
Dim objPO As String, objCust As String
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
Let MyPath = "C:\Documents and Settings\" + UserNameWindows() + "\My Documents"
'Let MyPath = "Z:\TMP PDF"
Range("D6").Select
Let objCust = Application.ActiveCell
Let PDFFilename = Application.ActiveCell & ".pdf"
Range("E7").Select
Let objPO = Application.ActiveCell
OutputString = MyPath & Application.PathSeparator & PDFFilename
With objMail
' .To = "blah@blah.com"
.Subject = "text here" & PDFFilename
.Body = "Body test here " & objCust & " extra stuff " & objPO
.Attachments.Add OutputString
.Display
' .Send
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub

Notice that some stuff in the email part is commented out, it does not automatically send, but the line is there

Like I said it is not perfect in design as it is a few years old, but it works for everyone that has used it. It should at least get you started. I'm sure someone here has a more up to date method, one that is even a bit more refined.

Edit:

forgot, in the pdfmake part, I call this fucntion to get the username from the system

Function UserNameWindows() As String

Dim lngLen As Long
Dim strBuffer As String

Const dhcMaxUserName = 255

strBuffer = Space(dhcMaxUserName)
lngLen = dhcMaxUserName
If CBool(GetUserName(strBuffer, lngLen)) Then
UserNameWindows = Left$(strBuffer, lngLen - 1)
Else
UserNameWindows = ""
End If
End Function

 

[mastermesh]

Go to the library and find yourself some books on vba (visual basic for applications).

 

[SatinKzo]

Go to the library and find yourself some books on vba (visual basic for applications).

Yes, if for nothing else then a refernce guide.

 

[Neutron]

Here's what we've done so far:

Recorded a Macro that saves the word document and attaches it to a new email.


Now how to we get it to autopopulate the To: Field and Subject line with what we want?

 

[SatinKzo]

In the second snippet of code I posted, look at the part where it says OBJMAIL, there are properties associated with it

.subjet, .to, .body etc.

here's some examples
Excel VBA Examples(2)