Word VBA Macro > Create DOC to PDF & Email

A while back, I had a request to automate the way users can create a PDF file from MS Word, and attach it to an email – essentially create a Word to PDF macro, with automatic email creation.  Currently their entire process would be:

  • Save to PDF (File > Save As > .PDF / File > Print > Adobe PDF)
  • Choose target folder & save
  • Rename newly created PDF (as it creates with the source Word document’s name)
  • Attach to Outlook & send
  • Delete the PDF from disk

Also, part of the problem was the way that Outlook locks up when the ‘Send to Email’ function is initiated from Word, so you need to save your new message as a draft in order to switch back to the main Outlook view & find a recipient’s email address, for example. I was looking for ways around this & came up with the below VBA code with the help of a colleague, saved inside each user’s normal.dotm file thereby applying to every document they create from our CRM.

The macro was added to their quick launch toolbar in Word 2010 (with a user-friendly envelope icon) via login scripts replacing their normal.dotm, allowing them a one-click option to initiate the code & be presented with the filename prompt. I used H:\ as the target location because every user has the home drive by default, and some users would be using this within Citrix without permission to the system C:\

The process is now as follows:

1: User completes their Word document & hits the shortcut to run the EmailPDF macro:

Word 2010 Macro Button

Word 2010 Macro Button

2: Macro executes & prompts for the filename to use:

Filename Prompt for Word PDF Macro

Filename Prompt for Word PDF Macro

3: Attachment is added with correct name to blank email:

Outlook Message With PDF Attachment

Outlook Message With PDF Attachment

Full code is below, this can obviously be cleaned up a little & offer friendlier error messages, but this wasn’t a priority for me – if the user cancels the process halfway through & gets a generic error message, then so be it.

You will also need to add a reference in Word to the Microsoft Outlook 14.0 Object Library to get this to work. If you find this useful, please leave me a comment because I would love to know. Any suggestions to improve the below would also be welcomed.

Should you wish to do the same function in Excel, please see this post.

Sub EmailPDF()
 Dim strData As String
 Dim ola As Outlook.Application
 Dim maiMessage As Outlook.MailItem
 Dim fs strData = InputBox("Please Enter Filename")
 strData = "h:\" & strData & ".pdf"

'Creates a PDF and stores it locally
 ActiveDocument.ExportAsFixedFormat OutputFileName:= _
 strData, ExportFormat:= _
 wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
 wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
 Item:=wdExportDocumentWithMarkup, IncludeDocProps:=False, KeepIRM:=True, _
 CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
 BitmapMissingFonts:=True, UseISO19005_1:=False
 On Error Resume Next

'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)
 oItem.Display

'Add attachment
 oItem.Attachments.Add strData

'Create a file system object to delete temporary file
 Set fs = CreateObject("Scripting.FileSystemObject")
 fs.deletefile strData
 End Sub

Posted in Macro, MS Word, VBA | Tagged , , , | 10 Comments

Lowering Active Directory Domain / Forest Functional Level

Well… my previous post about my sole domain controller corrupting caused me to go off and promote a new domain controller as a contingency.

When I set up my lab, I set the functional level to Server 2012 in order to see what the differences would be. My current domain controller is Server 2012, but my other hosts are all 2008 R2… because of this, I couldn’t promote my existing hosts to domain controllers & didn’t fancy running up a new 2012 box purely for this backup role. Running dcpromo on the host in it’s current state results in an error “The functional level of the domain or the forest is incompatible with this operating system”

So it left me with the question, how am I going to rollback the functional level to 2008 R2 to let me do this? Well, PowerShell obviously…

Firstly I had to lower the forest to the required level, via:
Set-ADForestMode -Identity domain.local -ForestMode Windows2008R2Forest
(substituting domain.local for the FQDN of my domain)

And then I could lower the domain functional level:
Set-ADDomainMode -Identity domain.local -DomainMode Windows2008R2Domain

Then running a dcpromo on my 2008 R2 host again allowed me to configure it as a new domain controller.

 

Posted in Active Directory, Server 2008, Server 2012 | Tagged , , , , | 1 Comment

Server 2012 Crashing – Error 0xc00002e2

Well… today I had a power outage at my house, and my Hyper-V lab was shut down abruptly. When I brought the computer up again my hosts had crashed, and my domain controller was stuck in a rebooting loop with this:

Server 2012 0xc00002e2 Crash ErrorYour PC ran into a problem and needs to restart. We’re just collecting some error info, and then we’ll restart for you. (0% complete). If you’d like to know more, you can search online later for this error: 0xc00002e2)

 

Ouch…. this isn’t good, as this is my only domain controller in the lab currently and I deleted my VM snapshots when troubleshooting a previous issue (d’oh).

As expected, the computer reboots and launches the new 2012 recovery console. It gives me usual options for safe mode & command prompt as well as system restore… I don’t have any system restores, and launching the command prompt and selecting sfc /scannow does little to help.

I started looking at the VM config in Hyper-V Manager, checking the RAM & CPU values, the IDE controller settings are normal (but I detached the ISO I had mounted to the DVD drive just to be safe). Everything else looks fine….

Then I noticed that the SCSI controller option has a hard disk listed, with no VHD mounted. I was playing around with this a week or so ago & hadn’t realised that I’d left this setting applied.

So I shut down the host, removed the SCSI config, hit the start button and crossed my fingers…. and now it’s back in action. A quick snapshot because hindsight is 20/20, and it’s goodnight from me. Tomorrow I’m building a second DC…

Posted in Hyper-V, Server 2012 | Tagged , , , , | 9 Comments

Managing Server 2008 R2 Servers with Server 2012 Server Manager

Today I was in my Hyper-V lab, adding a new 2008 R2 server to Server Manager inside Server 2012. The server has been joined to the domain, is visible from my domain controller & is a selectable machine from the 2012 Server Manager interface. However when trying to add it to be managed remotely, I receive the error “Online – Verify WinRM 3.0 service is installed, running, and required firewall ports are open“:

Server Manager - 2012

Server Manager – 2012

Hardly surprising, as this is a new box & hasn’t had any configuration done to it yet. So off to the target machine I go trotting, with high hopes of this being the fix. Starting up PowerShell & running “winrm quickconfig” advises that WinRM is already configured, but there is no listener configured:

PowerShell winrm /quickconfig

winrm /quickconfig

Back to my 2012 Server, I did a refresh in Server Manager… still no action. Maybe it’s something to do with an absence of .NET on this machine? Added .NET through the Add Features wizard and refreshed yet again from my 2012 box… still no juice.

Well… I figured it’s got a pre-requisite of some kind that I don’t have installed. A quick Google search later took me to this page: http://technet.microsoft.com/en-us/library/hh831456.aspx

“To fully manage remote servers that are running Windows Server 2008 or Windows Server 2008 R2, install the following updates, in the order shown.
1. .NET Framework 4
2. Windows Management Framework 3.0

So I was close… Off I go to download & install Windows6.1-KB2506143-x64.msu from the link provided. Rebooted as prompted & waited for the server to come back up.

A quick refresh on my 2012 Server Manager, and voila… “Online – Performance counters not started”:

Server Manager 2012 - Performance Counters Not Started

Performance Counters Not Started

A quick right-click & ‘Start Performance Counters’ later, and the server is showing as online & manageable from Server 2012:

Server Manager 2012

Great success

Posted in Server 2008, Server 2012 | Tagged , , , | 2 Comments