VBA macro code for Word doc convert to PDF, then email by Outlook 365, ask "subject"

[wdg1]

How Do I make a BVA macro code in Word 365, so that my doc is converted to PDF, then asks (give Subject), and can be mailed by Outlook 365.
Thanks

[Kenneth Hobs]

Welcome to the forum! Your question is a multi-part one which is often the case for Integration questions. As such, response time might take longer.

I normally work in Excel. I created this macro by recording the macro in Word first. You can do the same. I can make this into a Word macro if needed. It can readily be used as-is in any VBA project though.

'Requires Tools > References > Microsoft Word 16.0 Object Library
Sub MakeWordPDFFile(sMSWordFilename As String, sOutputFilename As String)
Dim wdApp As Word.Application, wdDoc As Word.Document
Dim wdExportFormatPDF As Integer, wdExportOptimizeForPrint As Integer
Dim wdExportAllDocument As Integer, wdExportDocumentContent As Integer
Dim wdExportCreateNoBookmarks As Integer

If Not CreateObject("Scripting.FileSystemObject").FileExists(sMSWordFilename) Then _
Exit Sub

On Error GoTo errorHandler
Set wdApp = New Word.Application
With wdApp
Set wdDoc = .Documents.Open(sMSWordFilename)
.Visible = False
End With

If CreateObject("Scripting.FileSystemObject").FileExists(sOutputFilename) Then _
Kill sOutputFilename

wdExportFormatPDF = 17
wdExportOptimizeForPrint = 0
wdExportAllDocument = 0
wdExportDocumentContent = 0
wdExportCreateNoBookmarks = 0

wdDoc.ExportAsFixedFormat _
OutputFileName:=sOutputFilename, ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
wdDoc.Close False

errorExit:
On Error Resume Next
Set wdDoc = Nothing
Set wdApp = Nothing
Exit Sub

errorHandler:
MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
Resume errorExit
End Sub

To get the text for the Subject, one can use Inputbox() or Application.InputBox() or a Userform with TextBox control(s) or other ways. I guess that your Outlook To field's value would always be the same.

The Outlook part is easy after you have what you need above. Ron de Bruin has several examples on his site. Some are Excel based but the VBA code part is the same in Outlook less the method to get values for the properties. Most of those you would set manually except for the Subject.

e.g.

'More Excel to Outlook Examples: http://www.rondebruin.nl/win/s1/outlook/bmail4.htm
'http://www.rondebruin.nl/win/s1/outlook/signature.htm


Sub Main()
'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.
'Dim olApp As Outlook.Application, olMail As Outlook.MailItem 'Early binding.
Dim olApp As Object, olMail As Object 'Late binding.
Dim pdfPath$, oExists As Boolean

'pdfPath = "I:\FST\R&D and Projects\Samples\Sample Requests\"
pdfPath = ThisWorkbook.Path & "\"
pdfPath = pdfPath & "Order.pdf"
Worksheets(1).ExportAsFixedFormat xlTypePDF, pdfPath

On Error Resume Next
' Set olApp = New Outlook.Application 'Early binding
Set olApp = GetObject(, "Outlook.Application")
If Err Then
Set olApp = CreateObject("Outlook.Application")
oExists = True
End If
On Error GoTo 0

Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = " abc@abc.co.uk;def@def.co.uk"
'.CC = "cc@cc.com"
.Subject = "Order Attachment"
.Body = "please can we order the attached"
.Attachments.Add pdfPath, Position:=Len(.Body) + 10
.Display
'.Send
End With

Set olMail = Nothing
If oExists Then olApp.Quit
Set olApp = Nothing
End Sub




If you need more help, please post back.

[gmayor]

I would do it as follows. Note that the code calls a function to open Outlook properly which can be copied from the link. The macro asks if you want to send as PDF then saves the document as such and opens the message for you to complete.


Sub Send_As_Mail_Attachment()
'Graham Mayor = http://www.gmayor.com
'Send the document as an attachment _
in an Outlook Email message
'Requires the code from - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to either retrieve an open instance of Outlook or open Outlook if it is closed.
Dim bStarted As Boolean
Dim olApp As Object
Dim oItem As Object
Dim oDoc As Document
Dim strName As String
Dim strDocName As String
Dim strPath As String
Dim intPos As Integer
Dim iFormat As Long
Set oDoc = ActiveDocument
'Prompt the user to save the document
On Error GoTo err_Handler:
oDoc.Save
strDocName = oDoc.Name
iFormat = MsgBox("Send as PDF format?", vbYesNoCancel)
If iFormat = 2 Then GoTo lbl_Exit
If iFormat = 7 Then strDocName = oDoc.FullName: strName = oDoc.Name
If iFormat = 6 Then
'Get the document name and path
strPath = oDoc.path & "\"
intPos = InStrRev(strDocName, ".")
strDocName = Left(strDocName, intPos - 1)
strName = strDocName & ".pdf"
strDocName = strPath & strDocName & ".pdf"

'And save the document as PDF
oDoc.ExportAsFixedFormat OutputFilename:=strDocName, _
ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, _
OptimizeFor:=wdExportOptimizeForPrint, _
Range:=wdExportAllDocument, From:=1, to:=1, _
Item:=wdExportDocumentContent, _
IncludeDocProps:=True, _
KeepIRM:=True, _
CreateBookmarks:=wdExportCreateHeadingBookmarks, _
DocStructureTags:=True, _
BitmapMissingFonts:=True, _
UseISO19005_1:=False

'Now close the document without saving as we have finished with it
oDoc.Close 0
End If
'Get Outlook if it's running
Set olApp = OutlookApp()
On Error GoTo 0
'Create a new mailitem
Set oItem = olApp.CreateItem(0)

With oItem
.Attachments.Add strDocName
.Display
End With

lbl_Exit:
Set oItem = Nothing
Set olApp = Nothing
Exit Sub
err_Handler:
Err.Clear
GoTo lbl_Exit
End Sub

[wdg1]

Thanks for your kind help. I tried your code but got a error "Set olApp = OutlookApp()" -> Sub of function not defined. So te macro does no work. Any idea why this happens?

[wdg1]

Thank you. The macro does not werk, error message: sMSWordFilename: variable not defined. Any idea? Thanks!

[Kenneth Hobs]

Use one of the 2 methods that I showed in #2 to set the outlook application object.

' Set olApp = New Outlook.Application 'Early binding
Set olApp = GetObject(, "Outlook.Application")

[gmayor]

Thanks for your kind help. I tried your code but got a error "Set olApp = OutlookApp()" -> Sub of function not defined. So te macro does no work. Any idea why this happens?You clearly didn't read the warning at the top of the macro as you have obviously not installed the function listed on that page-


'Requires the code from - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to either retrieve an open instance of Outlook or open Outlook if it is closed

[wdg1]

thank you for your nice help. So, I did copy your macro codend added a extra line: Set olApp = GetObject(, "Outlook.Application")
but I got an error: variable olApp not defined.What to do now? Thank you.

[Kenneth Hobs]

Most coders will Dim the variables as their first line(s) of code as Graham and I did. You must have added the Set line before the Dim alApp as Object line? You could remove the requirement to declare variables in VBE's Options or remove the Option Explicit line. I recommend requiring declarations myself.

Or, just do as Graham said and add the routine for OutlookApp() from Ron de Bruin's site. http://www.rondebruin.nl/win/s1/outlook/openclose.htm
That method is handy as it forces Outlook to open. If Outlook is not open for macros that Send, then those sends are put into the Output folder and won't send until you open Outlook.