Auto-attachments in Oulook

[Starbuzz]

Hello,

I want to attach an Excel file to all my outgoing Outlook e-mails. This is in a small-business Exchange server environment. Version is Oulook 2003.

So what I am hoping to do is just allow one user to auto-attach an Excel file whenever he sends an e-mail. So when he clicks on the "New" e-mail and the form opens up, the Excel attachment is already loaded up in the e-mail. All that is left is to type the e-mail and send it.

Is it possible? I researched well to see if such an option is possible but to no avail.

[NooNoo]

Why an excel file? What information would you put in an excel file that every recipient would want to receive? Would something like vCard be better?

[Starbuzz]

The Excel file is actually a complex order entry form that is made up of 2 sheets using the VLOOKUP function.

The first sheet is the order form itself. The second sheet is locked, hidden, and password protected and this sheet contains the huge company catalogue with products, prices, item numbers, description, packaging info, UPC code, etc. It is a long sheet you can scroll down and down.

Only 1 person needs to send this Excel file with his e-mails. He does not send that many e-mails out; only to potential customers.

[Starbuzz]

Very sorry for mistake double post. Please delete.

[CeeBee]

Not a solution per se but why not make this thing an online form? Easier to maintain and control the versions of catalog files flowing around. Sooner or later someone *will* use an out of date file for ordering.
As far as auto-attaching goes, it can be done with a macro.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
  Dim dRes As Long
  If Item.Attachments.Count = 0 Then
    dRes = MsgBox("You didn't attach the file!" & vbcrlf & "Send anyway?", vbYesNo + vbDefaultButton2 + vbQuestion, "Hey you dummy!")
    If dRes = vbNo Then Cancel = True
  End If
End Sub

I'll let you tweak it and put the code to attach the file

[Starbuzz]

Thank you very much, Cee! I will get started on that. Sorry for my late response as I was away on vacation.

I have very little experience with macro's but I found this to get me started:
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

I will work on this right away and see how it goes. Thank you.

Not a solution per se but why not make this thing an online form? Easier to maintain and control the versions of catalog files flowing around. Sooner or later someone *will* use an out of date file for ordering.

We have a policy of publishing catalogues for each year. Right now the 2008 catalogue is the only one being used. 2007 catalogue is discarded. Customers are sent these catalogues in bulk so they can see the products and such. We also have a web-based order system with the up-to-date online catalogue, which does not replace, but compliments the secondary paper-based (fax) ordering.

Unfortunately, we have equal number of customers that use both web and fax to order.

This e-mail/Excel based ordering system seeks to replace the fax-based ordering since they are handwritten on the customer's own forms! Our CS people are losing time typing in the numbers into their application. If it is e-mail based, then they can just copy-paste the order info from the completed Excel file from the customer into their apps to process it thus saving us a lot of time.

[Lowland]

Is this too simple?

1 user only sends emails that need this attachment, and they don't send many.

Why not just let him have a copy of the excel handy (network or desktop) and tell hime to right click>send to email recipient

Mind you I've been away a while....

[Starbuzz]

Hi all,

So, the macro did not work. I tried everything. Anyway, the good news is I did find what seems to be the appropriate code for it.

Here is the code:

Sub InsertFile()
' requires reference to Microsoft Outlook library
Dim objOL As Outlook.Application
Dim objInsp As Outlook.Inspector
Dim objMail As Outlook.MailItem
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
Set objInsp = objOL.ActiveInspector
Set objMail = objInsp.CurrentItem
objMail.Attachments.Add "C:\data\myfile.txt"
Set objMail = Nothing
Set objInsp = Nothing
Set objOL = Nothing
End Sub

I am messing with it now but this is not my area of expertise. I am pretty bad at anything to do with code and programming...even basic things like this. So, I am working on it now. The code seems to make sense as to what I am trying to do.

Now my main question is that if I specify the path for the file, then that file should load up when I click to open a new mail message right? It doesn't do that...or I am probably doing it wrong.

I am writing the macro again in Word 2003 as that is set as my default editor for Outlook...let's see how it goes. Other than that, I need your help in trying to create a custom toolbar for it. And also any ideas on how to get this working...thanks everyone.

Why not just let him have a copy of the excel handy (network or desktop) and tell hime to right click>send to email recipient

He does not want to do that. If he did that, I would not be having to do this at all...

[Lowland]

Mmm. That would be my point. Sometimes simplest is best is all I was saying.

Anyay, from my memory only, try googling "yousendit" and attachment alarm. I looked at these a while back, a few years ago.
Perhaps one can help you.

[CeeBee]

That macro will work in Outlook, but not in Excel or Word.

[Starbuzz]

Hey all!

I got this problem solved a while ago but I thought I will post the code here so there is a record of the solution here in this forum. The code was given to me by expert Sue Mosher from another forum. She is an author of many IT related books among many other things.

So here it is:

Put the code in the ThisOutlookSession module. Then run Application_Startup.

Dim WithEvents m_colInsp As Outlook.Inspectors
Dim WithEvents m_objInsp As Outlook.Inspector

Private Sub Application_Startup()
Set m_colInsp = Application.Inspectors
End Sub

Private Sub m_colInsp_NewInspector(ByVal Inspector As Inspector)
Set m_objInsp = Inspector
End Sub

Private Sub m_objInsp_Activate()
Dim objMail As Outlook.mailItem
If m_objInsp.CurrentItem.Class = olMail Then
Set objMail = m_objInsp.CurrentItem
If objMail.Size = 0 Then
' it's a new message
objMail.Attachments.Add "C:\Data\myfile.xls"
End If
End If
Set objMail = Nothing
Set m_objInsp = Nothing
End Sub

And if you want to add more files, simply copy the objMail.Attachments.Add "C:\Data\myfile.xls" and paste it underneath in another line. Then of course you have to type in the right directory your files are in.

[NooNoo]

Sue's also an Outlook MVP. Glad she could help you.