Word to Outlook

[Brett Desper]

I am a complete newby to VBA and need some help. I am using Office 2007. We have some generic letter templates that are generated by a third program (ACT). I have already been able to get a macro that saves the current file as a PDF and then puts in into Outlook. What I need is some code that will read the content control areas in Word (specifically customer email and product description) and place it into Outlook in the TO address block and the subject block. After looking at lots of code, I am still not sure how to do this. Any help would be appreciated.
Brett

[lucas]

some code that will read the content control areas in Word

Which are what? Bookmarks, formfields, etc.?

[Brett Desper]

The first field is either imported from ACT or entered manually and includes the email address of the client. The second field is entered manually and is a description of the project. They are not bookmarks, but text.

[Brett Desper]

The first field is either imported from ACT or entered manually and includes the email address of the client. The second field is entered manually and is a description of the project. They are not bookmarks, but text.

From what I think you are talking about, these are form fields. they can be entered by hand or imported directly from another program.

[lucas]

See if this will get you started in the right direction:

Option Explicit
Sub FirstLineIsAddress()
Dim AppOutlook As Object
Set AppOutlook = CreateObject("Outlook.application")
With AppOutlook.CreateItem(olMailItem)
.To = ActiveDocument.FormFields(1).Result
.Subject = ActiveDocument.Sentences(1).Text
.Body = ActiveDocument.Content
' .Send
.Display
End With
Set AppOutlook = Nothing
End Sub

You must set a reference(in the VBE- Tools-reference) to the microsoft outlook xx.x Object library.

example attached.

[Brett Desper]

See if this will get you started in the right direction:

Option Explicit
Sub FirstLineIsAddress()
Dim AppOutlook As Object
Set AppOutlook = CreateObject("Outlook.application")
With AppOutlook.CreateItem(olMailItem)
.To = ActiveDocument.FormFields(1).Result
.Subject = ActiveDocument.Sentences(1).Text
.Body = ActiveDocument.Content
' .Send
.Display
End With
Set AppOutlook = Nothing
End Sub
You must set a reference(in the VBE- Tools-reference) to the microsoft outlook xx.x Object library.

example attached.
I keep getting errors with the .to statement saying that OUtlook has some problems with it. Do I need to put a word ref in Outlook? Where do I put the name of the fields that are in my Word doc? I have tried various places, but with no real success.

As I said, I am a total newbie at this. I am working through a rather large book on programming VBA, but this is obviously toward the back of the book. I have some code that will save my doc and attach it as a docx and pdf in outlook. It works, but I am not sure where the person working on this before me got the code (It looks like he downloaded it from Microsoft). It would be nice if I could patch this code in to the existing stuff.


I am posting it below


Sub Send_original_and_pdf()
'
' This macro creates a pdf-file of the current document and adds
' both the original and the pdf-version of the document as an
' attachment to a new Outlook message.
'
' This macro requires
' -Word 2007
' -The SaveAsPDFandXPS.exe addin to be installed
' -A reference added to the Microsoft Outlook <version> Object Library
'
' The SaveAsPDFandXPS.exe addin can be downloaded from;
' (microsoft address here)

On Error Resume Next

'Verify if the docment has been saved before so that we have a path to work with.
'If not, notify the user that there will be a safe dialog first.
If ActiveDocument.Path <> "" Then
ActiveDocument.Save
Else
Dim Msg, Style, Title, Response
Msg = "This document has not been saved before." & vbLf & _
"Please save the document to disk first." & vbLf & _
"Without saving first, only the pdf-file will be attached."
Style = vbInformation + vbOKOnly
Title = "Save current presentation"
Response = MsgBox(Msg, Style, Title)

Dim dlgSaveAs As FileDialog
Dim strCurrentFile As String
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

If dlgSaveAs.Show = -1 Then
strCurrentFile = dlgSaveAs.SelectedItems(1)
ActiveDocument.SaveAs (strCurrentFile)
End If
Set dlgSaveAs = Nothing
End If

'Get the name of the open file and strip any extension.
Dim MyFile As String
MyFile = ActiveDocument.name
intPos = InStrRev(MyFile, ".")
If intPos > 0 Then
MyFile = Left(MyFile, intPos - 1)
End If

'Get the user's TempFolder to store the created pdf item in.
Dim FSO As Object, TmpFolder As Object
Set FSO = CreateObject("scripting.filesystemobject")
Set FileName = FSO.GetSpecialFolder(2)

'Create the full path name for the pdf-file
FileName = FileName & "\" & MyFile & ".pdf"

'Save the current document as pdf in the user's temp folder.
'Note that we are going to include the document properties as well.
'If you do not want this set "IncludeDocProps" to False.
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
FileName, ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=0, To:=0, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False

'Declare an Outlook application an a mail item.
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem

'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)

'Add the attachments.
oItem.Attachments.Add FileName
oItem.Attachments.Add ActiveDocument.FullName

'Show the message.
oItem.Display

'Cleanup
Set FSO = Nothing
Set FileName = Nothing
Set oOutlookApp = Nothing
Set oItem = Nothing

End Sub


Edit: VBA tags added to code.

[lucas]

I don't see a to statement in your code.

btw, you can format your code for the forum by selecting it when posting and hitting the vba button as I have done to your code.

[Brett Desper]

The to statement was in your code that I quoted at the top. I was hoping to get some variation of your code plugged into the longer code statement I posted at the end

[lucas]

Are you saying the example doc in post 5 does not run for you?

[Brett Desper]

correct. Let me see if I can get the error code for you just a sec

[Brett Desper]

I can't get it posted right now. The error was in the .to line SOmething about Outlook not being able to handle the information

[Brett Desper]

Will try to get it posted later this evening.

[lucas]

You are talking about the actual attachment that you downloaded and ran on your computer, not just the code.

I have 2003. You said I think you have 2007

I have to think that must be the difference.

Maybe someone else will come along and test it for us. Any version of office.

[Brett Desper]

I solved the problem with the following bit of code inserted into the larger macro.

Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.To = getContent(ActiveDocument, "email")
.Subject = ActiveDocument.name
End With