Excel VBA Macro > Create XLS to PDF & Email

This is the Excel version of a previous post, Word VBA Macro > Create DOC to PDF & Email, to allow you to automatically generate a PDF attachment to a draft message in Outlook with a custom filename, from the click of a button in Excel. The current method to generate a PDF (with a custom file name) is to:

  • 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 Excel document’s name)
  • Attach to Outlook & send
  • Delete the PDF from disk

The process is now as follows:

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

Excel Macro Button

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

Macro prompts for filename to be used

3: Email message with the renamed attachment:

Outlook Message With PDF Attachment

You will 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.

You can swap out any of the target folders etc as required, and I’ve left examples in the code to use cell references to input CC: addresses etc.

Sub EmailPDF()
 Sub EmailPDF()
 fdir = "c:\\temp\\"
 fname = InputBox("Please Enter Filename")
 fpath = fdir & fname & ".pdf"
 With Application
.EnableEvents = True
.ScreenUpdating = False
 End With

ToAddress = "email@stott.asia"
CCAddress = Range("B22")
CCAddress2 = Range("i10")
CCAddress3 = CCAddress & "; " & CCAddress2
MailSub = "Emailing " & fname

'Generate PDF document to c:\. Substitute ActiveSheet for ActiveWorkbook to PDF the entire document
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
fpath, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

'Create Mail & attach PDF
 Set oOutlookApp = CreateObject("Outlook.Application")

'Create a new message
 Set oItem = oOutlookApp.CreateItem(olMailItem)
 With oItem
 .To = ToAddress
 .CC = CCAddress3
 .Subject = MailSub

'Bring up new mail window
 oItem.Display

'Add Attachment
oItem.Attachments.Add fpath

'Cleanup , baby
 Set OutMail = Nothing
 Set OutApp = Nothing
 Set fs = CreateObject("Scripting.FileSystemObject")

fs.deletefile fpath

End With
 End Sub

Posted in Macro, MS Excel, VBA | Tagged , , , | 1 Comment

Music

Well, here’s some music I’ve made over the last 10 years or so. You might like it, you might hate it… it’s all gravy baby. Some free 320 MP3’s for your musical pleasure, drum & bass & electro. Will add some more when I can be bothered.

After a couple of comments from people I’d just like to say, yes I do know who Ulterior Motive are, and no, I’m not pretending I’m them / bogarting their name. We’ve been producing as Ulteria Motive for 15 odd years, and if we were even 10% as good / successful as Ulterior Motive it would maybe be an issue. We’re not that good, and frankly I can’t be bothered thinking up another name for 5+ year old tunes which are never going to get any airplay anyway. Peace.

Symbolism- Electro boogie!

Download

 

Wardance – Ragga Jungle, sort of

Download
 

Violet – Jump up

Download

 

Posted in Music | 1 Comment

Add a Reference To The Microsoft Office Object Library

Here are some steps to follow, to allow you to add a reference to the Microsoft Office Object Library from within Excel. Steps 1, 2 & 3 can be skipped via ALT+F11, but it’s handy to have the developer tab added for later on.

Depending on your version of Office, these steps or the names of the objects may differ slightly – Office 2010 is 14.0, Office 2012 is 15.0 etc

1: Firstly, add the Developer tab to Excel – Right-click anywhere in the ribbon & select ‘Customise The Ribbon’:

1

2: Put a check in the ‘Developer’ box & hit ‘OK’:

2

3: Switch to the Developer tab, and hit the Visual Basic shortcut:

3

4: Select Tools > References:

4

5: Scroll down to the required Library, add a check box, and hit ‘OK’

5

Posted in MS Excel, VBA | Tagged , , , , | Leave a comment

SharePoint 2010 – Drop Off Library Not Indexed For Searching

After patching our SharePoint 2010 farm to SP1 & the Feb 2013 CU, we noticed that our drop off library was no longer being indexed for searching…. The configured crawls were taking place, but new items were no longer showing in the search results for some reason. Under Library Settings > Advanced Settings > Search we could see that the option “Allow items from this document library to appear in search results?” was set to ‘No’. We turned it back on, ran a crawl & items now appeared again.

Allow items from this document library to appear in search results?

Allow items from this document library to appear in search results?

When the users reported that the issue had recurred, we checked this option & it was set back to ‘No’. So we turned it back to ‘Yes’, and then ran a crawl again. An hour later the same thing happened again. We found this post from P Griffiths which indicated that the Content Organizer Timer Job was affecting this setting…. This tied in perfectly with the behavior we were seeing, as the timer job is running every 30 minutes turning this setting back off.

We couldn’t find much information about this online, but from what we could see this was by design as a Drop Off Library is not meant to be configured to be indexed like this. As the crawls are running every 10 minutes and this job is disabling indexing every 30 minutes, I needed a way of quickly forcing this back on without manual intervention.

We came up with the below solution…. Our SharePoint developer wrote this powershell script:

Add-PSSnapIn Microsoft.SharePoint.PowerShell
$site=Get-SPSite "http://sharepoint.domain.com/library/"
$web=$site.RootWeb
$docLibrary =$web.Lists["Drop Off Library"]
# Change the advanced settings
$docLibrary.NoCrawl = $false;
$docLibrary.Update()
$site.Dispose()

And then a batch file to call the script:

powershell -file "C:\\scripts\\DropOffCrawl.ps1"

And scheduled as a task to run every 5 minutes on the Web Front End server, forcing the option back on in time for the next crawl each time. Biggles.

Posted in SharePoint | Tagged , , , | Leave a comment