?
Solved

Outlook Macro does not run when doing a "Send to" in Excel/Word

Posted on 2009-02-18
15
Medium Priority
?
476 Views
Last Modified: 2013-11-25
I have an Outlook macro that prompts the user for confirmation on sending a message with an attachment to an external domain.
This macro works great in Outlook.  It does not work when doing a "Send To" in any office application.
Please tell me I don't need a macro for every office application...
Please help!
0
Comment
Question by:NoPainNoGain
  • 9
  • 6
15 Comments
 
LVL 47

Expert Comment

by:apache09
ID: 23677711
Which Version of Office are you using?

0
 

Author Comment

by:NoPainNoGain
ID: 23685270
Using Office 2003
0
 
LVL 47

Expert Comment

by:apache09
ID: 23685445
Great, I believe the following works for 2003.

Right Click Start and go to Explore
Navigate to :
C:\Documents and Settings\Username

*Make sure your view is set to show Hidden Files and Folders

Click on the SendTo Folder

On the right
Create a New Shortcut
Location should be:  "C:\Program Files\Microsoft Office\Office11\OUTLOOK.EXE" /a
Next
Name it accordingly, something you will recognize. (Example: WORD EXCEL EMAIL)
Finish

Now in Word or Excel when you use Send To this new shortcut option should be available.
Using this one, your macro should work.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:NoPainNoGain
ID: 23685685

Thanks for the help so far but I'm a little confused
I created the shortcut in the folder you suggested.  It is pointing to the outlook application (which is same location but actually on our d drive)
I'm not sure where I should see the shortcut appear now?   Will this change the current Send To functionality?
I am deploying this to 300 users by lowering macro security and copying the outlook macro file to their PC via a logon script.
I have attached the code for reference, not sure it is needed though.

Thanks again for the help!
0
 

Author Comment

by:NoPainNoGain
ID: 23685692

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim prompt As String
    Dim myInspector As Outlook.Inspector
    Dim myItem As Outlook.MailItem
    Dim myAttachments As Outlook.Attachments
    Dim ExtEmailAddress As Boolean
    Dim Totalrecipients As Integer
    Dim Count As Integer
    Dim ToList As String
    
    Set myInspector = Application.ActiveInspector
    ExtEmailAddress = False
    
    If Not TypeName(myInspector) = "Nothing" Then
        If TypeName(myInspector.CurrentItem) = "MailItem" Then
            Set myItem = myInspector.CurrentItem
            Set myAttachments = myItem.Attachments
                     
            'Check for an external email address (if an smpt format then external)
            Totalrecipients = myItem.Recipients.Count
            For Count = 1 To Totalrecipients
                If InStr(myItem.Recipients.Item(Count).Address, "@") Then
                    ToList = ToList & myItem.Recipients.Item(Count).Address & vbCrLf
                    ExtEmailAddress = True
                End If
            Next
                        
            'If there are attachments and an external email address exists then prompt the user
            If myAttachments.Count > 0 And ExtEmailAddress = True Then
                prompt = "This email contains an attachment(s) and it is being sent to the following external addresses: " _
                & vbCrLf & vbCrLf & ToList & vbCrLf & "Are you sure you want to send these " _
                & myAttachments.Count & " attachments to these external recipients?"
                If MsgBox(prompt, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "***Warning***") = vbNo Then
                    Cancel = True
                End If
            End If
        End If
    End If
End Sub
Private Sub Application_Startup()
    'This section is required to initialize macro's on startup
End Sub

Open in new window

0
 

Author Comment

by:NoPainNoGain
ID: 23685982
Ok, forgive the slowness in me catching on....but I do see what you were refereing to now, that was using Send To from windows explorer when right clicking on the file.
Your suggestion did help a great deal here.  I deleted the Mail Recipient File and then renamed my macro as the same name.   This allows my macro to run when doing the send to from explorer...so that is great!!!
Now I need to figure out how to disable the send to in the office application itself, or get my macro to run from there.
0
 

Author Comment

by:NoPainNoGain
ID: 23686001
Sorry, I meant to say I used your shortcut Idea with outlook /a as an an option to launch when right clicking on a file,  NOT my macro file...in any case that part is now completed.
0
 
LVL 47

Expert Comment

by:apache09
ID: 23686448
Sorry, thought it would add this shortcut as an option in Word or Excel Send To. It doesnt.

However, If you save the Word or Excel Document, say to your desktop.
Right click on the Word or Excel file.
Go to Send To>
Here you will see your newly created Outlook Send To Shortcut.
Selecting it this way will create a new mail item with this file attached and macro should still run.



0
 

Author Comment

by:NoPainNoGain
ID: 23686701
Your idea was helpful as it helped me resrict the "Send To" feature from the file system level, as in from the desktop or windows explorer.
I need to find a way to enable my macro or disable the Sent To feature from the applications then.
Any idea on where I could go next?
0
 
LVL 47

Expert Comment

by:apache09
ID: 23687710
Considering that Word and Excel use MAPIMAIL for Send To.
Here is the registry location to fix it, if they didnt work.

http://support.microsoft.com/kb/918792

However, since we are wanting to diable this option, im thinking would need to somehow do the opposite or set the settings to 0.


0
 
LVL 47

Expert Comment

by:apache09
ID: 23687715
Setting the following to 0 does make option in Word and Excel Unavailable

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Messaging Subsystem

MAPI REG_SZ  1
MAPIX REG_SZ  1
OLEMessaging REG_SZ  1
0
 

Author Comment

by:NoPainNoGain
ID: 23694031
Thanks, is very helpful!   If we can not find a way to get the macro from the MAPIMAIL, then I will present disabling this feature as the only other alternative.
Is there any way you can think of on how we can accomplish this?
0
 

Author Comment

by:NoPainNoGain
ID: 23694442
This also only hides the "Send To" "Mail Recipient (as attachment)".   It does not appear to hide "Mail Recipient" nor "Routing Recpient" options which still work.
0
 
LVL 47

Accepted Solution

by:
apache09 earned 1500 total points
ID: 23706570
Sorry, left one out.

Try Setting
CMC REG_SZ  1

to 0

0
 

Author Comment

by:NoPainNoGain
ID: 23714264
I have changed that key also but it doesn't seem to remove the hide "Mail Recipient" nor "Routing Recpient" options.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Changing a few Outlook Options can help keep you organized!
Take a look at these 6 Outlook Email management tools which can augment the working and performance of Microsoft Outlook to give you a more rewarding emailing experience.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question