Send Excel Workbook Automatically to Public Folders

  • Thread starter Thread starter Maria K Brickl
  • Start date Start date
M

Maria K Brickl

Does anyone know how to write code within excel VBA to send a workbook
automatically to Outlook Public folders? I know how to send an email
automatically - I just need to know how to post to a public folder
automatically.

Any ideas?
 
I don't think you can add an existing Workbook using the Items.Add method
with the olExcelWorkSheetItem argument; this just creates an empty Workbook
(what good is this I don't know!). That approach saves the spreadsheet as a
standalone empty file in the folder, but the code below can simply create a
Post and insert the active Workbook as an attachment:

Sub AttachWorkBookToPostItemAndSaveToChosenFolder()
Dim objOL As Outlook.Application, objFolder As Outlook.MAPIFolder
Dim objNS As Outlook.Namespace, objPostItem As Outlook.PostItem

Set objOL = New Outlook.Application
Set objNS = objOL.GetNamespace("MAPI")

Set objFolder = objNS.PickFolder
Set objPostItem = objFolder.Items.Add(olPostItem)
objPostItem.Subject = "My Workbook"
objPostItem.Attachments.Add ActiveWorkbook.FullName
objPostItem.Save

Set objPostItem = Nothing
Set objFolder = Nothing
Set objNS = Nothing
Set objOL = Nothing
End Sub
 
Instead of a PostItem, try creating a DocumentItem and then attach the file.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
That makes sense! Thanks Sue. Looks like you have to remove the existing
attachment first.

Here's my modified macro that will handle creating any supported Office
document type from an existing file:


Sub CreateOfficeDocInChosenFolder(lngDocType As OlOfficeDocItemsType)
On Error Resume Next

Dim objNS As Outlook.NameSpace
Dim objDoc As Outlook.DocumentItem
Dim objFolder As Outlook.MAPIFolder
Dim strFilePath As String, strExt As String, blnError As Boolean
Dim objFS As Scripting.FileSystemObject 'Need reference to Microsoft
Scripting Runtime

Set objNS = Application.GetNamespace("MAPI")

Set objFolder = objNS.PickFolder
If objFolder Is Nothing Then Exit Sub

strFilePath = InputBox("Please enter the location to your Office
document", "Enter File Path", "C:\Documents and
Settings\ericl\Desktop\Book1.xls")

Set objFS = New Scripting.FileSystemObject
If objFS.FileExists(strFilePath) = False Then
MsgBox "The file '" & strFilePath & "' is invalid.", vbOKOnly +
vbExclamation, "INVALID FILE"
Exit Sub
End If

strExt = Right(strFilePath, 3)

Select Case lngDocType
Case OlOfficeDocItemsType.olExcelWorkSheetItem
If strExt <> "xls" Then blnError = True
Case OlOfficeDocItemsType.olWordDocumentItem
If strExt <> "doc" Then blnError = True
Case OlOfficeDocItemsType.olPowerPointShowItem
If strExt <> "ppt" Then blnError = True
End Select

If blnError = True Then
MsgBox "Extension '" & strExt & "' does not match the
OlOfficeDocItemsType value.", vbOKOnly + vbExclamation, "INVALID FILE TYPE"
Exit Sub
End If

Set objDoc = objFolder.Items.Add(lngDocType)
objDoc.Attachments.Remove 1
objDoc.Attachments.Add strFilePath
objDoc.Subject = objDoc.Attachments.Item(1).DisplayName
objDoc.Save

Set objFS = Nothing
Set objDoc = Nothing
Set objFolder = Nothing
Set objNS = Nothing
End Sub
 
I think it may be even easier than that. Try:

Set objDoc = objFolder.Items.Add("IPM.Document")
objDoc.Attachments.Add strFilePath
objDoc.Subject = objDoc.Attachments.Item(1).DisplayName
objDoc.Save
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
Sneaky! You lose the document's native icon for the message though - you
just get a plain icon that looks like a document or letter (as opposed to the
Excel, Word or PowerPoint icon).

Also, and this may be a caching issue, my document loads as "Book1 (2).xls"
in Excel now. <shrug>
 
Hi,

Is there any way to have Outlook activate in a "Silent" mode. When I run
this code, Outlook becomes the active screen for a brief moment while it
sends email, this is very distracting. Any ideas ?

Thanks.
 
New and improved version at
http://www.outlookcode.com/codedetail.aspx?id=712 now handles the special
Excel, Word, and PowerPoint icons.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



Eric Legault said:
Sneaky! You lose the document's native icon for the message though - you
just get a plain icon that looks like a document or letter (as opposed to
the
Excel, Word or PowerPoint icon).

Also, and this may be a caching issue, my document loads as "Book1
(2).xls"
in Excel now. <shrug>
Click to expand...
 
Back
Top